Re: PDQ

> makes a big difference to stuff like index creation and update stats. We learned to be careful with PDQ and UPDATE STATISTICS. In a past job, I had been working mainly with a data warehouse. In that environment, I made constant and effective use of PDQ, and didn't think anything of it. Very few users. Very few stored procedures. No problems. Great performance. In present job, one day I cranked in a little PDQ to speed up UPDATE STATISTICS (tables and procedures). About 15 minutes after UPDATE STATISTICS routine finished, the system ground to a halt. Bottom line was that all the stored procedures were prepared according to the environment that existed when the preparation was done (PDQ cranked in, so multithreaded, etc.). Now the current system is OLTP, not a warehouse. So, has a few hundred users and 1000 SPs. As users started using the newly compiled SPs, they started sucking up ever increasing resources. There was continual allocation of virtual memory (several times a minute). Within 15 minutes, all memory was allocated, and system was effectively dead. Solution was to rerun UPDATE STATISTICS with 0 PDQ, and everything went back to normal. My lesson was learning not to be so "free and easy" with PDQ. Still a good thing where appropriate, though. DG "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message news:bn42df$t1sn3$1@ID-64669.news.uni-berlin.de... > Markus Bschorer wrote: > > > Hi guys, > > > > since the price-differences betwen IDS 9.4 and IDS 9.4 WE are not > > marginal, I wondered, whether it's worth to buy a IDS 9.4 in order to be > > able to use PDQ. May be it's better, to invest the difference-amount in > > better hardware. > > > > Does anyone of you have expieriences in using PDQ and it's benefits > > concerning Query-Performance? > > Only if you're doing big, heavy, table scanning queries, really. But it also > makes a big difference to stuff like index creation and update stats. > > -- > Ciao, > The Obnoxious One > > "Ogni uomo mi guarda come se fossi una testa di cazzo"

David E. Grove wrote: >> makes a big difference to stuff like index creation and update stats. > > We learned to be careful with PDQ and UPDATE STATISTICS. > >[SAD TALE SNIPPED] > > Solution was to rerun UPDATE STATISTICS with 0 PDQ, and everything > went back to normal. Yes - it's essential, nay, ESSENTIAL, to reset PDQ settings prior to optimising the stored procedures! A very easy thing to integrate into a stats script. Do all the SP's last, so they benefit from the refreshed stats of the tables. Depending on the setup, you could still profit from setting pdq priority to 1, to encourage parallel scans on fragmented tables. Of course this is only useful if u have fragged tables and SPs which do big things with them.

David E. Grove wrote: >> makes a big difference to stuff like index creation and update stats. > > We learned to be careful with PDQ and UPDATE STATISTICS. > > In a past job, I had been working mainly with a data warehouse. In that > environment, I made constant and effective use of PDQ, and didn't think > anything of it. Very few users. Very few stored procedures. No > problems. Great performance. > > In present job, one day I cranked in a little PDQ to speed up UPDATE > STATISTICS (tables and procedures). About 15 minutes after UPDATE > STATISTICS routine finished, the system ground to a halt. Bottom line was > that all the stored procedures were prepared according to the environment > that existed when the preparation was done (PDQ cranked in, so > multithreaded, etc.). Now the current system is OLTP, not a warehouse. > So, > has a few hundred users and 1000 SPs. As users started using the newly > compiled SPs, they started sucking up ever increasing resources. There > was > continual allocation of virtual memory (several times a minute). Within > 15 minutes, all memory was allocated, and system was effectively dead. > > Solution was to rerun UPDATE STATISTICS with 0 PDQ, and everything went > back to normal. > > My lesson was learning not to be so "free and easy" with PDQ. Still a > good thing where appropriate, though. There is a new environment variable (DBUPSPACE?) which, when used with PDQ will allocate more memory for the update statistics process which means that sorting of the index data will take place in parallel, which is much faster. However,what you're describing is the documented behaviour of SP compilation. So yes, you don't want to do this for SPs, but you do want to do it for data. > "Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message > news:bn42df$t1sn3$1@ID-64669.news.uni-berlin.de... >> Markus Bschorer wrote: >> >> > Hi guys, >> > >> > since the price-differences betwen IDS 9.4 and IDS 9.4 WE are not >> > marginal, I wondered, whether it's worth to buy a IDS 9.4 in order to >> > be able to use PDQ. May be it's better, to invest the difference-amount >> > in better hardware. >> > >> > Does anyone of you have expieriences in using PDQ and it's benefits >> > concerning Query-Performance? >> >> Only if you're doing big, heavy, table scanning queries, really. But it > also >> makes a big difference to stuff like index creation and update stats. -- Ciao, The Obnoxious One "Ogni uomo mi guarda come se fossi una testa di cazzo"