How Can I Log and Find the Most Expensive Queries?

by

  1. Use SQL Server Profiler (on the tools menu in SSMS) to create a trace that logs these events:

     RPC:Completed
 SP:Completed
 SP:StmtCompleted
 SQL:BatchCompleted
 SQL:StmtCompleted

  2. You can start with the standard trace template and prune it. You didn’t specify whether this was for a specific database or the whole server, if it is for specific Db’s, include the DatabaseID column and set a filter to your DB (SELECT DB_ID('dbname')). Make sure the logical Reads data column is included for each event. Set the trace to log to a file. If you are leaving this trace to run unattended in the background, it is a good idea to set a maximum trace file size say 500MB or 1GB if you have plenty of room (it all depends on how much activity there is on the server, so you will have to suck it and see).

  3. Briefly start the trace and then pause it. Goto File->Export->Script Trace Definition and pick your DB version, and save to a file. You now have a sql script that creates a trace that has much less overhead than running through the profiler GUI. When you run this script it will output the Trace ID (usually @ID=2); note this down.

  4. Once you have a trace file (.trc) (either the trace completed due to reaching the max file size or you stopped the running trace using

    EXEC sp_trace_setstatus @ID, 0
    EXEC sp_trace_setstatus @ID, 2

You can load the trace into profiler, or use ClearTrace (very handy) or load it into a table like so:

SELECT * INTO TraceTable
FROM ::fn_trace_gettable('C:\location of your trace output.trc', default)

Then you can run a query to aggregate the data such as this one:

SELECT COUNT(*) AS TotalExecutions, 
    EventClass, CAST(TextData as nvarchar(2000))
 ,SUM(Duration) AS DurationTotal
 ,SUM(CPU) AS CPUTotal
 ,SUM(Reads) AS ReadsTotal
 ,SUM(Writes) AS WritesTotal
FROM TraceTable
GROUP BY EventClass, CAST(TextData as nvarchar(2000))
ORDER BY ReadsTotal DESC

Once you have identified the costly queries, you can generate and examine the actual execution plans.

More Related Contents:

  1. Export from SQL Server to Excel
  2. How to get cumulative sum
  3. How to check if a column exists in a SQL Server table
  4. Infinite loop CTE with OPTION (maxrecursion 0)
  5. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  6. SQL Server add auto increment primary key to existing table
  7. What is the maximum characters for the NVARCHAR(MAX)? [duplicate]
  8. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  9. INSERT INTO vs SELECT INTO
  10. SQL Server sp_msforeachtable usage to select only those tables which meet some condition
  11. Query times out when executed from web, but super-fast when executed from SSMS
  12. TSQL: How to convert local time to UTC? (SQL Server 2008)
  13. How to: Create trigger for auto update modified date with SQL Server 2008
  14. How to update primary key
  15. error when inserting into table having instead of trigger from entity data framework
  16. Case sensitive variables in SQL Server
  17. Transpose rows and columns with no aggregate
  18. What happens to the primary key Id when it goes over the limit?
  19. SQL Recursive CTE: Finding objects linked by property
  20. Convert decimal time to hours and minutes
  21. SQL Server Configuration Manager cannot be found
  22. SQL Server recursive query
  23. How do I move a table into a schema in T-SQL
  24. nvarchar(max) vs NText
  25. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  26. How to find the boundaries of groups of contiguous sequential numbers?
  27. How to wait for 2 seconds?
  28. Find broken objects in SQL Server
  29. How to choose the clustered index in SQL Server?
  30. Populate Dataset With Table Names From Stored Procedure

Leave a Comment