使用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