There are a number of helpful links on the net to get you started using the Management Data Warehouse (MDW) which was released in SQL 2008, and continued in SQL 2008 R2. However I have not seen a lot written to date on the 'SQL Trace Collection Set' which has prompted this blog entry. The 'SQL Trace Collection Set' can provide unique and insightful analytics, which can be used for highly targeted performance tuning, and for examining performance trends over time.
I'll start by pointing you into the right direction for creating the SQL Server Management Data Warehouse, and then turn the focus to configuration of the SQL Trace Collection Set.
Setting-up the Management Data Warehouse (MDW) and Data Collection Components
The MDW is designed to collect performance related statistics that is stored in a SQL Server 2008 database. SQL Server 2008 has a ‘Configure Management Data Warehouse’ wizard that helps you to get started with this SQL Server performance monitoring extension:
Note: You will want to initiate this set-up process on the SQL Server instance that you would like to use as the official ‘Management Data Warehouse’ repository. This instance can be separate from the SQL Server instance(s) that are being monitored for this performance data.
This wizard will step you through creating the MDW database, and configuring the standard System Data Collections Sets (Disk Usage, Query Activity, and Server Activity). For ‘step-by-step’ assistance on using this wizard refer to these articles on msdn and/or Database Journal.
Note: Any name can be provided for the resulting ‘Management Data Warehouse’ database. As a matter of practice, ‘MDW’ is often used, and is assumed in the following examples. Also, be aware that once you configure and enable the MDW there is no method to remove the corresponding SQL Agent jobs which are created. You can disable data collection, which will also disable the SQL job, but they will continue to reside in SQL Agent’s list of jobs.
Once you have the configured the Management Data Warehouse for your environment, you will want to manually add the ‘SQL Trace Collection Set’ since it is not automatically set-up by the MDW wizard. As mentioned earlier, the ‘SQL Trace’ collection set will provide key metrics such as ‘Query Duration’, ‘Database Reads’, and ‘CPU Time’ that will be populated within the MDW database.
Configuring the 'SQL Trace' Data Collection Component
Albeit a slight detour, the best way to set-up the ‘SQL Trace Collection Set’ for the Management Data Warehouse is to use ‘SQL Server Profiler’ and create an active trace. This trace definition will then be saved as a script which can be used to configure the SQL Trace data collector. To get started, start SQL Server Profiler:
Once in ‘Profiler’, click on ‘File’, ‘New Trace…’, and after connecting to SQL Server you will be able to set the desired columns and events to capture. The standard (default) template automatically sets most of the events and columns of interest for query monitoring. To minimize server overhead for data collection, you will most likely want to remove all events with the exception of ‘RPC:Completed’ and ‘SQL:BatchCompleted’. You can click on the ‘Events Selection’ tab to specifically choose the items to collect:
After clicking on the Run button, you can then immediately stop the trace, since you only need the extract the definition. You can then export the trace definition script for a trace collection set as shown below:
The resulting script can then be loaded into SQL Server Management Studio, and executed. However before executing, you will want to provide a friendlier name for the collection set, such as ‘SQL Trace’ which is highlighted in the code snippet below:
...
-- *** with the name you want to use for the collection set.
-- ***
DECLARE @collection_set_id int;
EXEC [dbo].[sp_syscollector_create_collection_set]
@name = N'SQL Trace',
@schedule_name = N'CollectorSchedule_Every_15min',
@collection_mode = 0, -- cached mode needed for Trace collections
@logging_level = 0, -- minimum logging
@days_until_expiration = 5,
...
Note: the parameter option above:
‘@days_until_expiration = 5’ determines how many days the trace data will be stored in the MDW database before it is automatically deleted. This value can be increased for greater data retention, but keep in mind that this also increases the monitoring overhead of the MDW database.
After running this script you will be see a new Data Collection set called ‘SQL Trace’ (right-click and refresh the ‘Data Collection’ container if necessary). To start the collection set, right click the ‘SQL Trace’ collection, and then click ‘Start Data Collection Set’
You can also double-click on the SQL Trace ‘Data Collection’ to view its’ configured properties:
You will want to be aware of the configurable collection frequency, which in this case is every 900 seconds (15 min), and how long the data is retained in the MDW (the default script is set to 5 days). Because the resulting Trace table ‘[snapshots].[trace_data]’ can be very large, especially in high volume environments, you may prefer to reduce the number of days for data retention. This however lessens the ability to trend reoccurring query patterns. To retain the ability to trend, another practice to consider is to create a simple ETL routine to export (and perhaps summarize) only the trace rows of interest. This is especially beneficial when trending stored procedures, or query patterns over a long period of time.
Note: You can also reduce the size of the resulting trace table by adding filters to the original trace, created in Profiler, and used to create the collection set.
When you start data collection sets, corresponding SQL Agents jobs are created to schedule and manage the data collection in the MDW. The following query will return details of collection sets, including the ‘collection_set_id’ which can be used to correlate with the actual SQL Agent jobs:
Following is a comprehensive list of the SQL Agent jobs that are created to collect, upload, aggregate, and purge data stored in the Management Data Warehouse (MDW) database:
This completes the set-up of the MDW and standard data collectors. In approximately 15 minutes, or based on the collector's schedule, you should notice rows in the table called:
[snapshots].[trace_data] in the MDW database.
Querying the [trace_data] Table
Based on the earlier ‘SQL Trace’ configuration, the key measures we are collecting in this MDW table consist of
- CPU Time
- Disk I/O
- Query Duration
These were also set to be recorded based on the completion of the following events:
- RPC:Completed
- SQL:BatchCompleted
This table you can be queried based on any of the key metrics listed above. For example, the top 10 queries based on ‘duration’ could be retrieved by using the following code:
SELECT TOP 10 *
FROM [snapshots].[trace_data]
ORDER BY [Duration] DESC -- or BY [Reads], or [CPU]
The result set should list the 10 longest running queries that completed based on data collected from all the SQL server instances monitored by this Management Data Warehouse (scroll to the right to see the ‘Server Name’ column). You may chose to filter this query based on a single 'Server Name'.
You may find it helpful to group by a substring of the [TextData] column. This may better illustrate reoccurring queries that perhaps vary within the WHERE clause, but are based on the same table joins and columns. An example of this, using just the first 80 characters of the [Text Data] column, and based on ‘disk reads’ is displayed below:
SELECT TOP 10
COUNT(*) AS [RunCount]
, SUM([Reads]) AS [SumReads]
, LEFT([TextData],80) AS [TextShort]
FROM [snapshots].[trace_data]
GROUP BY LEFT([TextData],80)
ORDER BY [SumReads] DESC
Note: the technique of grouping by the first
80 characters is used here in an attempt to find re-occurring code. You may need to alter this method to better suit the SQL code patterns in your database environment. The objective is to find re-occurring code which carries a high disk I/O (read) cost, and also be able to then identify the origin/source of the query for subsequent tuning efforts.
Another option is to trend SUM and AVG query metrics over time. The following query again groups by the first 80 characters of the [TextData] column to spot a reoccurring query pattern, and then uses a secondary ‘group by’ on date to show a performance trend. A ‘like’ predicate is also placed on the [TextData] column to narrow the query to those referencing the [Sales] table:
SELECT CONVERT(VARCHAR(10), [EndTime], 120) AS [DisplayDate]
, LEFT([TextData],80) AS [TextShort]
, COUNT(*) AS [TimesRun]
, SUM(Duration) AS [SumDuration]
, SUM(Reads) AS [SumReads]
, SUM(CPU) AS [SumCPU]
, AVG(Duration) AS [AvgDuration]
, AVG(Reads) AS [AvgReads]
, AVG(CPU) AS [AvgCPU]
FROM [snapshots].[trace_data]
WHERE [TextData] LIKE '%Sales%'
GROUP BY LEFT([TextData],80), CONVERT(VARCHAR(10), [EndTime], 120)
ORDER BY [TextShort], [DisplayDate]
This I hope gives you an idea of the possibilities for analytics using the [trace_data] table, which is enabled via the 'SQL Trace Collection Set' of the MDW. In a follow-up article (under construction), we will examine additional performance based reporting options that are available when collecting this 'SQL Trace' data.