本文主要是介绍本人收集的SQL Server性能相关资料，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

这是本人在工作中整理的关于SQL Server性能相关资料，便于随时查阅

目录

性能监视器指标

内存问题诊断

侦测 CPU 压力

磁盘相关

内存

Memory Clerks

Buffer Pool

Plan Cache

CPU

线程

硬件信息

等待信息

SOS_SCHEDULER_YIELD等待

CXPACKET等待

资源消耗统计

I/O

读写信息

常见问题

索引

MDV

统计 

日志

WRITELOG等待

LOGBUFFER等待

小结

相关SQL

---

 

 

性能监视器指标

内存问题诊断

Memory Manager\Total Server Memory（KB）	Buffer Pool的大小
Memory Manager\Target Server Memory（KB）	表示空闲的Buffer Pool大小。 Total和Target的值应该尽可能相同， 如果Total明显小于Target， 可能意味着出现了内存压力，需要更深入地研究。
Memory Manager\Granted Workspace Memory（ KB）	当前查询正在使用的总内存大小。 Workspace：指在查询过程中，进行hash和排序操作时，临时存储数据的结果集所用的内存。如果在执行计划中看到出现了hash或者sort操作符，那么就表示会使用这部分的内存完成处理。 Memory Grants：已经分配给查询的那部分内存。 可以用过 sys. dm_exec_query_memory_grants查看。 另外， RESOURCE_ SEMAPHORE 等待状态是针对 memory grants 的，所以如果在sys. dm_ os_ wait_ stats这个DMV中看到这个等待类型存在很久，并且处于前几位，可能需要检查内存是否足够快。 如果内存授予（memory grants）太久，会导致查询超时，可以使用SQL trace或者执行计划查看是否存在hash或者sort warning这些信息。
Memory Manager\Maximum Workspace Memory（ KB）	SQL Server 标记预留给某个查询的总内存大小。
Memory Manager\Memory Grants Pending	正在队列中的内存grants数量。	>2
Memory Manager\Memory Grants Outstanding	正在使用的内存grants数量。
Buffer Manager\Buffer Cache Hit Ratio	Percentage of pages that were found in the buffer pool without having to incur a read from disk.	>90%
Buffer Manager\Page Life Expectancy	代表着一个数据存在于Buffer Pool的时间。这个值越长越好，最低时间应该设置为300s。	>1000，至少为300
Buffer Manager\Page Lookups/sec	这个计数器用于衡量实例中的请求在buffer pool里面查询的单独页数量。当这个数值很高时，可能意味着不高效的执行计划，通常需要研究该执行计划。一般数值很高是因为执行计划中产生了大量的Page Lookups和Row Lookups。	Page Lookups： Batch Request<100
Plan Cache\Cache hit Ratio	Ratio between cache hits and lookups
Memory：Available Mbytes	以字节表示的物理内存数量。此内存能立刻分配给一个进程或系统使用。它等于分配给待机(缓存的)、空闲和零分页列表内存的总和。要获得内存管理器的详细解释，请参阅 MSDN 和/或 Windows Server 2003 Resource Kit 里的系统性能和疑难解答指南章节。
Memory：Page Faults/sec	每秒钟出错页面的平均数量。由于每个错误操作中只有一个页面出错，计算单位为每秒出错页面数量，因此这也等于页面错误操作的数量。这个计数器包括硬错误(那些需要磁盘访问的)和软错误(在物理内存的其他地方找到的错误页)。许多处理器可以在有大量软错误的情况下继续操作。但是，硬错误可以导致明显的拖延。
Memory：Pages/sec	指为解决硬页错误从磁盘读取或写入磁盘的速度。这个计数器是可以显示导致系统范围延缓类型错误的主要指示器。它是 Memory\\Pages Input/sec 和 Memory\\Pages Output/sec 的总和。是用页数计算的，以便在不用做转换的情况下就可以同其他页计数如: Memory\\Page Faults/sec 做比较，这个值包括为满足错误而在文件系统缓存(通常由应用程序请求)的非缓存映射内存文件中检索的页。

