Black Belt Administration: Performance Dashboard for Microsoft SQL Server, Part I
March 19, 2007
About the Series ...
This article is a member of the series MSSQL Server Reporting Services. The series is designed to introduce MSSQL Server Reporting Services (Reporting Services), presenting an overview of its features, with tips and techniques for real-world use. For more information on the series in general, please see my initial Database Journal article, A New Paradigm for Enterprise Reporting. For the software components, samples and tools needed to complete the hands-on portion of this article, see BlackBelt Administration: Linked Reports in Report Manager, another article within this series.
About the Black Belt Articles ...
As I state in BlackBelt Components: Manage Nulls in OLAP Reports and other articles of this subseries, the Black Belt articles represent an attempt to minimize the setup required in simply getting to a point within an article where we can actually perform hands-on practice with the component(s) or method(s) under consideration. I typically accomplish this by using existing report samples or other prefabricated objects that either come along as part of the installation of the applications involved, or that are otherwise readily accessible to virtually any organization that has installed the related components of the Microsoft business intelligence solution. In the Black Belt Administration articles, I focus upon procedures and tools that specifically relate to the job of the Reporting Services Administrator, versus report authors and other developers, although in many real world scenarios, as most of us are aware, these roles often intersect.
In this article, we extend the meaning of samples, as well as the type of Administrator, a bit further. We use an installation file recently made available by Microsoft that allows us to implement an exciting new report pack with minimal effort. Because we will be running the installation package, certain system and permissions requirements apply. We will cover these in the Preparation section below.
For more information about the BlackBelt articles, see the section entitled About the BlackBelt Articles in BlackBelt Components: Manage Nulls in OLAP Reports.
On the heels of the release of Service Pack 2 for MSSQL Server 2005 comes what will, perhaps, be the most warmly received Reporting Services report pack to date, the Performance Dashboard for Microsoft SQL Server (Performance Dashboard Reports). The Performance Dashboard Reports are a set of custom report files designed to be run from within SQL Server Management Studio.
The Performance Dashboard Reports are targeted toward SQL Server Administrators and other users; the objective of the report set is to act as both a health monitoring and diagnostic tool. Although it relies upon Reporting Services definition files (.rdl), Reporting Services does not need to be installed to use the Performance Dashboard Reports. This custom report set relies upon SQL Server's dynamic management views (DMVs) as a data source, providing the wealth of data the dynamic management views contain, while insulating the viewers of the information from the views and the structures underlying them. No additional sources, data capture or tracing is required to access and use this storehouse of performance information. Other obvious benefits of using these prefabricated views are constant availability of the information they contain and their inexpensive nature (from the tandem perspective of collection and querying) as a source of server monitoring.
The report set comes with a primary dashboard report file, as we shall see in the hands-on installation procedure that follows. This report file is loaded directly as a custom report in SQL Server Management Studio. The other Performance Dashboard Reports are accessed via the Reporting Services drillthrough mechanism, each path of which is initially entered when the user clicks a navigation link on the main page. The linkages are pre-constructed, and, once the primary dashboard report is loaded as a Custom Report in Management Studio, the rest of the reports work out of the box automatically, without any additional setup.
NOTE: For more information on Custom Reports, which debuted with Analysis Services 2005, Service Pack 2, see my article Administration and Optimization: Introducing Custom Reports, a member of the Database Journal Introduction to MSSQL Server Analysis Services series.
The end effect is that the primary dashboard gives a summarized status overview, while serving as a multi-directional launch point from which we can drill into the specific details we need to diagnose problems, inefficiencies, and other subjects of analysis and reporting with which we develop concerns. As an example, we can identify the presence of a bottleneck on a given server, and then investigate the details surrounding the bottleneck, capturing diagnostic data along the way to allow us to eliminate the problem, or to mitigate the condition by taking indicated actions (perhaps until we can get to a point where we can implement a better design or remove conflicting resource demands, etc.). An example of a commonly encountered challenge might be the identification of significant system waits for disk IO: we can access the dashboard, and subsequently drill to various reports as appropriate, to ascertain the identity of the most resource-hungry sessions, the queries that make up those sessions, and even the query plan associated with each constituent statement.
In addition to (and in conjunction with) CPU and IO bottlenecks (and which queries are consuming the most resources), typical performance problems whose resolution we might pursue with the Performance Dashboard Reports include:
NOTE: We discuss the individual, underlying members of the Performance Dashboard Reports, and more specific details of drillthrough operation, in Part II of this article.
In Part I and Part II of this article, we will implement the Performance Dashboard Reports, and then take them on a test-drive to gain an appreciation for the fact that, for a tiny investment in time to install them, we can experience significant returns through the performance enhancing information that the Performance Dashboard Reports provide. As a part of our examination of the Performance Dashboard Reports in this article, we will: