Microsoft
has written some out-of-the-box reports that offer some useful
information when looking for problematic queries. You can write many of
the same kinds of reports using DMVs, but the canned reports are a quick
and easy way to find some of your worst performing queries.
You can view a list of reports
that apply to the entire instance or to a specific database. To display
the list of reports shown in Figure 1, right-click on the SQL Server instance in SQL Server Management Studio, select Reports =>
Standard Reports. To display a list of reports for a specific database,
right-click on the database instead of the Server Instance.
You can see a sample of the Performance - Top Queries by Average CPU Time report in Figure 2. If you look at the worst performing query.
Alternatively, you can use DMVs and execute the query in Listing 16-5
to display the same information. But not only is the standard report in
a format that is easier to read, it is also a lot easier to execute. We
don't know about you, but unless we have the script handy, we wouldn't
want to have to remember the syntax required to write the query in Listing 1.
If you do want to have the scripts on hand, you can start a trace in
Profiler, run a report, and capture the query that the report executed.
Example 1. Top Ten Queries by Average CPU Time
SELECT TOP 10
creation_time,
last_execution_time,
(total_worker_time+0.0)/1000 as total_worker_time,
(total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime],
total_logical_reads as [LogicalReads],
total_logical_writes as [LogicalWrites],
execution_count,
total_logical_reads+total_logical_writes as [AggIO],
(total_logical_reads+total_logical_writes)/(execution_count+0.0) as [AvgIO],
case when sql_handle IS NULL
then ' '
else ( substring(st.text,(qs.statement_start_offset+2)/2,
(case when qs.statement_end_offset = −1
then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
end - qs.statement_start_offset) /2 ) )
end as query_text,
db_name(st.dbid) as db_name,
st.objectid as object_id
FROM sys.dm_exec_query_stats qs
CROSS apply sys.dm_exec_sql_text(sql_handle) st
WHERE total_worker_time > 0
ORDER BY [AvgCPUTime] desc
|
One advantage that DMVs have
over the reports is that you can run the DMVs from a scheduled job and
insert the output in a table. Every time SQL Server is restarted, you
lose all of the useful information provided by the DMVs. Storing the
output from DMVs in a table allows you to take snapshots of the DMVs so
you can analyze the data over a long period of time without worrying
about losing all of your information if SQL Server is restarted.
