In SQL Server 2008, a new feature called the Performance Data Collector was added. It has the ability to collect SQL Server-related performance data, store it in a database called the Management Data Warehouse, and then produce various performance reports based on the collected data.
By default, the Performance Data Collector includes three different data collection sets (Disk Usage, Query Statistics, and Server Activities) that are used to collect a wide variety of SQL Server 2008 performance data. One of the features of the Performance Data Collector is that you can create your own data collection sets, which allow you to decide what kind of data you want to collect and store in the Performance Data Collector’s Management Data Warehouse. For example, if you want, you can create your own custom data collection set to collect and store Profiler trace data. Once the data has been collected and stored in the Management Data Warehouse, you can then create queries or Reporting Services reports to analyze the data you have collected.
There are two ways to create your own custom data collection set to collect and store Profiler trace data. You can either write a custom Transact-SQL script from scratch that creates the custom data collector, or you can let Profiler write the script for you. Let’s see how Profiler can do this for us.
First, ensure that the SQL Server 2008 Data Collector has been properly configured and is running properly. Next, start SQL Server 2008 Profiler and load an existing Profiler trace definition, or create a new Profiler trace definition. Keep in mind that the SQL Trace Data Collection Set will be based on your trace definition, so choose an appropriate one.
Figure 1: Export a Profiler Trace to a Performance Data Collector SQL Trace Collection Set.
The next step is to export the Profiler trace definition into a Transact-SQL file that can be executed later to create the SQL Trace Collection Set. To do this, From the SQL Server 2008 Profiler, select “File|Export|Script Trace Definition|For SQL Trace Collection Set,” and a “Save As” dialog box appears, allowing you to name the file and to save it in any folder you prefer. Once the script has been saved, it can be opened in SSMS. For example, the Transact-SQL script that is created looks similar to this (it has been truncated because of its length).
Figure 2: Profiler can create a SQL Trace Collection Set for you automatically.
Before you can run this script and create the SQL Trace Collection Set for the Performance Data Collector, you need to make two changes in the code. If you look through the generated code, you will see these place holders:
'SqlTrace Collection Set Name Here'
'SqlTrace Collection Item Name Here'
You will need to replace these place holders with descriptive names. Once the code is changed and executed, you will see the new SQL Trace Collection Set in Performance Data Collection portion of SSMS, along with the three default collection sets that come with SQL Server 2008. At this point, you can enable the new custom collection set, and it will begin collecting Profiler trace data based on the criteria you specified in the Profiler template you used as the basis for creating it. If you like, you can create as many different SQL Trace Collection Sets as you want, with each one based on a different Profiler template.
Unfortunately, there are no built-in reports available to view the Profiler data you have collected. Because of this, you will need to create your own Transact-SQL scripts, or to create Reporting Service reports, to view and analyze the Profiler trace data.
One warning about creating your own custom SQL Trace Collection Sets, and that is they can use a lot of SQL Server resources and disk space when enabled. To minimize this impact, ensure that the Profiler trace definitions that you use to create your SQL Trace Collection Sets only collect the minimum number of events and data columns you need. In addition, only enable SQL Trace Collection Sets as needed; don’t run them all the time. If this is the first time you have tried this, you might want to first test this on a test SQL Server before implementing it on production server.