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:
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.
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.