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:
The following sections present an overview of these changes, which are discussed in great detail in Chapter 9.
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:
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.