侦测 CPU 压力

Access Methods\Forwarded Records/sec。	该计数器统计每秒通过正向记录指针提取的记录数，这个计数器用于衡量服务器中对Forwarded数据的访问情况，通常来说，这个值不应该超过Batch Requests/sec的10%。虽然10%不是绝对值，但它是一个警告值。
Access Methods\FreeSpace Scans/sec。	这是关于堆表的另外一个计数器。当在堆表中插入数据时，它会标识发生了什么操作。
Access Methods\Full Scans/sec。	通过这个计数器可查看Full Scans/sec的值，这个值包含聚集、非聚集索引及堆表。高值意味着查询存在性能问题，这种情况可能会引起Page Life Expectancy（用于衡量内存压力的一个主要计数器）的变动，这将加大数据在内存中的存储时间，并引起I/O问题。	Batch Requests：Full Scans<1000
Access Methods\Index Searches/sec	大部分情况下，索引查找会比索引扫描有效，这个计数器显示出SQL Server实例上发生索引查找的比率，这个值相对于Full Scans/sec来说越高越好。	Index Searches： Full Scans>1000
Access Methods\Page Splits/sec	对应于堆上的Forwarded Records，聚集索引上的就是Page Splits了。补充一下，虽然非聚集索引也有这个特性，但是由于没有聚集索引的表就是堆表，所以堆表上的非聚集索引还是使用的Forwarded Records。Page Splits是一个较消耗资源的操作，而且在拆页的时候会对原来的页加上排他锁，并且会产生碎片，所以应尽可能少用。	Batch Requests：Page Split>20
Locks(*)\Lock Wait Time（ms）	相对于前面的计数器，这类计数器更偏重于检查索引的压力情况。它可以衡量SQL Server花在索引、表、页上锁资源的时间。它没有可参考值，但是可以作为一个历史数据，然后用最近监控的数据和这个历史数据对比，比值应该越低越好。
Locks(*)\Number of Deadlocks/sec。	极端情况下，不良的索引设计和过度锁阻塞会引起死锁（Deadlocks），这种情况是绝对不能容忍的。	=0
Processor/%Privileged Time	花费在执行Windows内核命令上的处理器时间的百分比。
Processor/%User Time	花费在处理应用程序如SQL Server上的处理器时间百分比。
Process（sqlservr. exe）/%Processor Time	每个处理器上所有进程的总处理时间。
SQL Statistics\Auto-Param Attempts/sec	Number of auto-parameterization attempts.
SQL Statistics\Failed Auto-params/sec	Number of failed auto-parameterizations.
SQL Statistics\Batch Requests/sec	Number of failed auto-parameterizations.
SQL Statistics\SQL Compilations/sec	Number of SQL compilations.
SQL Statistics\SQL Re-Compilations/sec	Number of SQL re-compiles.

磁盘相关

Physical Disk\Average Disk sec/Read	Avg. Disk sec/Read 指以秒计算的在此盘上读取数据的所需平均时间。
Physical Disk\Average Disk sec/Write	Avg. Disk sec/Write 指以秒计算的在此盘上写入数据的所需平均时间。
Physical Disk\Average Disk Read/Read Queue Length	Avg. Disk Read Queue Length 指读取请求(为所选磁盘在实例间隔中列队的)的平均数。
Physical Disk\Average Disk Read/Write Queue Length	Avg. Disk Write Queue Length 指写入请求(为所选磁盘在实例间隔中列队的)的平均数。
Physical Disk\Disk Reads/sec	Disk Reads/sec 指在此盘上读取操作的速率。	＜10ms=没有性能问题 ·10~20ms=存在问题 ·20~50ms=性能很低 ·＞50ms=性能问题严重
Physical Disk\Disk Writes/sec	Disk Writes/sec 指在此盘上写入操作的速率。	＜10ms=没有性能问题 ·10~20ms=存在问题 ·20~50ms=性能很低 ·＞50ms=性能问题严重

 

内存

Memory Clerks

-- 用于内存缓存的一种机制，

