431.chapter14.working with SQL Server Performance-优快云博客

Lesson4 DMV and DMF

一、动态管理是如和函数返回可用于监视服务器实例的运行状况，诊断故障以及优化性能的服务器状态信息。其分为两种类型：服务器范围内的数据库范围内。

二、详细分类。

前缀	含义
dm_db_*	提供数据库常规统计信息比如空间和索引使用
dm_exec_*	查询统计
dm_io_*	I/O统计
dm_os_*	硬件级别的信息

三、数据库统计

1. sys.dm_db_index_usage_stats 统计索引的使用情况。包括由用户查询引起或者系统所引起的。

select * from sys.dm-db_index_usage_stats

2. sys.dm_db_index_operational_stats 统计数据库中表或者索引的每个分区的当前低级I/O，锁定和访问方法活动。

Select * from sys.dm_db_index_operation_stats(database_id, object_id, index_id, partion_id)

3. sys.dm_db_index_physical_stats 统计指定表或者试图的数据和索引的大小和碎片信息。对于索引，真最每个分区中的B树的每个级别，返回预期相应的一行。对于堆，针对每个分区的IN_ROW_DATA分配单元，返回预期对应的一行。

（1） select * from sys.dm_db_index_physical_stats(database_id, object_id, index_id, partion_id, mode)

（2）此函数将替换DBCC SHOWCONTIG语句。此函数还不接受CROSS 和OUTER相关参数

　　　　4.　sys.dm_db_missing_index_* 查询分析器根据优化查询的目的为没有索引的表或者索引建立的索引的相关信息。让没有一个好的workload用于生成SQL Server Trace时，这些函数或者
　　　　　　视图可以做一建立或者优化索引的参考。如果IndexAdvantage>5000，建议增加索引

四、与执行有关的动态管理函数和视图

1. 分为两类。一类是连接到数据库实例；另一类在数据库内部执行的查询

2. 连接到数据库的连接信息保存在两个DMV中。 Sys.dm_exec_requests和sys.dm_exec_sessions.

（1）每个连接都分配了一个system process ID(SPID),其信息就存放在sys.dm_exec_sessions中。

（2）包含四部分信息：query settings, query execution, transactions, and request executed/

3. 更详细的执行统计保存在sys.dm_exec_query_stats和sys.dm_exec_cached_plans 中。sys.dm_exec_cached_plans提供缓存的查询计划，缓存的查询文本，缓存计划占用的内存量，以及重新使用缓存计划的计数。

4. 查询计划分为两种已经编译过的查询计划或执行时生成的查询计划。比如执行存储过程是，因为存储过程的执行计划属于编译过的执行计划。因为当有1个查询在执行时，仅仅调用已经生成的查询计划，而不是在执行这个存储过程的时候生成执行计划。那么如果有10个人同时执行这个存贮过程，同样他们调用的都是已经编译过的那个执行计划。但是有10个查询计划在cache中

5. sys.dm_exec_sql_text 返回由指定sql_handle标识的sql批处理文本。

6. sys.dm_exce_query_plan返回有指定的plan handle标志的xml查询计划

五、I/O统计

1. sys.dm_io_vertual_file_stats(database_ID, file_ID) 返回数据和日志文件的I/O统计信息

2. sys.dm_id_pending_io_requests 对于SQL Server中每个挂起的I/O请求，返回与其对应的一行。

六、与SQL Server操作系统相关的攻台管理视图

1. sys.dm_os_performance_counters 为汝武器维护的每个性能计数器返回一行。

2. sys.dm_os_wait_stats 返回执行的线程所需要的所有等待的相关信息。可以使用聚合视图来诊断SQL Server以及特定查询和批处理的性能问题

Code
select * FROM
(SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS index_advantage,
migs.* FROM sys.dm_db_missing_index_group_stats migs) AS migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs_adv.group_handle =
mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage

--connection info more than sp_who
select * from sys.dm_exec_sessions
go
select * from sys.dm_exec_requests
go
-- more detailed for execution
select * from sys.dm_exec_cached_plans
go

--
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_query_plan(plan_handle)
SELECT * FROM sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_sql_text(sql_handle)
SELECT * FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle)

-- I/O

转载于:https://www.cnblogs.com/yang_sy/archive/2009/02/28/1400434.html