-
sql语句大全之SQL Server 性能调优（方法论）
目录
虚拟文件信息（virtual file Statistics）
性能调优很难有一个固定的理论。调优本来就是处理一些特殊的性能问题。
通常一旦拿到一个服务器那么就先做一下性能检查。查看整个数据库是运行在什么样的状况下的。
分析收集的数据想像这种情况是否合理。
确定思路
一个数据库操作的时间都是执行时间+等待时间，在无法估计执行时间的时候看要看看等待时间。
那么等待时间分为锁等待时间和资源等待时间。
那么就先用 sys.dm_os_wait_stats动态性能视图，查看主要的状况。如果pageiolatch_sh等待很大，那么就说明，session在等待buffer pool的页。当一个session要select一些数据，但是刚刚好，这些数据并没有在buffer pool 中，那么sql server 就会分配一些缓存这些缓存是属于buffer pool 的，用来存放从磁盘读取出来的数据，在读取的时候都会给这些缓存上latch（可以看成是锁）。当存在io瓶颈的时候，那么磁盘上的数据不能立即读到buffer pool 中就会出现等待latch的情况。这个可能是io过慢，也有可能是在做一些多余的io造成的。
那么接下来查看sys.dm_io_virtual_file_stats 性能视图来确定哪个数据库造成了怎么大的延迟。并且通过physical disk \avg.disk reads/sec和physical disk\avg.disk writes/sec来确定到底数据库有多少io负载。
接下来通过 sys.dm_exec_query_stats 查看执行计划，通过查看高物理读的sql和执行计划看看有没有优化的空间。如添加索引，修改sql，优化引擎访问数据的方法。
有可能，sql 语句已经不能再优化，但是性能还是不行，往往这种sql是报表查询类的sql，会从磁盘中读取大量数据，很多数据往往在buffer pool 找不到那么就会发生大量的pageiolatch_sh等待。这时，我们就要看看是否是内存不足照成的，用perfmon 查看 page life expectancy(页寿命长度)，free list stalls/sec(等待空页的次数)和Lazy writes/sec。 page life expectancy 波动很厉害，free liststalls/sec一直大于0，Lazy writes/sec 的量也很大，那么就说明buffer pool 不够大。但是也有可能是sql 写的不严谨，select了很多没必要的数据。
在上面的troubleshooting 过程中，很容易走入一个误区，sys.dm_io_virtual_file_stats和一些性能指标，就会很容易断定说io有问题，需要额外的预算来扩展io的性能，但是扩展io是比较贵的。io性能不理想很有可能miss index或者buffer pool的压力造成的。如果单纯的添加物理设备，但是没有找到根本原因，当数据量增长后，依然会出现相同的问题。
waitevent的基本troubleshooting
wait statistics 是SQLOS跟踪得到的
SQLOS 是一个伪操作系统，是SQL Server 的一部分，有调度线程，内存管理等其他操作。
SQLOS比windows调度器更好的调度sql server 线程。SQLOS的调度器间的交互，会比强占式的系统调度又更好的并发性
当sql server 等待一个sql 执行的时候，等待的时间会被sqlos捕获，这些时间都会存放在sys.dm_os_wait_stats性能视图中。各种等待时间的长度，并且和其他的性能视图，性能计数器结合，可以很明显的看出性能问题。
对于未知的性能问题sys.dm_os_wait_stats 用来判断性能问题是很好用的，但是在服务器重启或者dbcc 命令清空 sys.dm_os_wait_stats后会很好分析，时间一长就很难分析，因为等待时间是累计的，搞不清楚哪个是你刚刚执行出来的时间。当然可以考虑先捕获一份，当sql 执行完后，再捕获一份，进行比较。
查看wait event，得到的信息只是实际性能问题的其中一个症状，为了更利用wait event 信息，你需要了解资源等待和非资源等待的区别，还有需要了解其他troubleshooting信息。
在sql server中有一部分的sql是没问题的，可以使用一下sql 语句查看说有的 session的wait event
SELECT DISTINCT
wt.wait_type
FROM sys.dm_os_waiting_tasks AS wt
JOIN sys.dm_exec_sessions AS s ON wt.session_id = s.session_id
WHERE s.is_user_process = 0
因为很大一部分是正常的，所以提供了一个sql 来过滤正常查询操作
SELECT TOP 10
wait_type ,
max_wait_time_mswait_time_ms ,
signal_wait_time_ms ,
wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms ,
100.0 * wait_time_ms / SUM(wait_time_ms) OVER ( )
AS percent_total_waits ,
100.0 * signal_wait_time_ms / SUM(signal_wait_time_ms) OVER ( )
AS percent_total_signal_waits ,
100.0 * ( wait_time_ms - signal_wait_time_ms )
/ SUM(wait_time_ms) OVER ( ) AS percent_total_resource_waits
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0 -- remove zerowait_time
AND wait_type NOT IN -- filter outadditional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT',
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT',
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
ORDER BY wait_time_ms DESC
检查wait event一般只关注前几个等待信息，查看高等待时间的等待类型。
CXPACKET：
表明并发查询的等待时间，通常不会立刻产生问题，也可能是因为别的性能问题，导致CXPACKET等待过高。
SOS_SCHEDULER_YIELD
任务在执行的时候被调度器中断，被放入可执行队列等待被运行。这个时间过长可能是cpu压力造成的。
THREADPOOL
一个任务必须绑定到一个工作任务才能执行，threadpool 就是task等待被绑定的时间。出现threadpool过高可能是，cpu不够用，也可能是大量的并发查询。
LCK_*
这中等待类型过高，说明可能session发生堵塞，可以看sys.dm_db_index_operational_stats 获得更深入的内容
PAGEIOLATCH_*,IO_COMPLETION,WRITELOG
这些往往和磁盘的io瓶颈关联，根本原因往往都是效率极差的查询操作消费了过多的内存。PAGEIOLATCH_*和数据库文件的读写延迟相关。writelog和事务日 志文件的读写相关。这些等待最好和sys.dm_io_virtual_file_stats 关联确定问题是发生在数据库，数据文件，磁盘还是整个实例。
PAGELATCH_*
在buffer pool 中非io等待latch。PAGELATCH_* 大量的等待通常是分配冲突。当tempdb中大量的对象要被删除或者创建，那么系统就会对SGAM，GAM和PFS的分配发生冲突。
LATCH_*
LATCH_*和内部cache的保护，这种等待过高会发生大量的问题。可以通过 sys.dm_os_latch_stats 查看详细内容。
ASYNC_NETWORK_IO
这个等待不完全表明网络的瓶颈。事实上多数情况下是客户端程序一行一行的处理sql server 的结果集导致。发生这种问题那么就修改客户端代码。
简单的解释了主要的等待，减少在分析wait event 的时候走的弯路。
为了确定是否已经排除问题可以用DBCC SQLPERF('sys.dm_os_wait_stats', clear)清除wait event。也可以用2个wait event 信息相减。
虚拟文件信息（virtual file Statistics）
通常，当使用wait event 分析问题的时候，都为认为很想io的性能问题。但是wait event 并不能说明io是怎么发生的，所以很有可能会误判
这就是为什么要使用sys.dm_os_latch_stats 查看的原因，可以查看累计的io统计信息，每个文件的读写信息，日志文件的读写，可以计算读写的比例，io等待的次数，等待的时间。
SELECT DB_NAME(vfs.database_id) AS database_name ,
vfs.database_id ,
vfs.FILE_ID ,
io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency ,
io_stall_write_ms / NULLIF(num_of_writes, 0)
AS avg_write_latency ,
io_stall / NULLIF(num_of_reads + num_of_writes, 0)
AS avg_total_latency ,
num_of_bytes_read / NULLIF(num_of_reads, 0)
AS avg_bytes_per_read ,
num_of_bytes_written / NULLIF(num_of_writes, 0)
AS avg_bytes_per_write ,
vfs.io_stall ,
vfs.num_of_reads ,
vfs.num_of_bytes_read ,
vfs.io_stall_read_ms ,
vfs.num_of_writes ,
vfs.num_of_bytes_written ,
vfs.io_stall_write_ms ,
size_on_disk_bytes / 1024 / 1024. AS size_on_disk_mbytes ,
physical_name
FROM sys.dm_io_virtual_file_stats(NULL,NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
AND vfs.FILE_ID = mf.FILE_ID
ORDER BY avg_total_latency DESC
查看是否读写过大，平均延时是否过高。通过这个可以知道是否是io的问题。
如果数据文件和日志文件是共享磁盘队列的，avg_total_latency 比预期的要高，那么就有可能是io的问题了
如果当前的数据库是用来归档数据到比较慢的存储中，可能会有很高的PAGEIOLATCH_*和io_stall那么我们就需要确定怎么高的等待是否属于归档的线程，因此在troubleshooting的时候要注意你的服务器的类型。
如果你的磁盘读写比例是1:10，而且又很高的 avg_total_latency 那么就考虑把磁盘队列换成 raid5，为io读提供更多的主轴。
性能指标
在最开始的troubleshooting，性能指标是非常有用的。也可以用来验证自己的判断是否正确。
PLA 是一个很好的性能日志分析工具http://pal.codeplex.com. 可惜没有中文版，当然可以去codeplex下载源代码自己修改。这个工具内嵌了性能收集集合，也就是通常要收集的一些性能指标。也内嵌了阀值模板，可以在性能指标收集完之后做分析。
sql server 对自己的性能指标有对应的性能视图 sys.dm_os_performance_counters。对于性能指标有些是累计值，因此需要做2个快照，相减计算结果。
DECLARE @CounterPrefix NVARCHAR(30)
SET @CounterPrefix = CASE WHEN @@SERVICENAME = 'MSSQLSERVER'
THEN 'SQLServer:'
ELSE 'MSSQL$' + @@SERVICENAME + ':'
END ;
-- Capture thefirst counter set
SELECT CAST(1 AS INT) AS collection_instance ,
[OBJECT_NAME] ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_init
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page lifeexpectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time(ms)'
)OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLRe-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory GrantsPending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLCompilations/sec'
)
-- Wait on Secondbetween data collection
WAITFOR DELAY '00:00:01'
-- Capture thesecond counter set
SELECT CAST(2 AS INT) AS collection_instance ,
OBJECT_NAME ,
counter_name ,
instance_name ,
cntr_value ,
cntr_type ,
CURRENT_TIMESTAMP AS collection_time
INTO #perf_counters_second
FROM sys.dm_os_performance_counters
WHERE ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Full Scans/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Access Methods'
AND counter_name = 'Index Searches/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Lazy Writes/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Buffer Manager'
AND counter_name = 'Page lifeexpectancy'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'Processes Blocked'
)
OR ( OBJECT_NAME = @CounterPrefix + 'General Statistics'
AND counter_name = 'User Connections'
)OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Waits/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Locks'
AND counter_name = 'Lock Wait Time(ms)'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLRe-Compilations/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'
AND counter_name = 'Memory GrantsPending'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'Batch Requests/sec'
)
OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'
AND counter_name = 'SQLCompilations/sec'
)
-- Calculate thecumulative counter values
SELECT i.OBJECT_NAME ,
i.counter_name ,
i.instance_name ,
CASE WHEN i.cntr_type = 272696576
THEN s.cntr_value - i.cntr_value
WHEN i.cntr_type = 65792 THEN s.cntr_value
END AS cntr_value
FROM #perf_counters_init AS i
JOIN #perf_counters_second AS s
ON i.collection_instance + 1 = s.collection_instance
AND i.OBJECT_NAME = s.OBJECT_NAME
AND i.counter_name = s.counter_name
AND i.instance_name = s.instance_name
ORDER BY OBJECT_NAME
-- Cleanup tables
DROP TABLE #perf_counters_init
DROP TABLE #perf_counters_second
主要收集一下性能指标：
• SQLServer:AccessMethods\Full Scans/sec
• SQLServer:AccessMethods\Index Searches/sec
• SQLServer:BufferManager\Lazy Writes/sec
• SQLServer:BufferManager\Page life expectancy
• SQLServer:BufferManager\Free list stalls/sec
• SQLServer:GeneralStatistics\Processes Blocked
• SQLServer:GeneralStatistics\User Connections
• SQLServer:Locks\LockWaits/sec
• SQLServer:Locks\LockWait Time (ms)
• SQLServer:MemoryManager\Memory Grants Pending
• SQLServer:SQLStatistics\Batch Requests/sec
• SQLServer:SQLStatistics\SQL Compilations/sec
• SQLServer:SQLStatistics\SQL Re-Compilations/sec
这里又2个 Access Methods 性能指标，说明了访问数据库不同的方式，full scans/sec 表示了发生在数据库中索引和表扫描的次数。
如果io出现瓶颈，并且伴随着大量的扫描出现，那么很有可能就是miss index 或者sql 代码不理想照成的。那么多少次数到多少时可以认为有问题呢？在通常状况下 index searches/sec 比 full scans/sec高800-1000，如果 full sacans/sec过高，那么很有可能是miss index 和多余的io操作引起的。
Buffer Manager 和 memory manager 通常用来检测是否存在内存压力，lazy writes/sec，page life expectancy ,free list stalls/sec 用来佐证是否处于内存压力。
很多网上的文章和论坛都说，如果Page Life expectancy 低于300秒的时候，存在内存压力。但是这只是对于以前只有4g内存的服务器的，现在的服务器一般都是32g以上内存5分钟的阀值已经不能在说明问题了。300秒虽然已经不再适用，但是我们可以用300来作为基值来计算当前的PLE的阀值(32/4)*300 = 2400那么如果是32g的服务器设置为2400可能会比较合适。
如果PEL一直低于阀值，并且 lazy writes/sec一直很高，那么有可能是buffer pool压力造成的。如果这个时候full scans/sec值也很高，那么请先检查是不是miss index 或者读取了多余的数据。
generalstatistics\processes blocked,locks\lock waits/sec和locks\lock wait time(ms)如果这3个值都是非0那么数据库会发生堵塞。
SQL Statistics 计数器说明了sql 的编译或者重编译的速度，sql compilations/sec和 batch requests/sec 成正比，那么很有可能大量sql 访问都是 ad hoc方式无法通过执行计划缓冲优化它们，如果 SQLRe-compilations/sec 和 batch requests/sec 成正比，那么应用程序中可能又强制重新编译的选项。
memorymanager\momory grants pending 表示等待授权内存的等待，如果这个值很高那么增加内存可能会有效果。但是也有可能是大的排序，hash操作也可能造成，可以使用调整索引或者查询来减小这种状况。
执行计划缓冲的使用
执行计划缓冲是sql server 的内部组件，可以使用 sys.dm_exec_query_stats 查询，下面有个sql查询物理读前十的计划
SELECT TOP 10
execution_count ,
statement_start_offset AS stmt_start_offset ,
sql_handle ,
plan_handle ,
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_physical_reads ,
t.text
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
ORDER BY avg_physical_reads DESC
在执行计划里面的这些值可以看出哪些查询物理io操作很频繁，也可以和wait event 和虚拟文件结合分析有问题的io操作。
我们也可以使用sys.dm_exec_query_plan()查看存在内存里面的执行计划。
这里又2本书深入的讲述了查询执行计划：《SQL Server 2008 Query performance tuningdistilled》，《Inside Microsoft SQL Server 2008:T-SQLQuerying》。
sys.dm_exec_query_stats还用来查询 cpu时间，最长执行时间，或者最频繁的sql
在sql server 2008中加入了2个额外的列，query_hash,query_plan_hash用来聚合相似的sql的。对于ad hoc 过大的服务器可以用来分析相似的sql，不同的编译的总数。
总结
上面各个部分都讲了一个思维，一个思路。要想性能调优调的好，那么就先系统体系结构，你要清楚如前面说的miss index 一旦发生，那么不知会影响io，还会影响内存和cpu。接下来要会分析，从一开始的简单的性能统计信息，往下分析，用其他统计信息排除问题，得到性能问题的真正原因。
文章来源于：Troubleshooting SQL Server: A Guide for theAccidental DBA 如果看不懂的或者想更深入了解的，可以看原文。
