In the previous part of this article, we described SQL Server Data Collector, its features, and pre-defined collection sets. We showed what data is collected and described the mechanisms used to collect, store, and purge the saved records. In this part, we will give detailed steps to provide all necessary pre-requests and set up Data Collector

How to set up Data Collector to monitor performance

Data Collector is supported only in Microsoft SQL Server 2008 and higher, in the Enterprise, Standard, Business Intelligence, and Web editions. SQL Server Integration Services must be installed

There are several features you should set up and/or enable: SQL Server Agent, Management Data Warehouse, and Data Collection

1. Make sure SQL Server Agent is running. If not, right-click it and select Start
2. In SQL Server Management Studio Object Explorer, expand Management

3. Right-click Data Collection and select Configure Management Data Warehouse

   

4. Select Create or upgrade a management data warehouse as a task to perform

   

5. Click Next

6. Select the SQL Server instance where the captured data will be stored. It’s recommended to select a different server from the one where data will be captured, to avoid capturing data for the storage database and reduce overhead

   It’s also recommended to have a database that will only store collected data. If you haven’t created it earlier, click New and do it here

   

7. Click Next

8. The Management Data Warehouse (MDW) uses three database roles: mdw_admin, mdw_reader, and mdw_writer. Map the appropriate roles to existing SQL Server logins, or click New Login to create a new login first

   “The mdw_admin role will allow users to read, write, update and execute SQL Server Agent which can purge and cleanup data available within a MDW database.
   The mdw_reader role will allow users to read data which is available within a MDW database.
   The mdw_writer role will allow users to write and upload data to a MDW database” [1]

   

9. Click Next
10. Once MDW is configured, right-click Data Collection and select Configure Management Data Warehouse again

11. This time, select the Set up data collection option

    Note that this sequence of actions and selecting the same option again is necessary. First time, you have to create the MDW. In the second run, you configure the SQL Server instance for data collecting. If you run the Set up data collection option without creating the MDW first, there will be no databases in the drop-down list, you will not be able to create a new one in this wizard and configure data collecting

    Select the DataCapture database created earlier. The Cache directory is the directory where data is collected locally before uploading into the MDW. This option is used for the collection sets that upload data in cached mode

    

Data Collection pre-defined sets

To see the data collection sets created by Management Data Warehouse Wizard, expand Data Collection in Object Explorer

Three data collection sets are created. They collect the data that is commonly needed to diagnose and troubleshoot performance issues. Each collection set can be manually stopped and started, and the data collection and upload into the MDW can be manually initiated. Data is collected for all databases on the SQL Server instance. That’s why it’s not recommended to have the MDW on the same instance

The Disk Usage data collection set collects information about disk space used by the database data and log files

It has two collection items – one for collecting data for the data files (MDF, NDF), and the other for the transaction log file (LDF)

“The collection set gathers the following data:

• Snapshots of data file sizes obtained from the sys.partitions and sys.allocation_units views.
• Snapshots of log file sizes obtained from the DBCC SQLPERF (LOGSPACE) command.
• Snapshots of I/O statistics from the sys.dm_io_virtual_file_stats function.”[2]

Code for each of the collection items is shown in the Input parameters field. As the Collection Frequency column shows, data for both collection items is collected every 60 seconds

By default, collected data is uploaded into the MDW every 6 hours. To change this behavior, click Pick and select one of existing schedules, or click New and create a new one

The data collection and upload mode can also be changed

By default, collected data is kept in the MDW for 2 years. As keeping collected data for so long can take up much of hard disk space, it’s recommended to reduce this number. First, determine how far back you need the collected data for analysis. Then calculate how much space data collecting for all your SQL Server instances will take. Keep in mind that in high transaction databases, MDW growth can be 200 – 300 MB a day

The Query Statistics data collection set has a single collection item that collects information about query statistics, activity, execution plans, and code on the SQL Server instance

“This collection set collects data from the following sources:

• sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_stats and other related dynamic management views.
• The text of selected batches and queries.
• The plan of selected batches and queries.
• The normalized text of selected batches.”[2]

By default, it collects data every 10 seconds and uploads it in the cached mode every 15 minutes, as shown in the Uploads tab

The Server Activity data collection set collects information about resources used by the SQL Server instance (processor, memory, disk I/O, and network usage), SQL Server instance activity (batch requests, logouts, SQL compilations and recompilations, transactions, user connections, and logins) and waits

“This collection set collects data samples from the following dynamic management views:

• sys.dm_os_wait_stats
• sys.dm_os_latch_stats
• sys.dm_os_schedulers
• sys.dm_exec_sessions, sys.dm_exec_requests, sys.dm_os_waiting_tasks (using a joined query)
• sys.dm_os_process_memory
• sys.dm_os_memory_nodes

Additionally, data samples are collected from several system and SQL Server performance counters.”[2]

Also, several SQL Server Agent jobs that collect and upload the data are created

“The following rules govern job creation:

• Data collection by different collection sets are executed as separate jobs.
• Collection sets that use cached collection mode are scheduled as jobs that start when the SQL Server Agent starts. These jobs run continuously and are controlled by the data collector run-time component.
• Collection sets that use non-cached collection mode are scheduled as normal SQL Server Agent jobs. These jobs use a SQL Server Agent schedule that matches the schedule defined in the collection set. The actual duration of execution can be customized by the user.”[3]

As shown, Data Collection set up requires a number of steps that can be executed using SQL Server Management Studio options. The feature is not available in all Microsoft SQL Server editions, only in Enterprise, Standard, Business Intelligence, and Web. While the feature provides built-in capturing of the most commonly needed performance metrics, adding new metrics to data collecting requires coding. All databases on the SQL Server instance have to be monitored, which adds overhead. Unlike Activity Monitor, Data Collection enables storing captured data for a specified time period and thus enables detailed performance analysis

In the next part of this article, we will show the reports available in Microsoft SQL Server Management Studio, metrics collected, and how they can help with troubleshooting performance issues