1 / 9

Understanding Performance Bottlenecks using Performance Dashboard

Understanding Performance Bottlenecks using Performance Dashboard. Amit Banerjee Support Escalation Engineer Microsoft Corporation SQL Server FAQ: http://blogs.msdn.com/sqlserverfaq TroubleshootingSQL : http://troubleshootingsql.com Twitter: @ banerjeeamit | @ MicrosoftSQLCSS. Agenda.

kirsten
Download Presentation

Understanding Performance Bottlenecks using Performance Dashboard

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Understanding Performance Bottlenecks using Performance Dashboard Amit Banerjee Support Escalation Engineer Microsoft Corporation SQL Server FAQ: http://blogs.msdn.com/sqlserverfaqTroubleshootingSQL: http://troubleshootingsql.com Twitter: @banerjeeamit | @MicrosoftSQLCSS

  2. Agenda • What is Performance Dashboard • Installation and usage • What can it be used for? • Demo

  3. What is Performance Dashboard A set of RDLs deployed using custom reports feature in Management Studio which help resolve the most common performance issues, such as blocking, high CPU usage, latch contention and disk I/O bottleneck, without having to execute T-SQL queries.

  4. Installation and Usage • Install the Performance Dashboard Reports from Microsoft Downloads site • Once you install the above, go to \Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboardand run the setup.sql script against the SQL instance that you want to monitor the performance for. Right click on the Server Name in Object Explorer -> Reports -> Custom Reports and select performance_dashboard_main.rdl

  5. What can it be used for? • CPU bottlenecks (which queries are consuming the most CPU and Plan for this query) • IO bottlenecks (which queries are performing the most IO and Plan for this query). • Index recommendations generated by the query optimizer (using Missing Indexes DMV feature) • Blocking • Latch contention and other Wait Types

  6. What can I find? CPU usage based on Ring Buffer output Blocking information based on sys.dm_exec_requests output All hyperlinks allow various levels of drill-downs to get query-text and the query plan along with connection properties

  7. What can I find? Dashboard Main report -> Click on the waits showing up with the highest count to get to this report Further drill-down is available using Session ID or Query-text columns

  8. References • Download link: SQL Server 2005 Performance Dashboard • Configuring Performance Dashboard for SQL Server 2008

  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 accuracy 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.

More Related