Search Wiki:

Quick Start Guide


Prerequisites

Parts of Query Hash Statistics rely on the SQL Server Data Collector (DC) to harvest query stats data and upload it to a database for analysis. If you haven’t already created a Management Data Warehouse database and enabled data collection on your SQL Server instance, do this now:
  1. Decide where to host the MDW database. The MDW database host instance can be the same as the instance that you want to monitor, or it can be a different SQL instance.
  2. Create your MDW database:
    1. Connect SQL Server Management Studio (SSMS) to the SQL Server instance that you want to host the MDW database. If Object Explorer is not already visible, open it from the View drop-down menu.
    2. Right-click on the "Management\Data Collection" node, and select "Configure Management Data Warehouse".
    3. Choose the first option in the wizard, "Create or upgrade a management data warehouse", and click Next.
    4. On the next dialog, click the New button to create the MDW database. This database will be referred to in this documentation as "MDW", but you can name it something else if you wish. It is OK to accept the default options for the remainder of the wizard.
  3. Enable Data Collection:
    1. Connect SSMS to the SQL Server instance that you want to collect query hash statistics from. (This may be the same instance as the MDW host, but it may also be a different SQL Server instance if you wish.) Launch the "Configure Management Data Warehouse" wizard again, but this time select the second option in the wizard, "Set up data collection". Choose the server that hosts the MDW database, and then select the MDW database in the drop-down list box.
    2. The Query Hash Statistics collection set is very similar to the built-in "Query Statistics" system collection set. If you find that the Query Hash Statistics collection set meets your needs, you may wish to stop the "Query Statistics" system collection set to minimize data collection overhead and prevent the collection of redundant data.
  4. For more information about creating a MDW database and enabling data collection, see the Data Collection topics in Books Online.

Installing Query Hash Statistics

Once you have created a MDW database and have enabled Data Collector on the instance where you will run Query Hash Statistics:
  1. Download the latest release of Query Hash Statistics: You can find this under the Downloads tab, above. Extract the files to a directory on a machine that has SSMS installed -- say, "C:\QueryHashStats".
  2. Configure the MDW database to receive data from the Query Hash Statistics collection set. To do this, connect SSMS to your MDW host instance and open the script QueryHashStatsSetupMdw.sql in a query window. Important: You must manually change the database context to your MDW database before running the script. Execute the script in this database. Check for errors.
  3. Configure the Target Instance: Using SQLCMD.exe, execute the script QueryHashStatsSetupTarget.sql on your target instance (the instance that you want to collect data from). There should be no errors, but this script will return a resultset, which you can ignore. To run the target server setup script via SQLCMD, open a command prompt and run a command like the one below (update the -S parameter value with your target SQL instance name). This command line assumes that your current NT account has sysadmin permissions on the target SQL Server instance. If you want to use a SQL authentication login like 'sa', instead, replace the "-E" parameter with "-U login -P password".
SQLCMD -E -i C:\QueryHashStats\QueryHashStatsSetupTarget.sql -S <target_sql_server_instance>


The collection set takes a snapshot of query statistics once per minute, and it uploads its data once every five minutes. Wait about ten minutes before attempting to use the reports. Once at least ten minutes has elapsed:

Open the MDW Overview report: To open a custom report, connect SSMS to your MDW host SQL Server instance, right-click on the MDW database node in Object Explorer, and select "Custom Report..." from the "Reports" submenu. Browse to the location of your query hash statistics reports and open MdwOverviewCustom.rdl. You will not need to manually browse to the report 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.

Once the report has opened, click on the "Query Hash Statistics" link to launch the analysis reports for the Query Hash Statistics collection set.



We hope Query Hash Statistics makes your day a little bit easier. Please let us know about your experiences -- good or bad -- in the Discussions tab, above. If you have a suggestion or think you've found a bug, please open a new issue under the Issue Tracker tab. Thanks!


(Continue reading about the query fingerprint reports, or return to the top.)
Last edited Nov 30 2010 at 4:44 AM  by bartd, version 22
Comments
VladimirMagalhaes wrote  Jun 21 2011 at 11:14 PM  
I have done all the steps to install the Query Hash Statistics and they all worked, but now when I try to run the report I get a "Invalid object name " 'core.spapshots' " error.

Any idea of what could be the cause of this?

Verone wrote  Feb 23 2012 at 8:40 PM  
Vladimir, not sure if your still following this thread but I had the same error and here is what I did: I ran the QueryHashStatsSetupTarget.sql script directly in SSMS in each target instance that I wanted to monitor, rather then using SQLCMD, just to visually see the scripts working. Then I opened the QueryHashStatsReports.sln directly in Visual Studio. I had to upgrade it to the current version that I use. Then I used Solution Explorer and double clicked on the Shared Data Source: MDW.rds and pointed it to the collections server where my MDW database is installed in my environment.

This solved my problem and I was back in business.

I hope this helps!

Updating...
Page view tracker