Monitor the health of SQL Server (2005/2008)-from Microsoft Tetchnet

Original address:
Http://technet.microsoft.com/zh-cn/library/bb838723.aspx

Microsoft SQL Server 2005 provides tools to monitor the database. One way is to manage the view dynamically. The server state information returned by dynamic management view (DMV) and dynamic management functions (DMF) can be used to monitor the health of the server instance, diagnose problems, and optimize performance.

General server dynamic management objects include:

• Dm_db_*: Database and Database objects

• dm_exec_*: Executing user code and associated connections

• dm_os_*: Memory, lock, and schedule

• Dm_tran_*: Transactions and isolation

• dm_io_*: Network and disk input/output

This section describes some common queries that run against these dynamic management views and functions for monitoring SQL Server Health.

Excerpt some wonderful SQL as follows:

The following query shows the top 50 SQL statements with the highest average CPU usage.

Select TOP 50total_worker_time/execution_count as [Avg CPU time], (select SUBSTRING (TEXT,STATEMENT_START_OFFSET/2, ( case if Statement_end_offset =-1 Then LEN (CONVERT (nvarchar (max), text)) * 2 ELSE statement_end_offset End-statement_st Art_offset)/2) from Sys.dm_exec_sql_text (sql_handle)) as Query_text, *from sys.dm_exec_query_stats ORDER by [AVG CPU time ] DESC

The following query shows some operators that may be consuming significant CPU usage (such as '%hash match% ', '%sort% ') to identify suspicious objects.

Select *from       sys.dm_exec_cached_plans cross      apply Sys.dm_exec_query_plan (plan_handle) where       cast (query _plan as nvarchar (max)) like '%sort% '      or cast (query_plan as nvarchar (max)) like '%hash match% '

Run the following DMV query to view the CPU, scheduler memory, and buffer pool information.

Select cpu_count,hyperthread_ratio,scheduler_count,physical_memory_in_bytes/1024/1024 as PHYSICAL_MEMORY_MB, virtual_memory_in_bytes/1024/1024 as virtual_memory_mb,bpool_committed * 8/1024 as bpool_committed_mb,bpool_commit_t Arget * 8/1024 as Bpool_target_mb,bpool_visible * 8/1024 as Bpool_visible_mbfrom sys.dm_os_sys_info

The following sample query shows the first 25 stored procedures that have been recompiled. Plan_generation_num Indicates the number of times that the query has been recompiled.

Select Top 25sql_text.text,sql_handle,plan_generation_num,execution_count,dbid,objectid from Sys.dm_exec_query_ Stats across apply sys.dm_exec_sql_text (sql_handle) as Sql_textwhere plan_generation_num > 1order by Plan_generation_ Num DESC

The following DMV query can be used to find out which batches/requests generate the most I/O. The DMV query shown below can be used to find the top five requests that can generate the most I/O. Tuning these queries will improve system performance.

Select Top 5     (Total_logical_reads/execution_count) as Avg_logical_reads,    (total_logical_writes/execution_ Count) as Avg_logical_writes,    (Total_physical_reads/execution_count) as Avg_phys_reads,     Execution_count,     statement_start_offset as Stmt_start_offset,     sql_handle,     plan_handlefrom sys.dm_exec_query_stats  Order  by (Total_logical_reads + total_logical_writes) Desc

　　

Monitor the health of SQL Server (2005/2008)-from Microsoft Tetchnet