常规服务器动态管理对象包括：
-
dm_db_*：数据库和数据库对象
-
dm_exec_*：执行用户代码和关联的连接
-
dm_os_*：内存、锁定和时间安排
-
dm_tran_*：事务和隔离
-
dm_io_*：网络和磁盘的输入/输出
<一>、dm_os_*【内存、锁定和时间安排】
一、sys.dm_os_performance_counters
instance_name为数据库名称，cntr_value为数值，以下是常用的查询
SELECT instance_name,cntr_value FROM sys.dm_os_performance_counters WHERE counter_name='Active Transactions'
1、Active Transactions 查询当前某个数据库活动的事务
2、Cache Entries Count 当前缓存条目数
3、Cache Hit Ratio 缓存命中率 该值越高越好。如果持续低于80%，应考虑增加内存。 注意该参数值是从SQL Server启动后，就一直累加记数，所以运行经过一段时间后，该值将不能反映系统当前值。
4、Commit table entries 提交表条目数
5、Data File(s) Size (KB) 文件尺寸
6、Log Bytes Flushed/sec 刷新/秒的日志字节
7、Log File(s) Size (KB) 日志文件尺寸
8、Log File(s) Used Size (KB) 已使用日志文件尺寸
9、Log Flush Wait Time 日志刷新等待时间
10、Active Temp Tables 活动临时表
11、Average Latch Wait Time (ms) 平均闩锁等待时间（毫秒）
12、Avg. Time Between Batches (ms) 平均批次之间的时间（毫秒）
13、Batch Requests/sec 批量请求/秒
14、Broker Transaction Rollbacks 事务回滚
15、Buffer cache hit ratio 缓冲区高速缓存命中率
16、Checkpoint pages/sec 由要求刷新所有脏页的检查点或其他操作每秒刷新到磁盘的页数
17、Connection Memory (KB) 连接内存
18、Connection Reset/sec 连接重置间隔、秒
19、Logins/sec 每秒登录数
二、sys.dm_os_wait_stats
显示SQL Server中正在等待各种资源的线程的整体视图
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC
max_wait_time_ms----等待的最大时间
waiting_tasks_count---该等待类型的等待数。该计数器在每开始一个等待时便会增加
wait_time_ms--- 该等待类型的总等待时间（毫秒）。该时间包括 signal_wait_time_ms。
signal_wait_time_ms---正在等待的线程从收到信号通知到其开始运行之间的时差
等待类型：
sys.dm_os_wait_stats 显示已经完成的等待的时间。此动态管理视图不显示当前等待。
如果出现下列任一情况，则不认为 SQL Server 工作线程处于等待状态：
-
资源变得可用。
-
查询非空。
-
外部进程完成。
尽管线程不再处于等待状态，但是它不必立即开始运行。这是因为此类线程首先放入可运行工作线程的队列中，并且必须等待量程在计划程序中运行。
在 SQL Server 中，等待时间计数器的数据类型为 bigint，因此它们不会像 SQL Server 的早期版本中的等效计数器那样滚动进行计数。
执行查询期间的特定等待时间类型可以说明查询中存在瓶颈或失效点。同样，如果服务器级的等待时间较长或等待计数较多，说明服务器实例内交互查询交互中存在瓶颈或热点。例如，锁等待指示查询争用数据；页 IO 闩锁等待指示 IO 响应时间较慢；页闩锁更新指示表示文件布局不正确。
此动态管理视图的内容可通过运行以下命令来重置：
DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR);
GO
查询显示 CPU 平均占用率最高的前 100 个 SQL 语句
SELECT TOP 100
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