Friday, February 18, 2011

SQL Server Performance: Not All Fast Queries Are Innocent

To, find out costly quires, a majority of DBAs like to visit SQL Profiler. Queries with higher CPU and READ/WRITE are marked as costly quires. I have observed that most of the time quires with less CPU and READ are ACTUAL BIG PROBLEM. Cumulative effect of multiple executions of these commonly considered well performing quires normally put more pressure on system as compare to occasionally executing costly quires. Simple if a query giving result in 10 milliseconds but being executed 10000 time, with in a short time, then definitely it’s a REAL costly query.
To get optimum performance, identification of such COSTLY quires is necessary. This can be achieved by creating history of trace data for peak and off peak hours of your database. Follow given steps for this task.
  • Open profiler, and select following columns
    • EventClass, TextData, Duration, CPU, Reads, Writes
  • Create a filter on your required database
  • Start your trace, and later save this trace data to some trace output file.
  • Load trace data from trace file to trace table by using following query
SELECT * INTO Trace_Table
FROM ::fn_trace_gettable('C:\YourTraceFile.trc',default)
  • Once the trace data is imported, use following query to find quires with high CPU and READ/WRITE values
SELECT COUNT(*) AS TotalExecutions,
      SUM(Duration) AS TotalDuration,
      SUM(CPU) AS TotalCPU,
      SUM(Reads) AS TotalReads,
      SUM(Writes) AS TotalWrites
FROM Trace_Table
GROUP BY EventClass,TextData
Why order by TotalReads and not CPU, read this


  1. Actually not 'quires', but 'queries'.

  2. Why would you go to this trouble when you can use the canned reports that come with SQL Server out of the box that can provide this information in a graphical and exportable format?


All suggestions are welcome