SELECT [type],

       memory_node_id,

       virtual_memory_reserved_kb,

       virtual_memory_committed_kb,

       awe_allocated_kb

FROM   sys.dm_os_memory_clerks

ORDER  BY virtual_memory_reserved_kb DESC

 

Buffer Pool

SQL Server 内存中的最大消耗者。

--每个数据库缓存大小

SELECT LEFT(CASE database_id

              WHEN 32767 THEN 'ResourceDb'

              ELSE Db_name(database_id)

            END, 20)                                 AS Database_Name,

       Count(*)                                      AS Buffered_Page_Count,

       Cast(Count(*) * 8 / 1024.0 AS NUMERIC(10, 2)) AS Buffer_Pool_MB

FROM   sys.dm_os_buffer_descriptors

GROUP  BY Db_name(database_id),

          database_id

ORDER  BY Buffered_Page_Count DESC

 

--当前脏页数

SELECT Db_name(database_id) AS 'Database',

       Count(page_id)       AS 'Dirty Pages( KB)'

FROM   sys. dm_os_buffer_descriptors

WHERE  is_modified = 1

GROUP  BY Db_name(database_id)

ORDER  BY Count(page_id) DESC

 

Plan Cache

--执行计划缓存

SELECT Count(*)                                         AS 'Number of Plans',

       Sum(Cast(size_in_bytes AS BIGINT)) / 1024 / 1024 AS 'Plan Cache Size (MB)'

FROM   sys. dm_exec_cached_plans

 

--查看缓存对象的对应内存数。

SELECT objtype                                      AS 'Cached Object Type',

       Count(*)                                     AS 'Number of Plans',

       Sum(Cast(size_in_bytes AS BIGINT)) / 1048576 AS 'Plan Cache Size (MB)',

       Avg(usecounts)                               AS 'Avg Use Count'

FROM   sys. dm_exec_cached_plans

GROUP  BY objtype

 

CPU

线程

--当前系统最大线程数

SELECT max_workers_count

FROM   sys.dm_os_sys_info

 

--当前工作线程

SELECT Count(*)

FROM   sys.dm_os_workers

 

硬件信息

--NUMA配置

SELECT scheduler_id,

       cpu_id,

       parent_node_id,

       status

FROM   sys.dm_os_schedulers

 

--得知超线程和核心方面的关系。

SELECT cpu_count                     AS [Logical(逻辑) CPU Count],

       hyperthread_ratio             AS [Hyperthread(超线程) Ratio],

       cpu_count / hyperthread_ratio AS [Physical(物理) CPU Count],

       physical_memory_kb / 1024     AS [Physical Memory (MB)]

FROM   sys. dm_os_sys_info

 

等待信息

--等待类型中等待时间最长的10个类型。

SELECT TOP (10) wait_type,

                waiting_tasks_count,

                ( wait_time_ms - signal_wait_time_ms ) AS resource_wait_time,

                max_wait_time_ms,

                CASE waiting_tasks_count

                  WHEN 0 THEN 0

                  ELSE wait_time_ms / waiting_tasks_count

                END                                    AS avg_wait_time

FROM   sys.dm_os_wait_stats

WHERE  wait_type NOT LIKE '%SLEEP%'

       --去除不相关的等待类型

       AND wait_type NOT LIKE 'XE%'

       AND wait_type NOT IN

           -- 去除系统类型

           ( 'KSOURCE_WAKEUP', 'BROKER_TASK_STOP', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 'SQLTRACE_BUFFER_FLUSH',

             'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',

             'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',

             'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE', 'BROKER_RECEIVE_WAITFOR',

             'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH' )

ORDER  BY wait_time_ms DESC

 

 

SELECT wait_type,

       signal_wait_time_ms,

       wait_time_ms

FROM   sys.dm_os_wait_stats

