Server自己管理内存,这部分内存使用会相对较稳

作者: www.9159.com  发布:2019-11-22

开篇小感悟

  在实际的场景中会遇到各种奇怪的问题,为什么会感觉到奇怪,因为没有理论支撑的东西才感觉到奇怪,SQL Server自己管理内存,我们可以干预的方式也很少,所以日常很难遇到处理内存问题的案例。当遇到了原有的知识储备已经变得模糊,这是已经记不住第几遍阅读《SQL 2012实施与管理实战指南》内存管理章节,也分享给群友。

1、内存使用分类

数据页缓存是SQL Server的内存使用主要的方面,也是占用量最大的部分。在一个稳定的DB Server上,这部分内存使用会相对较稳定。

一些内存使用错误理解

    本文来澄清一些用户经常对SQL Server内存使用的误解。对这些知识的理解可以帮助数据库管理员理解SQL Server的行为 

    1. Windows上还有很多物理内存没有被使用,就意味着SQL Server不缺内存

    这个观点是非常错误的,因为:

    (1)SQL Server很可能设置了Max Server Memory,约束了SQL Server继续申请内存的能力。(注:实际场景中就遇到过客户128GB内存的机器SQL Server 的Max Server Memory 被设置成2048MB 导致严重的问题

    (2)在32位的机器上 ,由于虚拟地址空间的限制,SQL Server可能已经无法继续申请内存。

    例如,在一台4 GB或更大的32位服务器上,SQL Server最多只能使用2 GB物理内存。一般Windows会使用500 MB左右的物理内存,这台机器可能就有1 GB多的空闲物理内存。这部分内存是SQL Server不开AWE就用不到的。 所以,服务器上有很多空闲物理内存,只能说明Windows不缺内存,不能说明SQL Server自己不缺内存。(这也是为什么建议更换老一代服务器,使用64位的操作系统和SQL Server的原因

 

    2.  SQL Server进程的Private Bytes(或Working Set)在不停地向上涨,说明SQL Server有内存泄漏的问题

    恰恰相反,对于申请内存,SQL Server有严格的限制。在32位的服务器上,不管是Buffer Pool还是MemToLeave,都有一个使用最大上限。当Windows感觉到有内存压力的时候,SQL Server又会主动地释放内存所以作为一个进程,SQL Serve发生内存泄漏的机会是非常小的。

    之所以会有这样的疑惑,是因为SQL Server启动的时候,仅会Commit它启动所需要的那部分内存。随着用户的使用,SQL Server会不断地申请内存,直到Windows感觉到内存压力,或者SQL到了自己的上限为止,在这之前,SQL Server的内存使用量的确会不断地增加。对SQL Server来讲,缓存越多的数据,它的性能会越好这种增长是正常的,不用焦虑如果不希望SQL Server使用那么多内存,只需设置Max Server Memory这个上限即可。

  (注:实际情况中很多人提到过,SQL Server竟然吃掉了所有服务器的内存,是不是内存泄露了?或问内存是不是这就是我系统的瓶颈呀?) 

 

    3.  Max Server Memory的值,就是SQL Server内存使用量的最大值。超过这个值就不正常

  Max Server Memory这个值应该是Buffer Pool的上限(此点针对SQL Server 2005/2008而言,对于SQL Server 2012内存管理发生了非常大的变化),而不是SQL Server所有内存使用的上限。由于SQL Server 内存的使用包括Buffer Pool和MemToLeave,所以SQL Server实际内存使用量一定会比Max Server Memory要大但是在正常情况下SQL Server MemToLeave的使用会远小于Buffer Pool的使用,控制好Buffer Pool,就基木控制住了SQL Server的整体内存使用量

   (注:建议无论内存是否存在压力都要合理的设置最大内存,PS:我也曾经被使用的内存超过设置的Max Server Memory吓了一跳

  

    4. SQL Server的内存使用总量,就是性能监视器里面的SQL Server:Memory Manager一Total Server Memory的值

    性能监视器里面与SQL Server相关的counter,都是SQL Server自己负责收集的。从SQL Server 2005以后,SQL整合了所有的内存申请,让它们使用同一的接口。所以SQL Server对自己申请的内存数量,是了如指掌的,但问题是,在SQL Server进程里运行的代码不都是SQL Server自己的代码。对第三方的代码,SQL Server是不知道它们申请了多少内存的。

    SQLServer:Memory Manager - Total Server Memory的值,是SQL Server自己的代码申请的内存空间大小。真正SQL Server进程申请的空间值,会比这个值大一些。(具体大多少和MemToLeave的大小有关系)

    如果SQL Server没有开启AWE,SQL Server进程申请的逻辑内存数和物理内存数可以由Process下的Private Bytes和Working Set看出。这两个值会包含所有的内存支出,包括SQL自己的代码和第三方的代码。

    如果SQL Server开启了AWE,问题就比较尴尬了。因为Windows没有办法正确判断出一个使用了AWE 内存的进程,究竟总共用了多少内存。我们只能借助SQLServer:Memory Manager一Total Server Memory来判断SQL Server的Buffer Pool使用量。至于SQL Server自己申请的内存总数(Buffer Pool + MemToLeave ),可以通过查询和内存相关的DMV计算出来,但第三方的代码申请的内存,就很难做精确计算了

   

  

    5.当系统有内存压力的时候,SQL Server总是会自动释放内存

    默认情况下,SQL Server的确会在系统有内存压力的时候自动释放内存但是有个例外:SQL Server启动时会试图做“Lock Page In Memory”的动作。如果启动账号有这个权限,动作就会成功。那么当同一台服务器上的其他应用程序需要内存的时候,SQL Server很可能不会释放内存。所以在这种情况下,建议SQL Server设置Max Server Memory上限。

  (注:Lock Page In Memory很多资料上写到SQL的内存不会被释放了,但实际情况中,当操作系统感觉到压力一样会把SQL的内存释放掉,也是错误理解6的由来) 

 

    6.  SQL Server有办法将自己的内存绑定在物理内存里

    SQL Server的确想通过Lock Page In Memory的方法达到这个目的。但是,作为一个用户态为主的应用程序,它还是会受限于核心态。如果核心态里发出内存要求,SQL Server就会被迫把自己的内存释放出来。

 

    7.增加MemToLeave的大小可以提高SQL Server的性能

    在32位的SQL Server上,默认MemToLeave是256 MB+0.5 MB x ( Max Thread数目)。如果MemToLeave 用完了,SQL Server的一些重要功能就不能进行,甚至新的连接都建立不起来所以一些对MemToLeave需求比较大的SQL Server,例如,一些经常运行Linked Server分布式查询的SQL Server,或者是一些运行CLR,Extended Stored Procedur的SQL Server,可能不得不再加一些MemToLeave空间。这可以使用SQL Server的

一个启动参数一g完成。例如,如果想把MemToLeave设成512 MB+0.5 MB x ( Max Thread数目),可以加启动参数一g512。

    但是需要想清楚的是,SQL Server的虚拟地址空间只有2 GB,给MemToLeave越多,Buffer Pool能拿到的就越少。Data Cache的内存还可以从AWE扩展的内存补,Buffer Pool里的Stolen内存就没办法了。所以其实这是拆东墙补西墙,关键要看哪一块内存对SQL Server的性能和稳定运行最重要。不必要多给,这会浪费资源,影响Buffer Pool里面的性能。只有确定了SQL Server的MemToLeave的确不够,才应该去增大它

  

 8.增加物理内存一定会提高SQL Server的性能

    既然SQL Server这么喜欢内存,那管理员多给服务器配备一些内存,是不是就一定能够提高性能呢?很多时候这个想法是对的,但是并不是总是正确。这是因为增加的内存SQL Server不一定用得到。

    (1)首先要检查是哪一部分的内存有瓶颈,是Database Cache,还是Stolen,是Buffer Pool,还是MemToLeave。

    (2)要检查缺的那部分内存是不是已经到了理论上的上限,新加内存SQL Server用不用得上例如,在32位+AWE的服务器上,Buffer Pool里的Stolen Memory最多也不过1.6 GB。如果是这一部分内存不够,再加内存也没有用。只有将系统升级到64位,才能突破这些限制。

    (3)加人的内存,一般大部分都会被Buffer Pool使用,这样SQL Server就会缓存更多的数据页面和执行计划,大多数时候这会对性能有帮助。如果Database Pages没有压力,SQL Server会缓存太多的动态T-SQL执行计划,对性能没什么好处,反而会增加SQL Server的维护成本。只有在SQL Server的Database Page缺内存的时候,增加内存才会对性能有明显帮助。如果数据库比较小,常用数据页面已经缓存在了内存里,增加内存对性能不会有太大帮助。

  (注:内存这个东西对于一些初级DBA分析起来还是有些难度,而很多情况下只要看到内存压力就要添加内存这样也是不对的,很多时候内存的消耗很自己程序的语句优化程度也有着非常大的关系) 

 

 9. Stolen的内存真的是偷来的吗

   很多在描述Stolen的内存块的时候都说这块内存是不需要保留(Reserve)而直接提交(Commit )使用的,所以这块内存叫作Stolen。这么说错了吗?其实,从Windows的层面,对于任何内存的使用,都必须遵循先Reserve再Commit的过程。对于一块内存,如果不Reserve而直接使用会导致访问违例(Access Violation)因为SQL Server内部的内存管理机制,所有要使用的内存,Buffer Pool都已经帮我们保留(Reserve)好了如果SQL所要做的,是用Buffer Pool已经保留(Reserve)好了的那部分地址空间去直接提交(Commit ),而且,这一部分内存又不是用来存放Buffer的,就被称为Stolen

--------------博客地址---------------------------------------------------------------------------------------

博客园原文地址: 

 

 欢迎转载,请保留出处。


 

  总结 : 本文介绍了一些常见的内存方面的误区,都是文字描述,可能很多人无法真正的理解,但当遇到过这样的场景或奇怪的问题发生时,回头看看也会加深一下感受的!

  PS:我也是当遇到真正场景时才能明白,理论知识也是需要不断熟悉、不断理解的。

 

 ----------------------------------------------------------------------------------------------------

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

按用途分类
1)Database cache(数据页面)。SQL Server中的页面都是以8KB为一个页面存储的。当SQL Server需要用到某个页面时,它会将该页面读到内存中,使用完后会缓存在内存中。在内存没有压力的情况下,SQL Server不会将页面从内存中删除。如果SQL Server感觉到内存的压力时,会将最长时间没有使用的页面从内存中删除来空出内存。
2)各类Consumer(功能组件)
Connection的连接信息
General:一组大杂烩。语句的编译、范式化、每个锁数据结构、事务上下文、表格和索引的元数据等
Query Plan:语句和存储过程的执行计划。和Database cache类似,SQL Server也会将执行计划缓存以供将来使用,减少编译时间。
Optimizer:生成执行计划的过程中消耗的内存。
Utilities:像BCP、Log Manager、Backup等比较特殊的操作消耗的内存。
3)线程内存:存放进程内每个线程的数据结构和相关信息消耗的内存,每个线程需0.5MB的内存。
4)第三方代码消耗的内存:SQL Server的进程里,会运行一些非SQL Server自身的代码。例如:用户定义的CLR或Extended Stored PRocedure代码。
按申请方式分类
1)预先Reserve一块大的内存,然后在使用的时候一块一块的Commit。Database Page是按这种方式申请的。
2)直接用Commit方式申请的内存,成为Stolen方式。除了Database Page之外其他内存基本都是按这种方式申请的。
按申请内存的大小分类
1)申请小于等于8KB为一个单位的内存,这些内存称为Buffer Pool

