SQL optimization (DMV)

Monitor the running status of SQL Server (2005/2008)-from tetchnet

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

Microsoft SQL Server 2005 provides tools to monitor databases. One way is to manage the view dynamically. The server status information returned by the dynamic management view (DMV) and dynamic management function (DMF) can be used to monitor the running status of server instances, diagnose problems, and optimize performance.

Common Server dynamic management objects include:

• Dm_db _ *: database and database object

• Dm_exec _ *: Execution userCodeAnd associated connections

• Dm_ OS _ *: memory, lock, and schedule

• Dm_tran _ *: transaction and isolation

• Dm_io _ *: Network and disk input/output

This section describes common queries for these dynamic management views and function operations to monitor the running status of SQL Server.

The following is an excerpt of some wonderful SQL statements:

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

 

Select   Top   50
Total_worker_time / Execution_count As   [ Avg cpu time ] ,
( Select   Substring ( Text , Statement_start_offset / 2 ,( Case   When Statement_end_offset =   - 1   Then   Len ( Convert ( Nvarchar ( Max ), Text )) *   2   Else Statement_end_offset End   - Statement_start_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 occupy a large amount of CPU usage (such as '% hash match %' and '% 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 CPU and PlanProgramMemory 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_target *   8   /   1024   As Bpool_target_mb,
Bpool_visible *   8   /   1024   As Bpool_visible_mb
From SYS. dm_ OS _sys_info

The following example shows the first 25 stored procedures that have been re-compiled.Plan_generation_numIndicates the number of times the query has been re-compiled.

Select   Top   25
SQL _text. Text ,
SQL _handle,
Plan_generation_num,
Execution_count,
Dbid,
Objectid
From SYS. dm_exec_query_stats
Cross Apply SYS. dm_exec_ SQL _text (SQL _handle) As SQL _text
Where Plan_generation_num >   1
Order   By Plan_generation_num Desc

The following DMV query can be used to find which batches/requests generate the most I/O. The following DMV query can be used to find the first five requests that can generate up to I/O. Adjusting these queries improves 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_handle
From SYS. dm_exec_query_stats
Order   By (Total_logical_reads + Total_logical_writes) Desc