WHERE  wait_time_ms > 0

       AND wait_type NOT IN ( 'CLR_SEMAPHORE', 'CLR_AUTO_EVENT', 'LAZYWRITER_SLEEP', 'RESOURCE_QUEUE',

                              'SLEEP_TASK', 'SLEEP_SYSTEMTASK', 'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',

                              'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT',

                              'BROKER_TO_FLUSH', 'BROKER_TASK_STOP', 'CLR_MANUAL_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE',

                              'FT_IFTS_SCHEDULER_IDLE_WAIT', 'XE_DISPATCHER_WAIT', 'XE_DISPATCHER_JOIN', 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP' )

ORDER  BY signal_wait_time_ms DESC

 

SOS_SCHEDULER_YIELD等待

发生在一个任务自愿放弃当前的资源占用，让给其他任务使用的时候，就像前面说的离开饮水机让别人去接水。 现代关系型数据库管理系统早已支持多个任务同时运行，SQL Server的运行机制之一就是减少runnable的线程等待，也就是说，SQL Server希望尽快运行runnable的线程。它以协同模式运行，在必要的时候，SQL Server会让出某个资源来给其他线程，通常来说这种情况是临时的。但是当长期、大量出现这种等待类型的时候，有可能意味着CPU存在压力，这时候可以检查这个DMV中的数据：sys.dm_os_schedulers，看看当前有多少runnable的任务在系统中运行。

--当前有多少runnable的任务在系统中运行

SELECT scheduler_id,

       current_tasks_count,

       runnable_tasks_count,

       work_queue_count,

       pending_disk_io_count

FROM   sys.dm_os_schedulers

WHERE  scheduler_id < 255

通常我们需要关注runnable_tasks_count这个列的数据，如果见到长时间存在两位数的数值（记住是长时间存在），就意味着CPU可能存在压力，无法应付当前负载。

 

CXPACKET等待

CXPACKET 这种类型有点像平时所说的木桶效应，一个木桶的容量取决于组成木桶最短的那块木条的长度。如果一个查询由多个线程组成，那么只有在最慢的那个线程完成之后，整个查询才会完成。在这种情况下，SQL Server就出现了CXPACKET等待。 CXPACKET是其中一个最常见的并行等待，在多CPU的环境下，这种等待经常会出现。并行执行最重要的目的就是使得运算更快，一个单独的查询可以使用多个线程来共同完成，每个线程会单独处理数据集的一部分。但应该注意的是，某些等待并不总是表示系统性能存在问题，CXPACKET就是其中一种。比如有一家软件公司，通常会招聘多个开发人员去完成开发工作。但是每天上下班时，总需要一个人一个人串行“打卡”，这时候CXPACKET就会出现在打卡的过程中，因为这个时候必须串行而不是并行。你可能觉得打卡过程中所用的时间是浪费的，但是从整体来说，你雇用更多的人去完成功能开发，理想情况下是可以加快项目的开发进度的，不应该因为打卡这几秒钟的浪费而无视他们在一天中对进度的贡献。 在并行过程中，如果某个线程处于落后状态，CXPACKET等待状态就会产生。在上面的例子中，如果你为了移除这种等待状态而减少雇员，比如只留下一个，那么你整体的项目进度将会严重延迟。 但是毕竟出现等待状态就是表示有一定的资源问题，所以需要针对这种情况进行分析。前面提到过，对问题的分析要全面、整体，并且要区分应用系统类型。

OLTP系统

它的特点是事务数量多，但是正常来说，事务的持续时间不会很久。

如果CXPACKET等待状态频繁出现，且持续时间很长，那就意味着性能可能有问题了。

理想情况下事务很短，这时候就没有必要通过并行运行来提高运行速度了。所以对于这类系统，有一个极端方法（如非必要不要用），即把最大并行度（Max Degree of Parallelism）设为1，强制SQL Server不去使用并行操作，从而减少不必要的资源等待。

OLAP等系统

它的事务量可能不多，但是持续时间往往会很久。

由于事务普遍较长，所以并行操作往往能提高速度和资源利用率。这时候可以让SQL Server自己控制并行，也就是把最大并行度设为0（即不限制）。

--计划缓存中存在并行查询的语句

SELECT TOP 10 p.*,

              q.*,

              qs.*,

              cp.plan_handle

FROM   sys.dm_exec_cached_plans cp

       CROSS apply sys.Dm_exec_query_plan(cp.plan_handle) p

       CROSS apply sys.Dm_exec_sql_text(cp.plan_handle) AS q

       JOIN sys.dm_exec_query_stats qs

         ON qs.plan_handle = cp.plan_handle

WHERE  cp.cacheobjtype = 'Compiled Plan'

       AND p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan"; 

        max(//p:RelOp/@Parallel)', 'float') > 0

ORDER  BY total_worker_time DESC

OPTION (MAXDOP 1)

 

资源消耗统计

--CPU最高消耗的10个语句

SELECT TOP 10 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 statement_text,

              execution_count,

              total_worker_time / 1000                                                                                   AS total_worker_time_ms,

              ( total_worker_time / 1000 ) / execution_count                                                             AS avg_worker_time_ms,

              total_logical_reads,

              total_logical_reads / execution_count                                                                      AS avg_logical_reads,

              last_logical_reads,

              total_logical_writes,

              total_logical_writes / execution_count                                                                     AS avg_logical_writes,

              last_logical_writes,

              total_elapsed_time / 1000                                                                                  AS total_elapsed_time_ms,

              ( total_elapsed_time / 1000 ) / execution_count                                                            AS avg_elapsed_time_ms,

              last_elapsed_time / 1000,

              qp.query_plan

FROM   sys.dm_exec_query_stats qs

       CROSS APPLY sys.Dm_exec_sql_text(qs.sql_handle) st

       CROSS APPLY sys.Dm_exec_query_plan(qs.plan_handle) qp

ORDER  BY total_worker_time DESC

 

--找出使用频率最高的20%的查询

SELECT TOP 20 PERCENT cp.usecounts     AS ' 使用次数',

                      cp.cacheobjtype  AS ' 缓存类型',

                      cp.objtype       AS [ 对象类型],

                      st.text          AS 'TSQL',

                      --cp.plan_handle AS ' 执行计划',

                      qp.query_plan    AS ' 执行计划',

                      cp.size_in_bytes AS ' 执行计划占用空间( Byte)'

FROM   sys.dm_exec_cached_plans cp

       CROSS APPLY sys.Dm_exec_sql_text(plan_handle) st

       CROSS APPLY sys.Dm_exec_query_plan(plan_handle) qp

ORDER  BY usecounts DESC

 

I/O

读写信息

--查看当前数据库文件中的IO情况

SELECT Db_name(Db_id())                                                                                   AS [Database Name],

       [file_id],

       num_of_reads,

       num_of_writes,

       num_of_bytes_read,

       num_of_bytes_written,

       Cast(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1))                     AS [# Reads Pct],

       Cast(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1))                    AS [# Write Pct],

       Cast(100. * num_of_bytes_read / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1))    AS [Read Bytes Pct],

       Cast(100. * num_of_bytes_written / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Written Bytes Pct]

FROM   sys. Dm_io_virtual_file_stats(Db_id(), NULL);

 

--计划缓存中存储过程的逻辑写排名，主要是写操作的压力

SELECT p. NAME                  AS [SP Name],

       qs. total_logical_writes AS [TotalLogicalWrites],

       qs. execution_count,

       qs. cached_time

FROM   sys. procedures AS p

       INNER JOIN sys. dm_exec_procedure_stats AS qs

               ON p.[object_id] = qs. [object_id]

WHERE  qs. database_id = Db_id()

       AND qs. total_logical_writes > 0

ORDER  BY qs. total_logical_writes DESC;

 

常见问题

解决PAGEIOLATCH等待

潜在问题

数据从磁盘加载到内存buffer中的时间不会很长，一旦数据进入buffer，提取数据会很快，并且查找过程不会产生任何等待。但是也有特殊情况，如果有10个任务都需要同时请求相同的数据，第一个任务会申请加载数据，其他9个任务都必须等待，直到第一个任务的数据加载完毕为止。如果这时磁盘已经超负荷，那么第一个请求会花费较长的时间。同样，如果内存不足，缓存在buffer中的数据会过早地被冲刷掉（从buffer中清空），从而引起9个任务再次等待下一轮的加载。

降低PAGEIOLATCH等待

当buffer正在加载和卸载时，都会对其中的数据加上闩锁。这意味着其他进程必须等待，直到释放闩锁且数据依旧存在于buffer中为止。快速地加载和长时间驻留能最大限度地降低等待，这类等待和其他等待类似，当遇到相关问题时，可以评估下面的方案是否可行。

提升I/O子系统的速度。

当存在内存问题或者压力出现时也会引起这种等待，检查内存相关的计数器，查看是否存在内存问题，如果存在，尝试优化或者改善内存。

将LDF/MDF/tempdb的文件分开存放，减少资源争用。

检查文件统计信息，看看高I/O读写操作出现在什么文件上。

检查系统是否有合适的索引，丢失索引和不合理的索引都会造成大面积的扫描，通过添加有效的索引可减少I/O请求，缓解I/O压力。使用覆盖索引来替代聚集索引是一个不错的选择，可以明显降低CPU、内存和I/O的压力（因为一般非聚集索引包含的列都比聚集索引少，聚集索引实际上是全表索引）。

更新统计信息，使SQL Server查询优化器能选择最优执行计划。

检查下面的性能计数器。

SQL Server：Memory Manager\Memory Grants Pending>2

SQL Server：Memory Manager\Memory Grants Outstanding

SQL Server：Buffer Manager\Buffer Hit Cache Ratio>90%

SQL Server：Buffer Manager\Page Life Expectancy>1000，至少为300

Memory：Available Mbytes Memory：Page Faults/sec ·检查磁盘相关的计数器。

Average Disk sec/Read Average Disk sec/Write Average Disk Read/Write Queue Lenght

 

索引

MDV

sys.index_columns	提供了索引内包含的列，也就是索引键。每个键一行，通过关联其他DMV 就可以获得索引定义的列情况。
sys.xml_indexes	和sys.indexes 类似，但是主要针对XML 索引。
sys.spatial_indexes	也和sys.indexes 类似，主要针对spatial 索引。
sys.column_store_dictionaries sys.column_store_segments	主要用于描述从SQL Server 2012 开始引入的列存储索引信息。
sys.dm_db_index_physical_stats	索引碎片
sys.dm_db_missing_index_details	缺失索引相关
sys.dm_db_missing_index_columns	缺失索引相关
sys.dm_db_missing_index_group_stats	缺失索引相关
sys.dm_db_missing_index_groups	缺失索引相关

 

统计 

SELECT '[' + Db_name() + '].[' + su.[name] + '].[' + o.[name]

       + ']'                  AS [statement],

       i.[name]               AS [index_name],

       ddius.[user_seeks] + ddius.[user_scans]

       + ddius.[user_lookups] AS [user_reads],

       ddius.[user_updates]   AS [user_writes],

       Sum(SP.rows)           AS [total_rows]

FROM   sys.dm_db_index_usage_stats ddius

       INNER JOIN sys.indexes i

               ON ddius.[object_id] = i.[object_id]

                  AND i.[index_id] = ddius.[index_id]

       INNER JOIN sys.partitions SP

               ON ddius.[object_id] = SP.[object_id]

                  AND SP.[index_id] = ddius.[index_id]

       INNER JOIN sys.objects o

               ON ddius.[object_id] = o.[object_id]

       INNER JOIN sys.sysusers su

               ON o.[schema_id] = su.[UID]

WHERE  ddius.[database_id] = Db_id() -- current database only

       AND Objectproperty(ddius.[object_id], 'IsUserTable') = 1

       AND ddius.[index_id] > 0

GROUP  BY su.[name],

          o.[name],

          i.[name],

          ddius.[user_seeks] + ddius.[user_scans]

          + ddius.[user_lookups],

          ddius.[user_updates]

HAVING ddius.[user_seeks] + ddius.[user_scans]

       + ddius.[user_lookups] = 0

ORDER  BY ddius.[user_updates] DESC,

          su.[name],

          o.[name],

          i.[name]

 

--查找未被使用过的索引

SELECT Object_name(i.object_id)                                      AS table_name,

       COALESCE(i.NAME, Space(0))                                    AS index_name,

       ps.partition_number,

       ps.row_count,

       Cast(( ps.reserved_page_count * 8 ) / 1024.AS DECIMAL(12, 2)) AS size_in_mb,

       COALESCE(ius.user_seeks, 0)                                   AS user_seeks,

       COALESCE(ius.user_scans, 0)                                   AS user_scans,

       COALESCE(ius.user_lookups, 0)                                 AS user_lookups,

       i.type_desc

FROM   sys.all_objects t

       INNER JOIN sys.indexes i

               ON t.object_id = i.object_id

       INNER JOIN sys.dm_db_partition_stats ps

               ON i.object_id = ps.object_id

                  AND i.index_id = ps.index_id

       LEFT OUTER JOIN sys.dm_db_index_usage_stats ius

                    ON ius.database_id = Db_id()

                       AND i.object_id = ius.object_id

                       AND i.index_id = ius.index_id

WHERE  i.type_desc NOT IN ( 'HEAP', 'CLUSTERED' )

       AND i.is_unique = 0

       AND i.is_primary_key = 0

       AND i.is_unique_constraint = 0

       AND COALESCE(ius.user_seeks, 0) <= 0

       AND COALESCE(ius.user_scans, 0) <= 0

       AND COALESCE(ius.user_lookups, 0) <= 0

ORDER  BY Object_name(i.object_id),

          i.NAME

 

--写操作还是远大于读操作的索引

SELECT Object_name(ddius.[object_id])                            AS [Table Name],

       i.NAME                                                    AS [Index Name],

       i.index_id,

       user_updates                                              AS [Total Writes],

       user_seeks + user_scans + user_lookups                    AS [Total Reads],

       user_updates - ( user_seeks + user_scans + user_lookups ) AS [Difference]

FROM   sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )

       INNER JOIN sys.indexes AS i WITH ( NOLOCK )

               ON ddius.[object_id] = i.[object_id]

                  AND i.index_id = ddius.index_id