SQL Server会把经常使用到的数据缓存在内存里(就是数据页缓存),用以提高数据访问速度。因为磁盘访问速度远远低于内存,所以减少磁盘访问量同样是数据库优化的重要方面。

2)申请大于8KB为一个单位的内存,这些内存称为Multi-Page(或MemToLeave)

当数据页缓存区出现内存不足,则会出现查询慢,磁盘忙等等问题。

SQL Server对于Database cache都是采用先Reserved后Commit的方式申请的,而数据页都是以8KB为单位进行申请的。
对于Consumer中的内存申请,一般都是按Stolen方式申请的,且大多数的执行计划的大小都是小于8KB的,少数特别复杂的存储过程的执行计划会超过8KB,默认的连接的数据包是4KB,除非客户端特别设置了超过8KB(不建议)
第三方代码的内存申请一般是按Stolen方式申请的,个别比如CLR中可能会用Reserved/Commit的方式申请。
线程的内存每个都以0.5MB的方式申请,自然是放在MemToLeave中。
之所以花了这么大篇幅来讲SQL Server的内存分类,是因为SQL Server尤其是32位的SQL Server对不同种类的内存的申请大小是不一样的,对Commit、Stolen和MemTOLeave等类型的内存是有限制的。因此会出现系统中还有空闲内存,但是SQL Server不会申请使用的现象。
2、SQL Server内存使用情况的分析
一般来说有两种方式,第一种就是用来分析系统内存情况时使用的用性能计数器来分析,第二种是使用动态管理视图(DMV,只适用于SQL Server2005和2008)
1)SQL Server性能计数器
SQLServer:Memory Manager:Total Server Memory(KB):SQL Server缓冲区提交的内存。不是SQL Server总的使用内存,只是Buffer Pool中的大小。
SQLServer:Memory Manager:Target Server Memory(KB):服务器可供SQL Server使用的内存量。一般是由SQL Server能访问到的内存量和SQL Server的sp_Configure配置中的Max Server Memory值中的较小值算得。
SQLServer:Memory Manger:Memory Grants Pending:等待内存授权的进程总数。如果该值不为0,说明当前有用户的内存申请由于内存压力被延迟,这意味着比较严重的内存瓶颈。
SQLServer:Buffer Manager:Buffer Cache Hit Ratio:数据从缓冲区中找到而不需要从硬盘中去取的百分比。SQL Server在运行一段时间后,该比率的变化应该很小,而且都应该在98%以上,如果在95%以下,说明有内存不足的问题。
SQLServer:Buffer Manager:Lazy Writes/Sec:每秒钟被惰性编辑器(Lazy writer)写入的缓冲数。当SQL Server感觉到内存压力的时候,会将最久没有使用的数据页面和执行计划从缓冲池中清理掉,做这个动作的就是Lazy Writer。
SQLServer:Buffer Manager:Page Life Expectancy:页面不被引用后,在缓冲池中停留的秒数。在内存没有压力的情况下,页面会一直待在缓冲池中,Page Life Expectancy会维持在一个比较高的值,如果有内存压力时,Page Life Expectancy会下降。所以如果Page Life Expectancy不能维持在一个值上,就代表SQLServer有内存瓶颈。
SQLServer:Buffer Manager:Database Pages :就是Database Cache的大小。
SQLServer:Buffer Manager:Free Pages:SQL Server中空闲可用的大小。
SQLServer:Buffer Manager:Stolen Pages:Buffer Pool中Stolen的大小。
SQLServer:Buffer Manager:Total Pages: Buffer Pool的总大小(等于Database Pages+Free Pages+Stolen Pages)。该值乘以8KB,应该等于Memory Manager:Total Server Memory的值。
从上面这些计数器中我们就能了解SQL Server的内存使用情况,结合前面说的系统层的计数器大概能看出是否存在内存瓶颈。
2)内存动态管理视图
在SQL Server 2005以后,SQL Server的内存管理是使用Memory Clerk的方式统一管理。所有的SQL Server的内存的申请或释放,都需要通过它们的Clerk,SQL Server也通过这些Clerk的协调来满足不同需求。通过查询这些DMV,可以得到比用性能计数器更加详细的内存使用情况。
我们可以通过下面的查询语句来检测SQL Server的Clerk的内存使用情况。
使用sys.dm_os_memory_clerks查看内存使用情况
SELECT type, --Clerk的类型
sum(virtual_memory_reserved_kb) as vm_Reserved_kb, -- 保留的内存
sum(virtual_memory_committed_kb) as vm_Committed_kb, --提交的内存
sum(awe_allocated_kb) as awe_Allocated_kb, -- 开启AWE后使用的内存
sum(shared_memory_reserved_kb) as sm_Reserved_kb, -- 共享的保留内存
sum(shared_memory_committed_kb) as sm_Committed_kb, -- 共享的提交内存
sum(single_pages_kb) as SinlgePage_kb, --Buffer Pool中的Stolen的内存
sum(multi_pages_kb) as Multipage_kb -- MemToLeave的内存
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY type
从上面的查询语句,我们可以算出前面提到的内存大小
Reserved/Commit = sum(virtual_memory_reserved_kb) / sum(virtual_memory_committed_kb)
Stolen = sum(single_pages_kb) + sum(multi_pages_kb)
Buffer Pool = sum(virtual_memory_committed_kb) + sum(single_pages_kb)
MemToLeave = sum(multi_pages_kb)
通过上面的介绍我们可以知道SQL Server总体和各部分内存的使用情况,如果我想知道数据页的缓存中到底缓存了哪些数据,这些数据是属于哪个数据库的哪个表中的呢?执行计划又是缓存了哪些语句的执行计划呢?这也可以通过DMV查看的到。
查看内存中的数据页面缓存的是哪个数据库的哪个表格的数据
declare @name nvarchar(100)
declare @cmd nvarchar(1000)
declare dbnames cursor for
select name from master.dbo.sysdatabases
open dbnames
fetch next from dbnames into @name
while @@fetch_status = 0
begin
set @cmd = 'select b.database_id, db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) from '
--这里的object_id代表是SQL Server中的对象号,index_id代表是索引号,buffer_count代表的是页面数

