SQL Server 2008 Performance Studio

In a previous entry, I discussed how to configure SQL Server 2008's new Performance Data Collector. The data collector is responsible for maintaining performance-related information in an "easy to use" format that will allow DBAs to perform more effective performance tuning.

Configuring the Data Collectors

One of the new nodes in SQL Server Management Studio is the "Data Collection" node, which can be found under the "Management" top level node. Under the data collection node, there are 3 specific data collectors, "Disk Usage", "Query Statistics" and "Server Activity". These nodes make up the SQL Server 2008 "Performance Studio" and represent the individual data collectors that are working behind the scenes to collect data and import it into the Management Data Warehouse.

The data collectors work on an individual schedule that are configured through the data collector node. In order to access the configuration information, right-click on the data collector node and select "Properties". This will bring up the following dialog (This example shows configuration information for the "Disk Usage" collector):

In the General tab of the configuration, notice the cache options. Default configuration is that data is not cached and is scheduled to upload to the mgmt data warehouse every 6 hours. The real difference between cached and non-cached data collection is that the non-cached collection both collects and uploads the data on the same schedule. Non-cached data collection simply means that the data is periodically collected and stored on the disk (in the folder that you configured when you configured the data warehouse) and then uploaded to the data warehouse on a separate schedule. Generally speaking, you would use this option to reduce the load on busy systems. There is also a configuration area for "Collection Items". In this case, the items of interest are the data and log files. They have a default collection frequency of 5 seconds, and use a generic TSQL command in order to populate the data. The default query is shown in the input parameters configuration (shown below):

DECLARE @dbsize bigint 

DECLARE @logsize bigint 

DECLARE @ftsize bigint 

DECLARE @reservedpages bigint 

DECLARE @pages bigint 

DECLARE @usedpages bigint

SELECT @dbsize = SUM(convert(bigint,case when type = 0 then size else 0 end)) 

      ,@logsize = SUM(convert(bigint,case when type = 1 then size else 0 end)) 

      ,@ftsize = SUM(convert(bigint,case when type = 4 then size else 0 end)) 

FROM sys.database_files

SELECT @reservedpages = SUM(a.total_pages) 

       ,@usedpages = SUM(a.used_pages) 

       ,@pages = SUM(CASE 

                        WHEN it.internal_type IN (202,204) THEN 0 

                        WHEN a.type != 1 THEN a.used_pages 

                        WHEN p.index_id < 2 THEN a.data_pages 

                        ELSE 0 

                     END) 

FROM sys.partitions p  

JOIN sys.allocation_units a ON p.partition_id = a.container_id 

LEFT JOIN sys.internal_tables it ON p.object_id = it.object_id 

SELECT 

        @dbsize as 'dbsize',

        @logsize as 'logsize',

        @ftsize as 'ftsize',

        @reservedpages as 'reservedpages',

        @usedpages as 'usedpages',

        @pages as 'pages'

This is the query that is used to populate the data for disk size within the management data warehouse.

The other items to configure on the general tab are the account to use when collecting the data, and finally the retention settings..

Once the general settings are configured the data collectors are now ready to use. (You do not need to configure anything additional in the other tabs at this time)

Using the Management Studio Reports

Once the data collector is configured, you can either wait for a schedule to cycle, or you can right-click on the collector and choose "Collect and Upload Data Now" to get data into the Data Warehouse. Once data is present in the warehouse, you can view a report. To view a report, right-click on the collector and choose Reports, then Historical, and then the appropriate report for that node (for example, to view the disk usage report, click on the "Disk Usage" collector, choose Reports, Historical, "Disk Usage Summary", which will produce a report similar to the following:

The reports have hotlinks that allow you to drill into information. For example, you can see the details of a specific database by selecting the link in the report above, which will produce a report that looks like this:

One of the more interesting reports is the Server Activity report, which can be accessed by right-clicking the "Server Activity" node, selecting reports, historical and then Server Activity. It will produce a report that looks like:

Conclusion

The new performance studio included with SQL Server 2008 provides a wealth of information for DBAs and simplifies the task of monitoring the server.