WHERE  Objectproperty(ddius.[object_id], 'IsUserTable') = 1

       AND ddius.database_id = Db_id()

       AND user_updates > ( user_seeks + user_scans + user_lookups )

       AND i.index_id > 1

ORDER  BY [Difference] DESC,

          [Total Writes] DESC,

          [Total Reads] ASC;

 

--索引上的碎片

SELECT '[' + Db_name() + '].['

       + Object_schema_name( ddips.[object_id], Db_id())

       + '].['

       + Object_name(ddips.[object_id], Db_id())

       + ']'                                                  AS [statement],

       i.[name]                                               AS [index_name],

       ddips.[index_type_desc],

       ddips.[partition_number],

       ddips.[alloc_unit_type_desc],

       ddips.[index_depth],

       ddips.[index_level],

       Cast(ddips.[avg_fragmentation_in_percent] AS SMALLINT) AS [avg_frag_%],

       Cast(ddips.[avg_fragment_size_in_pages] AS SMALLINT)   AS [avg_frag_size_in_pages],

       ddips.[fragment_count],

       ddips.[page_count]

FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, 'limited') ddips

       INNER JOIN sys.[indexes] i

               ON ddips.[object_id] = i.[object_id]

                  AND ddips.[index_id] = i.[index_id]

WHERE  ddips.[avg_fragmentation_in_percent] > 15

       AND ddips.[page_count] > 500

