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

赞 (0)

相关推荐

  • 如何定位、排除和避免MySQL数据库性能问题？

    操作方法 01 做大流量访问级别的web应用开发的项目的时候,我们不得不经常要对应用中的各项功能不断的进行检测,优化以防止应用在关键时刻挂掉.下面作者就如何定位,排除以及避免MySQL数据库性能问题上 ...

  • QQ飞车关于A车的分析与性能

    本人一直在想A车的事 虽然个人经历有限 下面我说下我个人对内测时A车的看法 步骤/方法 01 第一个 A车 战车 现在可能有些朋友已经开过战车了 大部分朋友 技术一般的 总体他们认为 很难开 很慢 重 ...

  • 如何避免出现Oracle数据库无响应故障

    操作方法 01 Oracle数据库无响应故障,简单地讲就是数据库实例不能响应客户端发起的请求,客户端提交一个SQL后,就一直处于等待数据库实例返回结果的状态.更严重的现象是客户端根本不能连接到数据库, ...

  • 如何查看数据库日志

    数据库的安全涉及到各方面,数据的丢失或者篡改将会带来无法估量的损失,所以数据库的安全尤为重要,我们可以通过数据库的日志来分析数据库的安全性,然会针对分析结果采取相应的措施. 操作方法 01 用wind ...

  • Tomcat 服务器性能优化

    试想以下这个情景:你已经开发好了一个程序,这个程序的排版很不错,而且有着最前沿的功能和其他一些让你这程序增添不少色彩的元素.可惜的是,程序的性能不怎么地.你也十分清楚,若现在把这款产品退出市场,肯定会 ...

  • windows系统中如何安装Jmeter压力测试工具?

    Apache JMeter是Apache组织开发的基于Java的压力测试工具。用于对软件做压力测试，它最初被设计用于Web应用测试但后来扩展到其他测试领域。 它可以用于测试静态和动态资源例如静态文件 ...

  • sqlserver锁机制详解(sqlserver查看锁)

    简介 在SQL Server中，每一个查询都会找到最短路径实现自己的目标。如果数据库只接受一个连接一次只执行一个查询。那么查询当然是要多快好省的完成工作。但对于大多数数据库来说是需要同时处理多个查询的 ...

  • MongoDB学习笔记(一) MongoDB介绍与安装方法

    最近开始学习非关系型数据库MongoDB，却在博客园上找不到比较系统的教程，很多资料都要去查阅英文网站，效率比较低下。本人不才，借着自学的机会把心得体会都记录下来，方便感兴趣的童鞋分享讨论 一、前言 ...

  • windows系统中安装Jmeter压力测试工具

    Apache JMeter是Apache组织开发的基于Java的压力测试工具。用于对软件做压力测试，它最初被设计用于Web应用测试但后来扩展到其他测试领域。 它可以用于测试静态和动态资源例如静态文件 ...