SQL Server 2008 R2 : Performance Monitoring Tools (part 2) - Installing and Configuring the Data Collector

MDW Reports

The MDW reports included in SSMS present the information gathered by the Data Collector in the following areas:

• Query performance statistics and use of indexes

• Server activity information, including waiting processes, memory usage, CPU/scheduler usage, and disk I/O

• Disk usage information

Each of the reports present a summary of the data at a high level, with the capability to drill down into the details. Sometimes the reports can provide information to help direct you to a solution for a performance problem. For example, if the query performance statistics report shows an extremely slow-running query, you can drill down through the report to expose more details on the query, right down to the query plan. The query plan could indicate that there is a missing index on that table, and creating that index could make a major difference in the query performance.

Installing and Configuring the Data Collector

Before you can use the Data Collector, you must complete the following tasks:

• Create logins and map them to Data Collector roles.

• Configure the management data warehouse.

The Data Collector has specific roles for data collection and management data warehouse tasks. The logins and roles required for data collection need to be created on the server that performs the data collection. Logins and roles for the MDW need to be created in the server that hosts the MDW. These logins and the MDW are created using the Configure Management Data Warehouse Wizard, which performs the following tasks:

• Creates the management data warehouse
  
  
• Installs the predefined System Data collection sets
  
  
• Maps logins to management data warehouse roles
  
  
• Enables data collection
  
  
• Starts the System Data collection sets
  
  

To invoke the Configure Management Data Warehouse Wizard, perform the following tasks on the SQL Server instance where you want to host the MDW:

In the Select Configuration Task window, make sure the radio button for Create or Upgrade a Management Data Warehouse is selected and click Next. Specify the name of the server instance that will host the MDW and click on New to create the MDW database. This brings up the standard New Database dialog. Enter the name you want to use for the MDW database and specify the location of the database files if you want the database created in different drive or directory than the default data file directory.

Before saving your settings and creating the MDW database, display the Options page and make sure that the database is configured for Simple recovery mode. For the current release of the Data Collector, the management data warehouse should be created using the Simple recovery model, to minimize logging.

When you are satisfied with the database configuration, click on OK to create the MDW database. After the database is created and you are brought back to the Configure Management Data Warehouse Storage screen (see Figure 3), click Next to continue to the Map Logins and Users screen. On this screen, assign the appropriate MDW roles to your SQL Server users (see Figure 4). Any users who need to view the Data Collector reports need the mdw_reader role.

Figure 3. The Configure Management Data Warehouse Storage screen.

Figure 4. The Map Logins and Users screen.

By default, no user is a member of the MDW database roles. User membership in these roles must be granted explicitly. Members of the mdw_admin role have Read, Write, Update, and Delete access to the management data warehouse. Members of this role can change the management data warehouse schema when required (for example, adding a new table when a new collection type is installed) and run maintenance jobs on the management data warehouse, such as archive or cleanup. Members of the mdw_writer role can upload and write data to the management data warehouse; any Data Collector that stores data in the management data warehouse has to be a member of this role. Members of the mdw_reader role have Read access to the management data warehouse primarily for the purpose of supporting troubleshooting by providing access to historical data.

It is recommended that you create a new login for data collection and map it as shown in Figure 4.

After you map the users, click on Next to bring up the Complete the Wizard screen, which provides a summary of the tasks to be performed. If everything looks okay, click Finish to perform the configuration of the MDW, which includes running the installation script to install the required schema objects in the MDW.

After you have created the MDW and made it available, the next step is to begin data collection for one or more of your SQL Server 2008 instances. Right-click on the Data Collection node in Object Explorer and select the Configure Management Data Warehouse option again. On the Select Configuration Task screen (refer to Figure 2), select the Set Up Data Collection radio button and click Next. On the Configure Management Data Warehouse Storage screen (see Figure 5), specify the name of the server that hosts the MDW and the name of the MDW database created previously. When specifying the server, you can also specify which directory you want the Data Collector to use for its local file cache (again, if possible, this should be on a different drive than where your database data files reside to minimize I/O contention). If you leave the value blank, it uses the default SQL Agent TEMP directory.

Figure 5. The Configure Management Data Warehouse Storage screen when configuring data collection.

When you finish making your selections, click Next to bring up the Complete the Wizard screen, which provides a summary of the tasks to be performed. If everything looks okay, click Finish to have the wizard perform the configuration of the system collection sets and enable data collection.