ORDER  BY ddips.[avg_fragmentation_in_percent],

          Object_name(ddips.[object_id], Db_id()),

          i.[name]

 

日志

WRITELOG等待

这是日志等待中最常见的等待类型。在事务量很高的系统中，这种等待比较常见。当数据被修改时，在log cache和buffer cache中都会有记录，如果在log cache中的数据在checkpoint时写入磁盘，就会发生这种等待。但是有时候在客户端，用户会停止一个正在运行并且运行了很久的事务，这时会引起其回滚，这也就会导致这种等待的发生。

降低WRITELOG等待

把日志文件和数据文件及其他文件如TEMPDB存放到独立的磁盘中。另外就是避免类似游标等的低效操作，同时加快提交事务的频率，最后检查I/O相关的计数器。 除此之外，删除没用的非聚集索引、减少日志开销、修改索引键或使用填充因子减少页分裂（第6章介绍过）、修改程序架构、把负载分摊到多个服务器或者数据库中，这些手段都能减少出现这类等待的情况。

不要一见到这种等待就以为是I/O问题，也不要直接增加日志文件。上面已经说过，增加日志文件解决不了这类问题。 应该进行如下更加深入的分析：

查看sys.dm_io_virtual_file_stats的数据。

查看LOGBUFFER等待（下面介绍），看是否存在对日志缓冲区（log buffer）的争抢。

