SQL Server性能调优：资源管理之内存管理篇（下）

peiyuan1030

　　在上篇文章SQL Server性能调优：资源管理之内存管理篇（上），介绍了SQL Server的内存管理的一些理论知识，这篇利用这些知识来解决现实中常见的一些问题。
　　
　　一、数据页缓存压力的调优
　　前篇我们说过，如果用户访问的数据页面都缓存在内存里，这样的相应速度是最快的。但是现实中，数据库的大小都是大于物理内存的，SQL Server不可能将用户需要的所有数据都缓存在内存中，当用户需要的数据不在内存中，将会发生Paging动作从硬盘中读取需要的数据，偶尔的Paging不会从整体上影响SQL Server的性能，但如果Paging动作经常发生将会严重影响SQL Server整体性能。
　　当我们进行数据页缓存的调优时，第一步先是确定是否有数据页缓存的压力，第二步是确定数据页缓存页的压力是由哪里引起的，主要可以分成外部压力和内部压力。
　　1、是否有数据页缓存压力
　　确定是否有数据页缓存压力，主要可以从下面的一些内存性能计数器和sys.sysProcesses来确认。
　　SQL Server：Buffer Manager-Lazy Writes/Sec的值经常发生。
　　SQL Server：Buffer Manager-Page Life Expectancy的经常反复变化，始终升不上去。


SQL Server：Buffer Manager-Page Reads/Sec的值经常不为0。　　从sys.sysprocesses这一系统视图的wait_type中能看到ASYNC_IO_COMPLETION值，这一值代表的意思是“等待I/O操作的完成”，这通常代表内存不足发生了硬盘读写，也可能有人会说这是硬盘的速度太慢导致的，只要换上速度快的硬盘就能解决这个问题了。确实换上速度快的硬盘能使SQL Server的响应速度提高一些，但是如果上面那三个计数器的值经常，那硬盘的问题就不是主要问题，它只是内存不够（因）导致的硬盘读写（果），根本原因还是在内存上。

　　从上面的分析中，可以确认系统中存在数据页缓存压力，现在就来分析这一压力的来源，是外部压力还是内部压力。
　　
　　2、压力的来源
　　1）外部压力

　　SQL Server：Buffer Manager-Total Server Memory的值是否变小了。如果变小了那就说明是，SQL Server的能使用的内存被系统或者外部程序给压缩了。这就是外部压力。

　　2）内部压力

SQL Server：Buffer Manager-Total Server Memory的值没什么变化，但是和SQL Server：Buffer Manager-Target Server Memory的大小基本相等。这就是SQL Server的数据页的内存需求已经等于了系统能提供的内存大小了。说明是数据库内部压力。　　
　　3、解决办法
　　1）外部压力
　　发生外部压力的大多数情形都是由于系统中还运行了其他的服务器软件，在它需要内存的时候抢掉了SQL Server的内存。因此解决方案也就是将SQL Server运行在专门的服务器上。还有一种情形会导致外部压力的发生，那就是操作系统在占用大量内存的操作（比如备份），解决方案就是将这些操作方到SQL Server运行压力小的时候（比如凌晨1、2点的时候）。

　　2）内部压力
　　a、找出读取数据页面最多的语句，对它进行调优。找出这些语句可以通过sys.dm_exec_query_status动态视图和sys.dm_exec_sql_text动态函数的关联查询。



-- 物理读取页面最多的100条语句
SELECT TOP 100
    qs.total_physical_reads,qs.execution_count,
    qs.total_physical_reads /qs.execution_count as avg_io,
    qt.text, db_name(qt.dbid) as dbname, qt.objectid
FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.total_physical_reads desc
GO

-- 逻辑读取页面最多的100条语句
SELECT TOP 100
    qs.total_logical_reads,qs.execution_count,
    qs.total_logical_reads /qs.execution_count as avg_io,
    qt.text, db_name(qt.dbid) as dbname
