这是本人在工作中整理的关于SQL Server性能相关资料，便于随时查阅
目录
性能监视器指标
内存问题诊断
| 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;