6038.understanding performance bottlenecks using performance dashboard
TRANSCRIPT
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 1/9
Amit BanerjeeSupport Escalation EngineerMicrosoft Corporation
SQL Server FAQ: http: //blogs.msdn.com/sqlserverfaq TroubleshootingSQL: http://troubleshootingsql.com Twitter: @banerjeeamit | @MicrosoftSQLCSS
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 2/9
What is Performance Dashboard
Installation and usage
What can it be used for?Demo
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 3/9
A set of RDLs deployed using custom reports featurein Management Studio which help resolve the mostcommon performance issues, such as blocking, high
CPU usage, latch contention and disk I/O bottleneck,without having to execute T-SQL queries.
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 4/9
Install the Performance Dashboard Reports from
Microsoft Downloads siteOnce you install the above, go to \ProgramFiles\Microsoft SQLServer\90\Tools\PerformanceDashboard and run
the setup.sql script against the SQL instance thatyou want to monitor the performance for.
Right click on the Server Name inObject Explorer -> Reports ->
Custom Reports and selectperformance_dashboard_main.rdl
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 5/9
CPU bottlenecks (which queries are consuming themost CPU and Plan for this query)
IO bottlenecks (which queries are performing themost IO and Plan for this query).
Index recommendations generated by the queryoptimizer (using Missing Indexes DMV feature)
Blocking
Latch contention and other Wait Types
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 6/9
CPU usage based on Ring Buffer output
Blocking information based onsys.dm_exec_requests output
All hyperlinks allow various levels of drill-
downs to get query-text and the queryplan along with connection properties
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 7/9
Dashboard Main report -> Click on the waits showing up with the highestcount to get to this report
Further drill-down is available using Session ID or Query-text columns
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 8/9
Download link: SQL Server 2005Performance Dashboard
Configuring Performance Dashboard forSQL Server 2008
7/27/2019 6038.Understanding Performance Bottlenecks Using Performance Dashboard
http://slidepdf.com/reader/full/6038understanding-performance-bottlenecks-using-performance-dashboard 9/9
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market
conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accu racy of any information provided after the date of this presentation.MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.