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

  • SQLOS

  • 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

Description

dm_exec_cached_plans

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

dm_exec_connections

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

dm_exec_sessions

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

dm_exec_query_stats

Provides aggregate performance statistics for cached query plans.

dm_exec_query_optimizer_info

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

dm_io_pending_io_requests

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

dm_os_loaded_modules

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

dm_os_memory_cache_counters

Provides a general snapshot of the health of the cache.

dm_os_memory_cache_entries

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

dm_os_performance_counters

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

dm_os_waiting_tasks

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

dm_tran_locks

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

dm_tran_current_transaction

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

dm_tran_active_transactions

Provides information about active transactions.

dm_clr_properties

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

dm_clr_appdomains

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

dm_clr_loaded_assemblies

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

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net