We unleashed Service Pack 4 on our SQL 7 servers, which are running the Enterprise Edition of SQL 7 a couple of months ago. Within a week, an intermittent problem began occurring that has us completely stumped. At seemingly random times, the queries that contain subqueries (or nested queries if you want to call it that) that we run on a regular basis will become extremely slow in returning their results or may never end at all. One time when this happened, we let it go as long as we could stand it and we stopped the query after 45 minutes when it should have taken about 35 seconds. Time is of the essence when we run these statements. We have several different select statement queries with subqueries that it happens to. Nothing changed at all except Service Pack 4, there are not any background processes and nobody is hitting the database except for us. The system was previously using Service Pack 1.
Believe it or not, we went for several years on Service Pack 1 without any problems and without reindexing. One of our quick fixes is to reindex one of our main tables when this happens which takes about five minutes, but is worth it. Another quicker fix is to run update statistics on that table, but that does not always solve the problem.
We figure this has to be happening to somebody else. Anybody? Please?
In case you are wondering the select statements are to the effect of this:
select * from table01 where column01 = 'value01' and column02 in (select column02 from table02 where column03 = 'value02')
Unfortunately, we've checked the log file and nothing unusual is there. We've also run profiler several times and doesn't indicate anything and grinds to a halt when the query does as well. We haven't tried the index tuning wizard since the datbase was created years ago and has been working great until Service Pack 4. I will give that a try. Thanks for the help!
Originally posted by Satya
This may not be a SP4 related problem, first off make sure SP4 got installed without any errors by referring to SQLSTP.LOG file.
Then run PROFILER and see where exactly the performance lacks and submit the trace to INDEX TUNING WIZARD for index recommendations.