>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,
      EventClass,
      TextData,
      SUM(Duration) AS TotalDuration,
      SUM(CPU) AS TotalCPU,
      SUM(Reads) AS TotalReads,
      SUM(Writes) AS TotalWrites
FROM Trace_Table
GROUP BY EventClass,TextData
ORDER BY TotalReads DESC
Why order by TotalReads and not CPU, read this
Advertisements

Posted on February 18, 2011, in Performance Tuning, SQL Profiler. Bookmark the permalink. 2 Comments.

  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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: