SQL Server 2008 : Managing Query Performance - Running the Standard Performance Reports

2/15/2011 7:54:10 PM
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.

Figure 1. List of standard reports in SQL Server Management Studio

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.

Figure 2. Performance – Top Queries by Average CPU Time Standard Report

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
(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],
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.

Top 10
SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
The latest Audi TT : New angles for TT
Era of million-dollar luxury cars
Game Review : Hearthstone - Blackrock Mountain
Game Review : Battlefield Hardline
Google Chromecast
Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
Michael Kors Designs Stylish Tech Products for Women
- First look: Apple Watch

- 3 Tips for Maintaining Your Cell Phone Battery (part 1)

- 3 Tips for Maintaining Your Cell Phone Battery (part 2)
Popular Tags
Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone