SQL Server 性能调优（方法论）-白红宇

SQL Server 性能调优（方法论）

阅读量：5785 次

发布时间：2019-06-18

本文共 12162 字，大约阅读时间需要 40 分钟。

性能调优很难有一个固定的理论。调优本来就是处理一些特殊的性能问题。

通常一旦拿到一个服务器那么就先做一下性能检查。查看整个数据库是运行在什么样的状况下的。

分析收集的数据想像这种情况是否合理。

确定思路

一个数据库操作的时间都是执行时间+等待时间，在无法估计执行时间的时候看要看看等待时间。

那么等待时间分为锁等待时间和资源等待时间。

那么就先用　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 list stalls/sec 一直大于0，Lazy writes/sec 的量也很大，那么就说明buffer pool 不够大。但是也有可能是sql 写的不严谨，select了很多没必要的数据。

在上面的troubleshooting 过程中，很容易走入一个误区，sys.dm_io_virtual_file_stats 和一些性能指标，就会很容易断定说io有问题，需要额外的预算来扩展io的性能，但是扩展io是比较贵的。io性能不理想很有可能miss index或者buffer pool的压力造成的。如果单纯的添加物理设备，但是没有找到根本原因，当数据量增长后，依然会出现相同的问题。

wait event的基本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_ms wait_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 zero wait_time

AND wait_type NOT IN -- filter out additional 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 the first 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 life expectancy'

)

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 = 'SQL Re-Compilations/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'

AND counter_name = 'Memory Grants Pending'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'Batch Requests/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'SQL Compilations/sec'

)

-- Wait on Second between data collection

WAITFOR DELAY '00:00:01'

-- Capture the second 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 life expectancy'

)

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 = 'SQL Re-Compilations/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'Memory Manager'

AND counter_name = 'Memory Grants Pending'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'Batch Requests/sec'

)

OR ( OBJECT_NAME = @CounterPrefix + 'SQL Statistics'

AND counter_name = 'SQL Compilations/sec'

)

-- Calculate the cumulative 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:Access Methods\Full Scans/sec

• SQLServer:Access Methods\Index Searches/sec

• SQLServer:Buffer Manager\Lazy Writes/sec

• SQLServer:Buffer Manager\Page life expectancy

• SQLServer:Buffer Manager\Free list stalls/sec

• SQLServer:General Statistics\Processes Blocked

• SQLServer:General Statistics\User Connections

• SQLServer:Locks\Lock Waits/sec

• SQLServer:Locks\Lock Wait Time (ms)

• SQLServer:Memory Manager\Memory Grants Pending

• SQLServer:SQL Statistics\Batch Requests/sec

• SQLServer:SQL Statistics\SQL Compilations/sec

• SQLServer:SQL Statistics\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 或者读取了多余的数据。

general statistics\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方式无法通过执行计划缓冲优化它们，如果 SQL Re-compilations/sec 和 batch requests/sec 成正比，那么应用程序中可能又强制重新编译的选项。