In this blog post, I will go through the steps needed to prepare for a proof of concept (POC) for using the SQL Server 2008 R2 Data Collector (DC) and Management Data Warehouse (MDW) reports (covered in part 2) to get you up and running. I’ll cover the installation of the new Query Hash Stats collection set from Bart Duncan and how to get the MDW reports installed on your SQL Server Reporting Services (SSRS) server. My assumption for the POC is that you’ll have around 30 systems that you’ll want to monitor.
The Query Hash Statistics collection set was created by Bart Duncan of Microsoft. At the tail end of the SQL Server 2008 release cycle, the Manageability team ran out of time to incorporate the late changes that the Engine team made to support the “Query Fingerprint” and “SQL Fingerprint” features. See Bart Duncan’s blog post – “Query Fingerprints and Plan Fingerprints (The Best SQL 2008 Feature That You've Never Heard Of)” for a deep understanding of how this works as well.
What it this means for the Data Collector is that the collection of Query Plans and Text is highly optimized with the new Query Hash Statistics collection set. In addition, Bart has created a set of reports which offers more insight into the most expensive queries running on your system.
Download the Query Hash Statistics project and unzip the files to a shared location accusable by the computer hosting the MDW and the computers hosting the SQL Server database engines that you’ll collect data from.
I’m going to assume that you’ve already installed SQL Server 2008 or SQL Server 2008 R2 Database Engine and Management Tools. Optionally you can install Reporting Services for running the RDL files that I’ll provide.
The MSDN topic “Getting Started with the Data Collector”, goes over the basic steps for creating the Management Data Warehouse database and the Data Collector, but there a few things that you should know before you begin.
After updating the MDW to support the new collection set, installing the collection set on the target instance, and copying the reports as instructed to the MDW server, you are ready to go.
The reports provided for the SQL Server Query Hash Statistics collection set replace the Query Statistics reports. To open the reports, connect SSMS to the SQL Server instance that hosts your MDW database, right-click on the MDW database in Object Explorer, and select "Custom Report..." from the Reports submenu. Browse to the location where you extracted the Query Hash Statistics files, and open MdwOverviewCustom.rdl. You will not need to manually browse to the .RDL file after this; you'll find the report name in the Reports context menu. The first time you open a custom report, SSMS will ask you to confirm that you trust the report.
You’ll want to wait a few hours after collecting data to run the reports so that you can start making sense of the data.
It’s that easy. In the next blog post, I’ll cover how to install the MDW reports on an SSRS server.
Be sure to follow me on Twitter at @billramo
Tremendous work with this blog. It was really convenient from a reader’s perspective. Lots of information is there. I love this place to visit. It has diversified contents.