SQL Server Performance

Odd variations in query performance ...

Discussion in 'Performance Tuning for DBAs' started by anil.nair@gtnexus.com, Dec 5, 2002.

  1. We have a SELECT query (not too complex) in an application that usually comes back with a sub-second response. But at times the same query will take upto 30 seconds. And then it will go right back to being a sub-second query. The query is run with transaction level "read uncommitted", so we dont think it is waiting for locks. We have trapped the slow instances of the query in the profiler. And it shows that the CPU time takes most of the 30 seconds of total duration for the query. That should indicate that a different query plan is being used occasionally. The sizes of the tables involved dont change drastically during this time, so the costs associated should be the same for the optimizer. What else could cause something like this to happen?
    How would you recommend that we go about troubleshooting this?

    Thanks in advance for any replies ...
  2. Chappy New Member

    Can you reproduce this behavior in query analyser ?
  3. sqljunkie New Member

    You might have a system level bottleneck (cpu or disk???).

    I would recommend logging Performance Monitor data on Disk and Processor as well as SQL Latches at the very minimum. At the same time I would gather Profiler data the same way you are now, but make sure you're gathering the StartTime Data Column.

    Once you're finished collecting data you can correlate PerfMon and Profiler data. You can adjust the time window in a PerfMon log to get to a particular point in time. With this information you can correlate the start time and duration of the poor performing queries from Profiler data with PerfMon data.

  4. bradmcgehee New Member

    I have this same problem, from time to time, on a couple of queries. In my case (but this may not be your case), the index statistics get out of date (before they are automatically updated), which causes the query optimizer to choose a less efficient execution plan and performance suffers. One option is to manually update all of the statistics in the affected table(s) once this happens, but his is reactive behavior. Next, I created a job to update the statistics on the affected tables nightly (this is on top of the automatic statistics being updated). But this did not help. It seems as the table was modifed throughout the day, the statistics still sometimes got goofed up. Finally, I decided to add appropriate query hints to the query, forcing the query to always use the indexes I specified, and this fixed the problem.

    Before using hints, if you decide to give them a try, be sure you know what a proper execution plan looks like. This will tell you what indexes and join types are being used, so that you know what hints to set up.

    On the other hand, I have seen this behavior when parallelism is acting strange, especially under SQL Server 7.0. SQL Server 2000 seems to be much better. In some cases, because parallelism is used, or not used, for a query, and this sometimes can seemrd to randomly affect query performance. Again, this may not be your case, but is something to think about.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  5. This one is driving me batty. We are using SQL 2000.

    Here is more information: I have trapped the query plans for some of these instances with wiely varying query times. And the query plans are the same!

    Which means stats are ok and I dont need to force the query plan with a hint. (I hate those hints in any case).

    If it is a CPU or Disk bottleneck then wouldnt the CPU be the same for different instances but the total Duration vary? In our cases, the CPU time varies widely. I have to investigate Latches!

    We turn off parallelism with "maxdop 1" because we uncovered bugs where SQL server returns the wrong number of rows when parallelism is on.
  6. sqljunkie New Member

    I posted some info under the Latching topic a day or so ago. Take a look at that for Latch monitoring.

    What does PerfMon say for Disk Queuing and CPU utilization?
  7. bradmcgehee New Member

    Do you have SP2 on SQL Server 2000, just case it is a "bug-related" issue that has been fixed? Of course, if you have SP2 loaded, then there is the remote possibility that SP3, when it comes out, will fix it. I grasping at straws here, based on the info provided.

    I assume that your server does not experience any hardware-related bottlenecks? If I am wrong, please tell us about them.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com

Share This Page