5分钟快速分析DB性能

在运行sqlsever 的系统上第一项应该查看的内容是硬件资源的使用率，特别是内存、存储和CPU的使用率。

在低内存的条件下，很有可能出现存储活动的增加，因为系统开始使用页面文件作为提交内存的临时存储。这一活动也会提升CPU的使用率，因为WINDOWS需要管理分页的过程。

可能注意到CPU的使用率很高或存储系统运行得比正常情况下慢。如果只看到这种现象，不要急于添加更多的CPU或更快的磁盘，因为这样可能错过根本的问题，即缺少内存。

所以我们在诊断问题或检查资源的时候，需要按照内存、存储和CPU的顺序进行。

 

1 检查内存瓶颈

Memory/AvailableMbytes

检查空闲物理内存，应保证在繁忙的系统上仍然有几百MB的空闲内存，保证在面对突发请求时都有足够的内存可用。

SQLServer:MemoryManager/TargetServer Memory(KB)

这个计数器表示SQLSERVER想要拥有的内存量。如果设置了最大服务器内存，那么这个计数器直接和最大服务器内存设置有关。如果让SQLSERVER自己管理内存，那个这个值和某个等效的动态值直接有关。

SQLServer:MemoryManager/TotalServer Memory(KB)

这个计数器表示SQLSERVER实际拥有的内存量，应比较这两个计数器，查看SQLSERVER想要的内存量和实际拥有的内存量是否有显著的不同。一般SQLSERVER一段时间后，这两个计数器的值应非常接近。如果SQLSERVER得不到所需要的内存，那么要注意可能会有外部的内存压力，或者说明最大服务器内存设置的太高了。

SQLServer:BufferManager/PageLife Expectancy --至少为300秒

这个计数器叫页面期望生存期(PLE),它的值表示SQLSERVER期望能够将未引用的页面保存在缓存中的时间，单位秒。

SQLServer:BufferManager/BufferCache Hit ratio --大于99%

这个计数器表示在缓存中命中而不需要从磁盘读取数据页面请求的百分比。

 

2.1 检查存储性能

LogicalDisk:Avg.Disksec/Transfer

windows进行一次磁盘传输消耗的时间(秒),是指示存储器性能的高级别指标，稳定在20毫秒以内，最好小于10毫秒

--如果Avg.Disksec/Transfer大于20毫秒，则要分析如下指标，看谁消耗的时间长

LogicalDisk:Avg.Disksec/Write

LogicalDisk:Avg.Disksec/Read

如果读性能和写性能有显著的差异，那么应该检查控制器缓存和RAID级别，看看是否能想办法重新均衡缓存的设置，或者推荐一个更快的RAID类型。

例如:假设发现数据文件的平均磁盘传输时间一直为75毫秒,然后检查读写性能,发现平均读时间为16ms,而平均写时间为87ms.

显然问题出现在写性能上，因此应该检查控制器缓存。控制器缓存是一项有后备电池的设备，默认设置为50%读50%写，因此应该调整缓存，使得写操作获得更好的性能提升。还可以检查RAID级别并通过DISKPART检查磁盘扇区对齐。如果RAID级别为，那么这种配置对写性能不好；如果扇区没有对齐，又会损失一些性能的提升。

所以建议，首先实现扇区对齐，然后迁移到RAID10上同时提高读写性能。

 

2.2 显示所有数据库文件I/O延迟

--如果延迟>20毫秒或毫秒，则要查看繁忙时间的PAGEIOLATCH_*

SELECT DB_NAME(database_id)AS 'Database Name',file_id,

io_stall_read_ms / num_of_reads AS'Avg Read Transfer/ms',

io_stall_write_ms / num_of_writes AS'Avg Write Transfer/ms'

FROM sys.dm_io_virtual_file_stats(-1,-1)

WHERE num_of_reads> 0

AND num_of_writes> 0

 

3 查看CPU使用状态

--主要关注用户模式和内核模式CPU的使用率及SQL SERVER服务消耗CPU的时间

Processor/%Processor Time 反映CPU的繁忙程度，>90%一般认为是很糟糕的。

Processor/%Privileged Time 表示执行内核模式操作时间的百分比，这点说明WINDOWS消耗了多少时间在进行资源管理，而不是运行应用程序。大于30%会存在问题，可能是内存不足,当内存不足时，数据将开始被分页到磁盘，CPU要在内核/特权模式处理这些工作。Processor/%Privileged Time 值会增长，所以还要去检查内存。

 Processor/%User Time 表示CPU运行应用程序(例如：sqlserver)的时间

Processor/%User Time + Processor/%Privileged Time =总的处理器时间Processor/%Processor Time

Process/%Processor Time:sqlservr当CPU使用率很高的时候，而且大部分时间消耗在用户模式应用程序上，这时要检查下是不是SQLSERVER 在使用大量的处理器时间。

 

4 检查SQLSERVER 等待

--4.1sys.dm_os_waiting_takes 列出正在等待的任务

select *from sys.dm_os_waiting_tasks

where session_id>50

 --在高cpu使用率下，查找高消耗cpu正在执行的代码及执行计划

SELECT wt.*,

st.text,

qp.query_plan

FROM sys.dm_os_waiting_taskswt

LEFT JOINsys.dm_exec_requestser

ON wt.waiting_task_address=er.task_address

CROSS APPLYsys.dm_exec_sql_text(er.sql_handle)st

CROSS APPLYsys.dm_exec_query_plan(er.plan_handle)qp

WHERE wt.wait_type= 'CXPACKET'

ORDER BYwt.session_id

 

--4.2sys.dm_os_wait_stats 自sql启动以来所有查询的所有等待时间，非常适合服务器级别的监视和性能调优

select *from sys.dm_os_wait_stats

order bywait_time_ms desc

 --重置sys.dm_os_wait_stats

dbcc sqlperf('sys.dm_os_wait_stats',clear)

 

 

 5 其他

 --计划缓存

select *from sys.dm_exec_cached_plans

--缓存中计划数目及计划缓存占用的空间(MB)

select COUNT(*) as'Number of plans',SUM(CAST(size_in_bytesas bigint))/1024/1024as 'Plan Cache Size(MB)'

from sys.dm_exec_cached_plans

--不同计划缓存对象占用空间情况(MB)

select objtypeas 'Cache Object Type',

COUNT(*)as 'Numberof plans',SUM(CAST(size_in_bytesas bigint))/1024/1024as 'Plan Cache Size(MB)',

AVG(usecounts)as 'Avg Use Count'

from sys.dm_exec_cached_plans

group byobjtype

 

--查看缺少的索引

select t1.statement,t2.user_seeks,t2.user_scans,t1.equality_columns,t1.inequality_columns

    from master.sys.dm_db_missing_index_detailsast1,

    master.sys.dm_db_missing_index_group_statsast2,

    master.sys.dm_db_missing_index_groupsast3

    where

     t1.index_handle=t3.index_handle

    and t2.group_handle=t3.index_group_handle