How can I find and find the most expensive queries?

Question

The activity monitor in sql2k8 allows us to see the most expensive queries. Well, that’s cool, but is there any way that I can log this information or get this information through the query analyzer? I really don't want to open the Sql management console, and I'm looking at the activity dashboard.

I want to find out which queries are poorly written / the schema is poorly designed, etc.

Thanks for the heaps for any help!

Answer 1

• Use the SQL Server Profiler (in the tool menu in SSMS) to create a trace that logs these events:

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

• You can start with a standard trace template and crop it. You did not indicate whether it was for a specific database or the entire server, if it was for specific Db, enable the DatabaseID column and set a filter for your database ( SELECT DB_ID('dbname') ). Verify that the Reads data column is enabled for each event. Set the trace to write to the file. If you leave this trace unattended in the background, it is recommended to set the maximum trace file size to 500 MB or 1 GB if you have a lot of space (it all depends on how much activity is on the server, so you have to suck it and see).

• Run the trace briefly and then pause it. Goto File-> Export → Script Define a trace and select the version of your database and save the file. Now you have a sql script that creates tracing with much less overhead than working with the profiler GUI. When you run this script, it will display the trace identifier (usually @ID=2 ); pay attention to this.

• As soon as you have a trace file (.trc) (either the trace is completed due to reaching the maximum file size, or the running trace is stopped using

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

You can load the trace into the profiler or use ClearTrace (very convenient) or load it into the table as follows:

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

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

 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 costly requests, you can create and analyze actual execution plans.

Answer 2

The following script gives the result.

 SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes, qs.total_worker_time, qs.last_worker_time, qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time,qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp ORDER BY qs.total_logical_reads DESC 

Answer 3

I have never heard of this tool before, but Microsoft provides a set of reports that do a fantastic job to give you exactly that, including the slowest queries. Check out their Dashboard Performance Reports .

Not sure if they are compatible with SQL 2008, but worth checking out.

Answer 4

Will SQL Server Profiler do what you need? I have not used 2008 yet, so I don’t know if there is a tool there, but if I suppose you can set up tracing for log requests that meet certain criteria (for example, those that execute and drive the CPU above a certain threshold).

We used this in our project, and it did a good job of helping us eliminate poorly executed requests (although do not leave it full-time, rely on common Windows performance counters to track health).

Answer 5

SQL Server 2008 introduced a new Performance Studio tool, which relies on dynamic management views that are automatically supported by the server, giving you an overview of server performance. It is worth checking out.

Answer 6

(DELL) Quest SQL Optimizer for SQL Server 9.0 introduces an SQL search module that allows users to find the most resource-intensive SQL on your SQL Server. https://support.quest.com/softwaredownloads.aspx?pr=268445262