查看日志文件所在磁盘的磁盘等待队列。

查看事务的平均大小。

查看是否有大量的页分裂，因为这样也会导致大量的日志。

 

LOGBUFFER等待

这种等待类型相对较少出现，当一个任务正在等待存储记录到log buffer时，就会产生这种等待。这种等待类型的高值可能表示这日志所在的磁盘无法响应请求。

降低LOGBUFFER等待

不同的原因解决方案不同，通常包括合理存放各类文件、避免类似游标等编程技巧及加快事务提交的频率等。

 

小结

可以查看sys.fn_vertualfilestats函数找到I/O相关的问题，并且可检查I/O相关的计数器，比如使用SELECT * FROM fn_virtualfilestats(1,1)来查看数据库ID为1、文件号为1的统计信息。

WRITELOG和LOGBUFFER等待的处理手段非常类似，它们的确有共同点，但是不要认为它们是相同的，需要分开对待。

 

相关SQL

--检查活动事务的日志情况

SELECT DTST.[session_id],

       DES.[login_name]                               AS [Login Name],

       Db_name(DTDT.database_id)                      AS [Database],

       DTDT.[database_transaction_begin_time]         AS [Begin Time],-- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],

       CASE DTAT.transaction_type

         WHEN 1 THEN 'Read/write'

         WHEN 2 THEN 'Read-only'

         WHEN 3 THEN 'System'

         WHEN 4 THEN 'Distributed'

       END                                            AS [Transaction Type],

       CASE DTAT.transaction_state

         WHEN 0 THEN 'Not fully initialized'

         WHEN 1 THEN 'Initialized, not started'

         WHEN 2 THEN 'Active'

         WHEN 3 THEN 'Ended'

         WHEN 4 THEN 'Commit initiated'

         WHEN 5 THEN 'Prepared, awaiting resolution'

         WHEN 6 THEN 'Committed'

         WHEN 7 THEN 'Rolling back'

         WHEN 8 THEN 'Rolled back'

       END                                            AS [Transaction State],

       DTDT.[database_transaction_log_record_count]   AS [Log Records],

       DTDT.[database_transaction_log_bytes_used]     AS [Log Bytes Used],

       DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],

       DEST.[text]                                    AS [Last Transaction Text],

       DEQP.[query_plan]                              AS [Last Query Plan]

FROM   sys.dm_tran_database_transactions DTDT

       INNER JOIN sys.dm_tran_session_transactions DTST

               ON DTST.[transaction_id] = DTDT.[transaction_id]

       INNER JOIN sys.[dm_tran_active_transactions] DTAT

               ON DTST.[transaction_id] = DTAT.[transaction_id]

       INNER JOIN sys.[dm_exec_sessions] DES

               ON DES.[session_id] = DTST.[session_id]

       INNER JOIN sys.dm_exec_connections DEC

               ON DEC.[session_id] = DTST.[session_id]

       LEFT JOIN sys.dm_exec_requests DER

              ON DER.[session_id] = DTST.[session_id]

       CROSS APPLY sys.Dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST

       OUTER APPLY sys.Dm_exec_query_plan(DER.[plan_handle]) AS DEQP

ORDER  BY DTDT.[database_transaction_log_bytes_used] DESC; -- ORDER BY [Duration ms] DESC;

 

 

 

这篇关于本人收集的SQL Server性能相关资料的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！