Re: Performance question and SQL SERVER

@vernon:

if you have to deal with tables containing hundreds of millions of records each, in a mixed environment with a BI frontend generating SQL statements by the fly and query users going out to the same tables (sometimes with strange statements like incomplete join conditions or strange order by criteria) index advisory files, plan cache snapshots are absolutely useless, because you don't see where the statements where coming from. In an environment with thousands of SQL statements Visual Explain is absolutely useless, new altered statements could arise faster, than 10 people could examine the statements - so I call this toys for people with too much time, very nice for playing around.

Bringing the QZDAxxx Jobs to debug is no big issue and doesn't harm the system too much and having a look to long running jobs is often bringing solutions in very short time. If IBM marketing is telling that this is deprecated (I don't believe so), maybe the marketing of IBM is deprecated.

The tool of first choice (in my experience) is database monitor and the only way to analyze the results is OoopsNerv (it's a nightmare using the german versions, most of my custumors are providing to me! The german versions are done by automatic translation and a new version normaly crashs, because the translator is destroying SQL statements...). If there would be something better, I would use this, but there isn't. Much better would be tools doing some analysis by automatic or if the database would have the capability to make temporary indexes persistent for a given amount of storage.

Just a little example: last time we've had some queries to a rather complex view not coming back within two days. Some collegues had a look to index advisory file, lots of (useless) EVIs where recommended. Next step was visual explain: playing around for hours they didn't find a solution. 10 minutes reading joblogs and 10 minutes to replace 2 time views (to join a time_id to last date of month and last date of week) with denormalized tables with some hundred of records brought the response time to some seconds.

As a conclusion of this:
- First you have to know where the problem is located (which SQL statement exactly is consuming the time)
- Then you can start to solve the problem (mostly creating an index or denormalise some data).

D*B