Performance Monitoring and Tuning Enhancements

In SQL Server 2000, SQL Profiler and PerfMon were the two primary tools used to monitor and troubleshoot performance issues. Some other ways to monitor performance included using system stored procedures such as sp_who2 and sp_lock, DBCC statements such as DBCC INPUTBUFFER and DBCC OPENTRAN, and current activity monitor in Enterprise Manager. As far as performance tuning is concerned, the only tool available in SQL Server 2000 was the Index Tuning Wizard.

For SQL Server 2005, Microsoft has invested a lot of time and resources in improving the existing tools and introducing new tools to monitor and tune the performance. These include the following:

• Several improvements and new features have been introduced in the Profiler tool.

• The Index Tuning Wizard has been replaced with a full-fledged application called Database Engine Tuning Advisor that does a better job of making recommendations on physical database design, works well with large databases and large workloads, contains more functionality, and is more manageable.

• There are new dynamic management views (DMVs) and dynamic management functions (DMFs) to view the current state of the SQL Server 2005 system.

• There are new ways to monitor and avoid blocking and deadlocking.

• SQL Server 2005 provides new Performance Monitor counters.

The following sections present an overview of these changes, which are discussed in great detail in Chapter 9.

Profiler Enhancements

SQL Profiler is still the primary tool for monitoring the activity on a SQL Server instance. The change in SQL Server 2005 is that Profiler now supports tracing of Analysis Services and Integration Services events, in addition to SQL Server.

A new feature called Performance Counters Correlation allows the correlating of Performance Monitor counter data, with the Profiler trace collected at the same time.

SQL Server 2005 Profiler defines a new event called a deadlock graph, which can be captured to monitor and view deadlock data as a picture. Figure 4.4 shows a deadlock graph as traced by the Profiler.

Figure 4.4. SQL Server 2005 Profiler provides several new events, such as deadlock graph.

Several new event categories and events have been added to Profiler to support the tracing of the new functionality, such as Service Broker and online index operation. Profiler also defines event classes to trace OLE DB events and deprecated features.

Profiler allows capturing of a showplan and saving it as XML. This XML showplan can be later analyzed using Query Editor in Management Studio. Third-party applications might be available in the future to allow various other operations, such as comparisons, using an XML showplan.

Profiler includes new replay options that allow the controlling of things such as maximum number of replay threads. Refer to Chapter 9 for more details on changes introduced in SQL Profiler.

Database Engine Tuning Advisor

The Index Tuning Wizard from previous releases of SQL Server is now being replaced with a full-fledged GUI application (dtashell.exe) named Database Engine Tuning Advisor (DTA). DTA can also be invoked from the DOS command prompt (dta.exe). It can be used to tune the physical database design to maximize performance and to aid in manageability.

Tuning is generally an iterative process. With the Index Tuning Wizard in previous releases, it was a little difficult to go through the steps multiple times and review the results, and there was no way to compare and do what-if analysis. DTA is a full-blown application that provides the notion of sessions, which makes it is easier to iterate the tuning process, review and evaluate the recommendations, and view reports. Figure 4.5 shows DTA in action.

Figure 4.5. DTA contains significant improvements over the Index Tuning Wizard in terms of functionality, quality of recommendations, and ability to handle large databases and workloads.

Here is a summarized list of some of the important tuning advancements introduced in SQL Server 2005:

• In addition to providing recommendations regarding indexes and indexed views, DTA can make several other recommendations, such as how to range partition the data, indexes, and indexed views; including non-key columns with nonclustered indexes; XML indexes; and index rebuilding/reorganization suggestions to reduce the cost of index scans and seeks.

• The Index Tuning Wizard is restricted to workload on a single database and can tune a single database at a time. DTA, on the other hand, supports a workload that spans multiple databases, and in each DTA session, you can choose multiple databases to be considered for tuning.

• It is not recommended to run DTA on a production server during business hours. Depending on the workload and size of the database, DTA might impose significant tuning overhead on the server being tuned. The nice thing is that you can tell DTA when to stop. (Refer to Figure 4.5 and notice the very first option on the Tuning Options tab.)

• DTA session definition and tuning recommendation results can be exported as an XML document. The session definition XML file can later be provided to DTA at the command line (dta.exe) or as a GUI (dtashell.exe) application. The use of XML by DTA opens the door for third-party vendors to provide solutions that integrate with DTA to enhance and support additional functionality. The XSD schema for input and output XML is the same and is available in a file named dtaschema.xsd under the folder C:\Program Files\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\2003\03\dta. The month and year in the path might change when SQL Server is released.

• DTA allows you to evaluate recommendations, and when you are satisfied with recommendations, you can specify which ones to implement.

• Unlike Index Tuning Wizard, which only sysadmin server role members can use, DTA can be executed by any members of the db_owner database role.

DTA is discussed in detail in Chapter 9.

Dynamic Management Objects

SQL Server 2005 provides several dynamic management views and functions that essentially provide a real-time snapshot of internal memory structures that indicate the server state. In addition to Profiler and PerfMon, dynamic management objects are an essential tool for proactively monitoring SQL Server. If you run the following query in the Management Studio's Query Editor, you should see a list of all the available DMVs and DMFs:

SELECT name, type_desc FROM sys.system_objects WHERE name like 'dm_%';

Like catalog views, dynamic management objects also belong to the sys schema.

Here is a sample T-SQL script that illustrates a DMV and a DMF:

USE AdventureWorks; GO SELECT * FROM sys.dm_tran_locks; GO SELECT * FROM sys.dm_db_index_physical_stats    (DB_ID(), OBJECT_ID('Sales.Store'), NULL, 0, 'DETAILED'); GO

Further details on dynamic management objects can be found in Chapter 9.

The Blocked Process Threshold Setting

SQL Server 2005 introduces a new advanced sp_configure setting called blocked process threshold, which can be used to generate an event as soon as blocking happens on the server. The value of this configuration setting is initially set to 0, indicating that this feature is disabled. You can turn on the sp_configure show advanced options setting, change the value of this parameter to a number between 1 and 86400 (indicating 24 hours), and run RECONFIGURE WITH OVERRIDE to immediately activate the option.

Let's say you specified 10 as the value of this new configuration setting. Now if there is a blocking for 10 seconds, SQL Server 2005 generates an event that can be seen in Profiler and also can be captured by using the new event notification mechanism. Refer to Chapter 9 for more details and a demo of this feature.