监控 SQL Server (2005/2008) 的运行状况

Microsoft SQL Server 二〇〇五提供了有些工具来监督数据库。方法之一是动态管理视图。动态管理视图 (DMV)
和动态处理函数 (DMF)
重临的服务器状态新闻可用于监察和控制服务器实例的运维境况、会诊难题和优化质量。
健康服务器动态管理对象富含:
– dm_db_*:数据库和数据库对象
– dm_exec_*:施行客户代码和关系的连天
– dm_os_*:内部存款和储蓄器、锁定和岁月计划
– dm_tran_*:事务和隔开分离
– dm_io_*:网络和磁盘的输入/输出
此部分介绍为监督 SQL Server
运转意况而针对性那么些动态管理视图和函数运营的一些常用查询。
剪辑部分优良SQL如下:

下边包车型客车询问展现 CPU 平均占用率最高的前 50 个 SQL 语句。
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
上面的查询展现一些恐怕占用大批量 CPU 使用率的运算符(举个例子 ‘%Hash
Match%’、‘%Sort%’)以搜索困惑对象。

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%’

运维下边包车型客车 DMV 查询以查看 CPU、安排程序内部存款和储蓄器和缓冲池音信。
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

上面包车型大巴身体力行查询显示已再一次编写翻译的前 25 个存款和储蓄进度。plan_generation_num
提示该查询已再一次编写翻译的次数。

select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid 
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

上边包车型客车 DMV 查询可用以查找哪些批管理/要求生成的 I/O 最多。如下所示的 DMV
查询可用以查找可生成最多 I/O 的前五个恳求。调解这个查询将加强系统品质。

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