FROM sys.dm_exec_query_stats qs
    cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.total_logical_reads desc
GO　　找出这些语句然后经可以用语句调优的方式来进行调优了。

　　b、如果你认为语句已经没有调优的空间了，或者像快速的提高服务器性能就只能增加物理内存了。

　　
　　二、Buffer Pool中的Stolen Memory的压力调优

　　1、通过Memory Clerk的分析
　　由于Buffer Pool里的Stolen内存都是SQL Server自己申请的，所以在Memory Clerk的动态管理视图里可以查看。通过分析各Clerk的大小，基本就能判断Stolen内存压力的来源。常见的使用Stolen的内存较多的Memory Clerk。
　　a）CACHESTORE_SQLCP：缓存动态TSQL语句的执行计划的地方。这通常和程序员的代码有关，如果程序员习惯使用动态TSQL语句，这部分的内存中缓存的执行计划就会非常大。解决方法就是使用存储过程或者参数话的TSQL。

　　b)OBJECTSTORE_LOCK_MANAGER：SQL Server里锁结构使用的内存。如果SQL Server中的阻塞严重的话，这部分内存的内存使用量会很大。解决方案就是解决阻塞问题了。
　　
　　2、通过sys.sysprocesses里面的waittype字段进行分析
　　1）CMEMTHREAD（0X00B9）
　　当多个用户向同一缓存区中申请内存或者释放内存，在某一时刻只会有一个连接的操作可以成功，其他的连接必须等待。这种情况比较少，主要是发生在哪些并发度非常高的系统中，而且通常都是在编译动态的TSQL语句。解决方案就是使用存储过程或者参数化的TSQL语句，提高执行计划的重用。
　　2）RESOURCE_SEMAPHORE_QUERY_COMPLIE（0X011A）
　　当用户传送过的语句或者调用的存储过程过分复杂，SQL Server编译它所需要的内存会非常大。SQL Server为了防止过多的内存被用来做编译动作，所以设置了编译内存的上限。当有太多复杂的语句同时在编译，编译所需要的内存可能达到这个上限，这将有部分语句将处于等待内存进行编译的状态，也就该waittype。
　　解决方法有：尽量多的使用存储过程或参数化的TSQL语句，简化每次需编译的语句复杂度，分成几个存储过程，实在不行的话可以考虑定期运行DBCC FREEPROCCACHE语句来手工清除缓存中的执行计划，保证stolen中内存量。
　　
　　三、Multi-Page Memory压力调优
　　由于32位的SQL Server会在启动的时候分配好Multi-Page的大小而且比较小，默认是384MB，因此对于32位的SQL Server比较容易发生Multi-Page Memory的压力。该部分的压力主要可能由下面三种情形导致。
　　1、程序连接数据库时的Network Packet Size大小，如果设置成8KB或者更高的时候，而且连接又非常大时。对于32位的SQL Server该部分的内存使用量会很快达到上限。解决方法就是将程序中设置的Network Packet Size改成默认的4KB，或者升级到64位SQL Server，这样Multi-Page的大小就没有限制了。

　　2、程序员使用了很多复杂的TSQL语句或者存储过程，它的执行计划超过了8KB，这将占用Multi-Page的空间。由于32位的SQL Server中该部分的大小比较小，它将很快被填满，而由于Buffer Pool很大没有压力，它将不会触发Lazy Writer，Mullti-Page中的执行计划将不会被清理。而这时如果用户需要申请Multi-Page Memory就必须等待。这会体现在sys.sysprocessed的waittype字段上，该值等于SOS_RESERVEDMEMBLOCKLIST。解决方案：语句进行调整，将它的执行计划控制在8KB以内，如果不行的话可以考虑定期运行DBCC FREEPROCCACHE语句来手工清理执行计划，或者升级到64位SQL Server。
　　
　　这篇写得很乱，大家凑合看吧。。。