分析方法:主要是用到性能计数器。

  • @name + '.sys.allocation_units a, '
  • @name + '.sys.dm_os_buffer_descriptors b, ' + @name + '.sys.partitions p
    where a.allocation_unit_id = b.allocation_unit_id
    and a.container_id = p.hobt_id
    and b.database_id = db_id(''' + @name + ''')
    group by b.database_id,p.object_id, p.index_id
    order by b.database_id, buffer_count desc'
    exec (@cmd)
    fetch next from dbnames into @name
    end
    close dbnames
    deallocate dbnames
    go
    -- 根据上面取出来的@object_id找出是哪个数据库的哪个表
    SELECT s.name AS table_schema, o.name as table_name --使用的就是table_schema.table_name表
    FROM sys.sysobjects AS o INNER JOIN
    sys.schemas AS s ON o.uid = s.schema_id
    WHERE (o.id = @object_id)
    -- 根据上面取出来的@object_id和@index_id找出索引的名称
    SELECT id, indid, name as index_name -- index_name就是索引的名称
    FROM sys.sysindexes

    WHERE (id = @object_id) AND (indid = @index_id)

    根据上面取出来的表名table_schema.table_name和索引的名称index_name,还可以找出该索引是建立在哪些字段上的
    EXEC sp_helpindex 'table_schema.table_name'
    查看内存中缓存的执行计划,以及执行计划对应的语句:
    -- 输出可能较大,请小心使用
    SELECT usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text
    FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    ORDER BY objtype DESC

   查看如下性能计数器:

         1. SQL SERVER:Buffer Manager-Lazy Writes/sec:内存不足则会频繁调用Lazy Writer把数数据写入磁盘,此值会经常不为0.

         2. SQL SERVER:Buffer Manager-Page life expectancy:内存不足时,此计数器表现为下降趋势或者一直停留在较低值。

         3. SQL SERVER:Buffer Manager-Page reads/sec:内存不足时,则查询那些经常使用但又没有缓存在内存里的数据时,就不需要读取磁盘,这此值表现为持续上升或者停留在较高值。

         4. SQL SERVER:Buffer Manager-Stolen pages:Stolen pages通常用于缓存执行计划,以备重用。内存不足时,SQL Server本身机制会优先清除执行计划缓存,则此值表现为下降或者较低水平。

    查询当前用户任务等待:

