二、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。
这篇写得很乱,大家凑合看吧......
相关链接: