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

[Ok3w_NextPage]

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)

[Ok3w_NextPage]

,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

[Ok3w_NextPage]

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

[Ok3w_NextPage]

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)

[Ok3w_NextPage]

查询可以确定按 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

[Ok3w_NextPage]

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

[Ok3w_NextPage]

所有的调度器并产看等待运行的任务数量

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

[Ok3w_NextPage]

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); 清空某一项

综合分析:

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

tech首页更多tech