Performance Monitoring and Tuning Tools

Tools play an important role in monitoring, troubleshooting, and tuning database performance. The basic performance monitoring toolset in SQL Server 2000 included System Monitor or Performance Monitor, SQL Profiler, the Current Activity folder in Enterprise Manager, system stored procedures and DBCC statements, and the showplan and statistics display in Query Analyzer. The ITW was the only tuning tool available in SQL Server 2000.

SQL Server 2005 continues to support and enhance SQL Profiler, Activity Monitor, showplan and statistics display, and Performance Monitor. In addition, the new dynamic management objects add a powerful capability to monitor the system state. In addition to graphical and textual execution plans, SQL Server 2005 supports using Profiler or SQL Server Management Studio to generate execution plans in XML format.

As mentioned earlier in this chapter, the ITW has been replaced with a more powerful, full-fledged application called DTA. Plan guides (by using sp_create_plan_guide) are a new tuning tool available in SQL Server 2005. Chapter 5 provides a detailed description of these tools.

SQL Server 2005 contains about 78 DMVs and about 9 dynamic management functions (DMFs). All the dynamic management objects belong to "the sys" schema, and at least two-part names are required to access these objects. DMVs can be referenced using two-part, three-part, or four-part names, and DMFs can be referenced using two-part or three-part names.

DMVs and DMFs are grouped into the following 12 categories:

  • .NET CLR

  • Execution

  • Full-text services

  • Index

  • IO

  • Query notifications

  • Replication

  • Service Broker


  • Database

  • Database mirroring

  • Transaction

DMV and DMF names begin with "dm_" and then are named consistently to identify the category they belong to. For instance, all the dynamic management objects in the .NET CLR category begin with "dm_clr_", execution-related objects begin with "dm_exec_", index-related objects begin with "dm_index_", and I/O-related objects begin with "dm_io_".

A complete description of all the dynamic management objects is beyond the scope of this chapter. However, Table 9.2 describes a few dynamic management objects that you can use to monitor and troubleshoot performance issues.

Table 9.2. Dynamic Management Objects

Dynamic Management Object



Provides information about the query execution plans that are cached by SQL Server for faster query execution.


Provides information about the connections established to SQL Server on various endpoints.


Contains one row per authenticated session on the SQL Server instance.


Provides aggregate performance statistics for cached query plans.


Provides detailed statistics about the operation of the SQL Server query optimizer.


Contains a row for each pending I/O in the system.


Contains a row for each module loaded into the server address space.


Provides a general snapshot of the health of the cache.


Enables you to view all entries in caches and their statistics.


Lists SQL Server 2005 Performance Monitor counters and their current value.


Provides information on the wait queue of tasks that are waiting on some resource.


Returns one row for every active request to the lock manager that has either been granted or is waiting to be granted (that is, the request is blocked by an already granted request).


Returns a single row that displays state information of the transaction in the current session.


Provides information about active transactions.


If the CLR is enabled, provides information such as CLR version, directory, state, and so on.


Returns a row for each CLR application domain in the server.


Contains a row for each CLR user assembly loaded into the server address space.

To try out some of the dynamic management objects, you can start SQL Server Management Studio and run the following script in a query window:

SELECT SUM(pagesused) * 8 AS 'Plan Cache (KB)'    FROM sys.dm_exec_cached_plans; SELECT protocol_type, count(*) AS 'Total Connections' FROM sys.dm_exec_connections GROUP BY protocol_type; SELECT login_name, client_interface_name, count(*) AS 'Total Sessions',    sum(memory_usage) AS 'Memory Usage' FROM sys.dm_exec_sessions GROUP BY login_name, client_interface_name; SELECT name, description, company, file_version, product_version FROM sys.dm_os_loaded_modules; SELECT [type], sum(single_pages_kb) AS 'Total Single Pages (KB)',    sum(multi_pages_kb) AS 'Total Multi Pages (KB)',    sum(virtual_memory_reserved_kb) AS 'Total Virtual Memory Reserved (KB)',    sum(virtual_memory_committed_kb) AS 'Total Virtual Memory Committed (KB)',    sum(awe_allocated_kb) AS 'Total AWE Allocated Memory (KB)'   FROM sys.dm_os_memory_clerks   GROUP BY [type]   ORDER BY 2 DESC, 3 DESC; SELECT * FROM sys.dm_os_performance_counters; SELECT * FROM sys.dm_os_tasks    ORDER BY context_switches_count DESC; SELECT * FROM sys.dm_clr_properties;

The first SELECT statement uses the sys.dm_exec_cached_plans DMV to calculate total memory taken by the plan cache. The second SELECT statement uses the sys.dm_exec_connections DMV to total the connections to the SQL Server over each protocol. The third SELECT statement uses the sys.dm_exec_sessions DMV to group sessions by login name and displays total sessions and total memory used by each login. The next query displays all the loaded modules by using the sys.dm_os_loaded_modules DMV. The next SELECT statement uses the sys.dm_os_memory_clerks DMV to display the total single-page, multi-page, virtual, and AWE memory being managed by each clerk. The final three SELECT statements use the sys.dm_os_performance_counters, sys.dm_os_tasks, and sys.dm_clr_properties DMVs to display the current SQL Performance Monitor counter data, tasks, and CLR properties, respectively.

Microsoft SQL Server 2005(c) Changing the Paradigm
Microsoft SQL Server 2005: Changing the Paradigm (SQL Server 2005 Public Beta Edition)
ISBN: 0672327783
EAN: 2147483647
Year: 2005
Pages: 150 © 2008-2017.
If you may any questions please contact us: