Performance Management Tools in SQL Server 2008

In addition to configuration and management tools discussed previously, SQL Server 2008 ships with three specialized tools for capturing, analyzing, and troubleshooting performance data.

Profiler

SQL Server Profiler is a graphical tool that acts as an interface to the SQL Trace Application Programming Interface (API). Profiler allows you to define SQL Server events, as shown in Figure 1, that you want to capture information on. You can also specify filtering options to target your data capture within the events that you have specified. You will learn about Profiler in the article, “Performance and Data Capture Tools,” which can be found on the Microsoft Press Online Windows Server and Client Web site at www.microsoft.com/learning/books/online/serverclient.

Figure 1. SQL Server Profiler Trace Properties dialog box Events selection

Database Engine Tuning Advisor

Database Engine Tuning Advisor (DTA) analyzes a query workload and makes recommendations on index and partitioning changes that can improve the performance of your queries (as shown in Figure 2). You will learn about capturing a query workload in the article, “Performance and Data Capture Tools,” which can be found on the Microsoft Press Online Windows Server and Client Web site at www.microsoft.com/learning/books/online/serverclient, and how to apply DTA in the article, “Performance Analysis Tools,” which can be found on the Microsoft Press Online Windows Server and Client Web site at www.microsoft.com/learning/books/online/serverclient.

Figure 2. DTA tuning workload definition

Performance Studio

Performance Studio is the name given to a collection of technologies within SQL Server 2008 that are targeted at the analysis of enterprise-wide performance data. The components of Performance Studio are:

• Performance Data Warehouse

• Data Collectors

• Performance Reports

The Performance Data Warehouse is a database that you create. Data Collectors are SSIS packages, which are executed on a scheduled basis using SQL Server Agent. Performance Reports are a set of Report Designer reports written against the data stored in the Performance Data Warehouse.

You will learn how to configure, manage, and leverage the components of the Performance Studio in the article, “Performance Analysis Tools,” which can be found on the Microsoft Press Online Windows Server and Client Web site at www.microsoft.com/learning/books/online/serverclient.