使用DMV和DMF分析数据库性能

 　　服务器等待的原因,　　SELECT TOP 10,　　[Wait type] = wait_type,,　　[Wait time (s)] = wait_time_ms / 1000,,　　[% waiting] = CONVERT(DECIMAL(12,2), wait_time_ms * 100.0,　　/ SUM(wait_time_ms) OVER()),　　FROM sys.dm_os_wait_stats,　　WHERE wait_type NOT LIKE %SLEEP%,　　ORDER BY wait_time_ms DESC;,　　读和写,　　SELECT TOP 10,　　[Total Reads] = SUM(total_logical_reads),　　,[Execution count] = SUM(qs.execution_count),　　,DatabaseName = DB_NAME(qt.dbid),　　FROM sys.dm_exec_query_stats qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt,　　GROUP BY DB_NAME(qt.dbid),　　ORDER BY [Total Reads] DESC;,　　SELECT TOP 10,　　[Total Writes] = SUM(total_logical_writes),　　,[Execution count] = SUM(qs.execution_count),　　,DatabaseName = DB_NAME(qt.dbid),　　FROM sys.dm_exec_query_stats qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt,　　GROUP BY DB_NAME(qt.dbid),　　ORDER BY [Total Writes] DESC;,　　数据库缺失索引,　　SELECT,　　DatabaseName = DB_NAME(database_id),　　,[Number Indexes Missing] = count(*),　　FROM sys.dm_db_missing_index_details,　　GROUP BY DB_NAME(database_id),　　ORDER BY 2 DESC;,　　缺失索引列表信息,　　SELECT DatabaseName = DB_NAME(database_id),* FROM sys.dm_db_missing_index_details Order BY DB_NAME(database_id),　　高开销的缺失索引,　　SELECT TOP 10,　　[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),　　, avg_user_impact,　　, TableName = statement,　　, [EqualityUsage] = equality_columns,　　, [InequalityUsage] = inequality_columns,　　, [Include Cloumns] = included_columns,　　FROM sys.dm_db_missing_index_groups g,　　INNER JOIN sys.dm_db_missing_index_group_stats s,　　ON s.group_handle = g.index_group_handle,　　INNER JOIN sys.dm_db_missing_index_details d,　　ON d.index_handle = g.index_handle,　　ORDER BY [Total Cost] DESC;,　　确定开销最高的未使用索引,　　SELECT TOP 10 [Total Cost]=ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),avg_user_impact,TableName=statement, [EqualityUsage]=equality_columns,[InequalityUsage]=inequality_columns,[Include Cloumns] = included_columns FROM sys.dm_db_missing_index_groups g INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle ORDER BY [Total Cost] DESC;,　　确定最高开销索引所使用的脚本并显示结果。,　　-- Create required table structure only.,　　-- Note: this SQL must be the same as in the Database loop given in the following step.,　　SELECT TOP 1,　　[Maintenance cost] = (user_updates + system_updates),　　,[Retrieval usage] = (user_seeks + user_scans + user_lookups),　　,DatabaseName = DB_NAME(),　　,TableName = OBJECT_NAME(s.[object_id]),　　,IndexName = i.name,　　INTO #TempMaintenanceCost,　　FROM sys.dm_db_index_usage_stats s,　　INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id],　　AND s.index_id = i.index_id,　　WHERE s.database_id = DB_ID(),　　AND OBJECTPROPERTY(s.[object_id], IsMsShipped) = 0,　　AND (user_updates + system_updates) > 0 -- Only report on active rows.,　　AND s.[object_id] = -999 -- Dummy value to get table structure.,　　;,　　-- Loop around all the databases on the server.,　　EXEC sp_MSForEachDB USE [?];,　　-- Table already exists.,　　INSERT INTO #TempMaintenanceCost,　　SELECT TOP 10,　　[Maintenance cost] = (user_updates + system_updates),　　,[Retrieval usage] = (user_seeks + user_scans + user_lookups),　　,DatabaseName = DB_NAME(),　　,TableName = OBJECT_NAME(s.[object_id]),　　,IndexName = i.name,　　FROM sys.dm_db_index_usage_stats s,　　INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id],　　AND s.index_id = i.index_id,　　WHERE s.database_id = DB_ID(),　　AND i.name IS NOT NULL -- Ignore HEAP indexes.,　　AND OBJECTPROPERTY(s.[object_id], IsMsShipped) = 0,　　AND (user_updates + system_updates) > 0 -- Only report on active rows.,　　ORDER BY [Maintenance cost] DESC,　　;,　　,　　-- Select records.,　　SELECT TOP 10 * FROM #TempMaintenanceCost,　　ORDER BY [Maintenance cost] DESC,　　-- Tidy up.,　　DROP TABLE #TempMaintenanceCost,　　显示索引已被使用的次数，并按“使用率”排序。,　　-- Create required table structure only.,　　-- Note: this SQL must be the same as in the Database loop given in the -- following step.,　　SELECT TOP 1,　　[Usage] = (user_seeks + user_scans + user_lookups),　　,DatabaseName = DB_NAME(),　　,TableName = OBJECT_NAME(s.[object_id]),　　,IndexName = i.name,　　INTO #TempUsage,　　FROM sys.dm_db_index_usage_stats s,　　INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id],　　AND s.index_id = i.index_id,　　WHERE s.database_id = DB_ID(),　　AND OBJECTPROPERTY(s.[object_id], IsMsShipped) = 0,　　AND (user_seeks + user_scans + user_lookups) > 0,　　-- Only report on active rows.,　　AND s.[object_id] = -999 -- Dummy value to get table structure.,　　;,　　-- Loop around all the databases on the server.,　　EXEC sp_MSForEachDB USE [?];,　　-- Table already exists.,　　INSERT INTO #TempUsage,　　SELECT TOP 10,　　[Usage] = (user_seeks + user_scans + user_lookups),　　,DatabaseName = DB_NAME(),　　,TableName = OBJECT_NAME(s.[object_id]),　　,IndexName = i.name,　　FROM sys.dm_db_index_usage_stats s,　　INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id],　　AND s.index_id = i.index_id,　　WHERE s.database_id = DB_ID(),　　AND i.name IS NOT NULL -- Ignore HEAP indexes.,　　AND OBJECTPROPERTY(s.[object_id], IsMsShipped) = 0,　　AND (user_seeks + user_scans + user_lookups) > 0 -- Only report on active rows.,　　ORDER BY [Usage] DESC,　　;,　　,　　-- Select records.,　　SELECT TOP 10 * FROM #TempUsage ORDER BY [Usage] DESC,　　-- Tidy up.,　　DROP TABLE #TempUsage,　　逻辑上最零碎的索引所使用的脚本,　　-- Create required table structure only.,　　-- Note: this SQL must be the same as in the Database loop given in the -- following step.,　　SELECT TOP 1,　　DatbaseName = DB_NAME(),　　,TableName = OBJECT_NAME(s.[object_id]),　　,IndexName = i.name,　　,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),　　INTO #TempFragmentation,　　FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s,　　INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id],　　AND s.index_id = i.index_id,　　WHERE s.[object_id] = -999 -- Dummy value just to get table structure.,　　;,　　-- Loop around all the databases on the server.,　　EXEC sp_MSForEachDB USE [?];,　　-- Table already exists.,　　INSERT INTO #TempFragmentation,　　SELECT TOP 10,　　DatbaseName = DB_NAME(),　　,TableName = OBJECT_NAME(s.[object_id]),　　,IndexName = i.name,　　,[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),　　FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s,　　INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id],　　AND s.index_id = i.index_id,　　WHERE s.database_id = DB_ID(),　　AND i.name IS NOT NULL -- Ignore HEAP indexes.,　　AND OBJECTPROPERTY(s.[object_id], IsMsShipped) = 0,　　ORDER BY [Fragmentation %] DESC,　　;,　　,　　-- Select records.,　　SELECT TOP 10 * FROM #TempFragmentation ORDER BY [Fragmentation %] DESC,　　-- Tidy up.,　　DROP TABLE #TempFragmentation,　　获得IO高的查询,　　SELECT TOP 10,　　[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,　　,[Total IO] = (total_logical_reads + total_logical_writes),　　,[Execution count] = qs.execution_count,　　,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,,　　(CASE WHEN qs.statement_end_offset = -1,　　THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2,　　ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),　　,[Parent Query] = qt.text,　　,DatabaseName = DB_NAME(qt.dbid),　　FROM sys.dm_exec_query_stats qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt,　　ORDER BY [Average IO] DESC;,　　获得I/O统计,　　Select wait_type, waiting_tasks_count, wait_time_ms from sys.dm_os_wait_stats where wait_type like PAGEIOLATCH% order by wait_type,　　查询当前I/O锁,　　select DB_NAME(database_id), file_id, io_stall,io_pending_ms_ticks,scheduler_address from sys.dm_io_virtual_file_stats(NULL, NULL) t1,sys.dm_io_pending_io_requests as t2 where t1.file_handle = t2.io_handle,　　看是那5条语句导致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,　　根据句柄得到语句,　　select text from sys.dm_exec_sql_text(0x03000E00D4AB884E808214016B9A00000100000000000000),　　查询可以确定按 CPU 使用率衡量的、开销最高的查询,　　SELECT TOP 10,　　[Average CPU used] = total_worker_time / qs.execution_count,　　,[Total CPU used] = total_worker_time,　　,[Execution count] = qs.execution_count,　　,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,,　　(CASE WHEN qs.statement_end_offset = -1,　　THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2,　　ELSE qs.statement_end_offset END -,　　qs.statement_start_offset)/2),　　,[Parent Query] = qt.text,　　,DatabaseName = DB_NAME(qt.dbid),　　FROM sys.dm_exec_query_stats qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt,　　ORDER BY [Average CPU used] DESC;,　　高开销的 CLR 查询,　　SELECT TOP 10,　　[Average CLR Time] = total_clr_time / execution_count,　　,[Total CLR Time] = total_clr_time,　　,[Execution count] = qs.execution_count,　　,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,,　　(CASE WHEN qs.statement_end_offset = -1,　　THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2,　　ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),　　,[Parent Query] = qt.text,　　,DatabaseName = DB_NAME(qt.dbid),　　FROM sys.dm_exec_query_stats as qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt,　　WHERE total_clr_time <> 0,　　ORDER BY [Average CLR Time] DESC;,　　最常执行的查询,　　SELECT TOP 10,　　[Execution count] = execution_count,　　,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,,　　(CASE WHEN qs.statement_end_offset = -1,　　THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2,　　ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),　　,[Parent Query] = qt.text,　　,DatabaseName = DB_NAME(qt.dbid),　　FROM sys.dm_exec_query_stats qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt,　　ORDER BY [Execution count] DESC;,　　受阻塞影响的查询,　　SELECT TOP 10,　　[Average Time Blocked] = (total_elapsed_time - total_worker_time) / qs.execution_count,　　,[Total Time Blocked] = total_elapsed_time - total_worker_time,　　,[Execution count] = qs.execution_count,　　,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,,　　(CASE WHEN qs.statement_end_offset = -1,　　THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2,　　ELSE qs.statement_end_offset END - qs.statement_start_offset)/2),　　,[Parent Query] = qt.text,　　,DatabaseName = DB_NAME(qt.dbid),　　FROM sys.dm_exec_query_stats qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt,　　ORDER BY [Average Time Blocked] DESC;,　　最低计划重用率,　　SELECT TOP 100,　　[Plan usage] = cp.usecounts,　　,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,,　　(CASE WHEN qs.statement_end_offset = -1,　　THEN LEN(CONVERT(NVARCHAR(MAX),,　　qt.text)) * 2 ELSE qs.statement_end_offset END -,　　qs.statement_start_offset)/2),　　,[Parent Query] = qt.text,　　,DatabaseName = DB_NAME(qt.dbid),　　,cp.cacheobjtype,　　FROM sys.dm_exec_query_stats qs,　　CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt,　　INNER JOIN sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handle,　　WHERE cp.plan_handle=qs.plan_handle,　　ORDER BY [Plan usage] ASC;,　　数据库连接情况,　　SELECT session_id,connect_time,endpoint_id,auth_scheme,num_reads,num_writes,client_net_address,connection_id from sys.dm_exec_connections order by client_net_address,　　查询优化器信息,　　select * from sys.dm_exec_query_optimizer_info,　　当前执行请求,　　select * from sys.dm_exec_requests,　　当前执行session,　　select * from sys.dm_exec_sessions,　　所有的调度器并产看等待运行的任务数量,　　select,　　scheduler_id,,　　current_tasks_count,,　　runnable_tasks_count,　　from,　　sys.dm_os_schedulers,　　where,　　scheduler_id < 255,　　所有的调度器并产看等待运行的任务数量,　　select,　　*,　　from,　　sys.dm_os_schedulers,　　where,　　scheduler_id < 255,　　整个CPU使用中最占用资源的查询,　　select top 50,　　sum(qs.total_worker_time) as total_cpu_time,,　　sum(qs.execution_count) as total_execution_count,,　　count(*) as number_of_statements,,　　qs.plan_handle,　　from,　　sys.dm_exec_query_stats qs,　　group by qs.plan_handle,　　order by sum(qs.total_worker_time) desc,　　所有的调度器并产看等待运行的任务数量,　　select,　　scheduler_id,,　　current_tasks_count,,　　runnable_tasks_count,　　from,　　sys.dm_os_schedulers,　　where,　　scheduler_id < 255,　　所有的调度器并产看等待运行的任务数量,　　select,　　*,　　from,　　sys.dm_os_schedulers,　　where,　　scheduler_id < 255,　　整个CPU使用中最占用资源的查询,　　select top 50,　　sum(qs.total_worker_time) as total_cpu_time,,　　sum(qs.execution_count) as total_execution_count,,　　count(*) as number_of_statements,,　　qs.plan_handle,　　from,　　sys.dm_exec_query_stats qs,　　group by qs.plan_handle,　　order by sum(qs.total_worker_time) desc,　　得到在给定的时间段内花费在查询优化的时间,　　select * from sys.dm_exec_query_optimizer_info,　　重编译次数最多的25个存储过程,　　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,　　累计使用cpu最多的查询,　　select,　　highest_cpu_queries.plan_handle,,　　highest_cpu_queries.total_worker_time,,　　q.dbid,,　　q.objectid,,　　q.number,,　　q.encrypted,,　　q.[text],　　from,　　(select top 50,　　qs.plan_handle,,　　qs.total_worker_time,　　from,　　sys.dm_exec_query_stats qs,　　order by qs.total_worker_time desc) as highest_cpu_queries,　　cross apply sys.dm_exec_sql_text(plan_handle) as q,　　order by highest_cpu_queries.total_worker_time desc,　　清空统计项的方法,　　checkpoint 检查点,　　dbcc freeproccache 释放缓存，小心,　　dbcc dropcleanbuffers 清空缓存，小心,　　DBCC SQLPERF (sys.dm_os_wait_stats, CLEAR); 清空某一项,　　GO,　　综合分析:,　　SELECT top 50 DB_ID(DB.dbid) 数据库名,　　, OBJECT_ID(db.objectid) 对象,　　, QS.creation_time 编译计划的时间,　　, QS.last_execution_time 上次执行计划的时间,　　, QS.execution_count 执行的次数,　　, QS.total_elapsed_time / 1000 占用的总时间(秒),　　, QS.total_physical_reads 物理读取总次数,　　, QS.total_worker_time / 1000 CPU 时间总量(秒),　　, QS.total_logical_writes 逻辑写入总次数,　　, QS.total_logical_reads N逻辑读取总次数,　　, QS.total_elapsed_time / 1000 N总花费时间(秒),　　, SUBSTRING(ST.text, ( QS.statement_start_offset / 2 ) + 1,,　　( ( CASE statement_end_offset,　　WHEN -1 THEN DATALENGTH(st.text),　　ELSE QS.statement_end_offset,　　END - QS.statement_start_offset ) / 2 ) + 1) AS 执行语句,,　　[Parent Query] = st.text,　　FROM sys.dm_exec_query_stats AS QS CROSS APPLY,　　sys.dm_exec_sql_text(QS.sql_handle) AS ST INNER JOIN,　　( SELECT *,　　FROM sys.dm_exec_cached_plans cp CROSS APPLY,　　sys.dm_exec_query_plan(cp.plan_handle),　　) DB,　　ON QS.plan_handle = DB.plan_handle,　　where SUBSTRING(st.text, ( qs.statement_start_offset / 2 ) + 1,,　　( ( CASE statement_end_offset,　　WHEN -1 THEN DATALENGTH(st.text),　　ELSE qs.statement_end_offset,　　END - qs.statement_start_offset ) / 2 ) + 1) not like %fetch%,　　ORDER BY QS.total_elapsed_time / 1000 DESC,