select * from sys.sysprocesses

  如果内存不足则,会看到较多的ASYNC_IO_COMPLETION等待类型。这是因为内存不足时:a.内存和磁盘间会频繁进行交互,磁盘负载增加 b.需要读取磁盘上的数据完成查询,磁盘负载增加。

  也就是说这时候磁盘也出现了性能瓶颈,但是这只是“表面”的,我们要结合多个性能指标来认清根本原因是“内存不足”。

确定压力来源及解决办法:

   通过前的分析,确定了数据页缓存相关的内存瓶颈。就要分析为什么会这样及解决办法。主要分为如下5个方面:

   1. 外部压力

       如果OS层面或者其它应用服务需要更多的内存,windows会压缩Database Pages的内存量。这时内存压力来自外部。可以查看如下性能计数器确定是否是外部压力:

        1. SQL Server:Memory Manager-Total Server Memory:此计数器值会下降。

        2. Memory:Available Mbytes:此值会下降到较低水平。

        3. 在没有使用AWE或者Lock page in memory前提下,查看Process:Private Bytes-SqlServer和Process:Working Set-SqlServer,两者值会有显著下降。

      解决方法:如果非DB专用服务器,则要权衡各个应用服务之间重要性来分配内存或者加大内存。尽量让服务器只运行SQL Server,成为DB专用服务器。

   2. SQL Server自身对Database Page的使用压力

      当Total Server Memory已经达到设定的Max Server Memory或者无法从OS获得更多内存,但是经常访问的数据量又远大于物理内存用于数据缓存的容量时,SQL Server被迫将内存的数据移入又移出,用于完成当前查询。

      观察如下性能计数器:

          1. SQL Server:Memory Manager-Total Server Memory 和 SQL Server:Memory Manager-Target Server Memory两者值将会相等。但是前者不会大于后者。

             2. 将会出现“分析方法”所述之情况。

       解决方法:既然SQL Server没有足够内存存放Database Page,那就要么增加SQL Server使用的内存量或者减少其使用的内存里

                    增加:可以通增加物理内存,启用AWE等方法。

                    减少:可以通过横向扩展,有两台或者多台服务器分别载部分库;优化相关读取量较大的语句等。

  3. Buffer Pool中的Stolen Memory压力

       正常情况下Buffer Pool中的Stolen Memory不会给Database Pages造成压力。因为Database Pages有压力,会触发Lazy Writes,同时SQL Server 会清理Stolen Memory中的执行计划缓存。

         但是,如果用户申明了过多的对象,而没有登出,并且占用内存过多,就会压缩Database Pages.如:游标,自定义引用的执行计划等。

       解决方法:通常是会表现为a)用户提交的请求因内存不足无法完成,701错误;b)需要压缩某些clerk的内存量,来完成用户请求,造成响应延时和缓慢。

                     通过查询sys.dm_os_memory_clerks的字段Single_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。

   4. Multi-Page的压力

       multi-page跟Buffer Pool共享OS的虚拟地址空间,如果multi-page使用过多内存,就会压缩Datbase pages。multi-page内存用量一般较小且相对固定,可能发生的情况有:

          a. 未开启AWE的32位SQL Server只有2G地址空间,且用-g启动参数扩展的MemToLeave的上限。

          b. 64位SQL Server调了内存泄露的第三方代码。

          c. 使用带有大量参数或者较长的”IN”语句

          d. 调高了Network Packet Size,大于或等于8KB,并且较多这种连接。

          e. 大量复杂XML查询,或者第三代码。

       解决方法: 通过查询sys.dm_os_memory_clerks的字段multi_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。      

本文由9159.com发布于www.9159.com,转载请注明出处:Server自己管理内存,这部分内存使用会相对较稳

关键词:

上一篇:没有了
下一篇:没有了