笔记30-徐 SQLSERVER内存管理模式与接口

笔记30-徐 SQLSERVER内存管理模式与接口

--SQLSERVER内存管理模式与接口


--SQLSERVER是个很喜欢内存资源的程序，他的理想状态就是把所有可能会用到的数据和结构
--都缓存在物理内存里，以达到最优的性能。

--SQLSERVER会约束自己的内存使用量，但是最好作出合理的分配

--SQL会缓存大量的数据页面，他还会缓存很多其他信息，包括存储过程的执行计划
--特定用户的安全上下文等
--如果这些信息没有在数据库中缓存，SQL都要重新计算一遍，花额外的时间

--SQLSERVER开发出来的内存调节接口却很少，只有下面几个：

--1、min server memory （MB）(sp_configure接口)
--定义SQL的最小内存值 需要澄清亮点
--（1）min server memory 是一个逻辑概念，控制SQLSERVER Total Server Memory的大小
--至于数据是放在物理内存还是缓存文件里，由Windows决定。所以这个值不能保证SQL使用
--的最小物理内存数

--（2）min server memory 的意思是，在SQL的地址空间增长到这个值以后，就不会再小于这个
--值。当SQL刚启动的时候，仅仅申请启动所需要的内存。所以这个时候内存使用量小于min server memory
--也是正常现象



--2、max server memory （MB）(sp_configure接口)
--定义SQL的最大内存值 需要澄清亮点
--（1）max server memory 也是一个逻辑概念，控制SQLSERVER Total Server Memory的大小
--至于数据是放在物理内存还是缓存文件里，由Windows决定

--（2）max server memory 只能控制SQL的一部分内存使用量，SQL的virtual bytes或working set大于
--你设置的max server memory 也是一个正常现象。并不是SQL有内存泄漏问题


--3、Set Working Set Size(sp_configure接口)
--在SQL的执行代码中调用一个Windows参数，试图将SQL在物理内存中使用的内存数固定下来。
--但是现在的Windows版本已经不再尊重这个请求。所以设置这个参数已经没有任何意义
--有时候还会带来副作用，请不要使用

--4、AWE Enabled(sp_configure接口)
--让SQL开启用AWE方法申请的内存，以突破32位Windows2GB用户寻址空间。所以这个
--设置对32位SQL意义重大。但是不是所有的SQL内存都会用AWE。AWE扩展出去的的地址
--空间不是什么数据都能存放。这个也是为什麽32位AWE效果没有64位好的一个重要
--原因。



--5、lock pages in memory(企业版会自动开启)
--这个开关是能在一定程度上确保SQL的物理内存数，但是不十分可靠。


--调节SQL内存的使用参数比较有限。SQL有一套比较完善的内存自我管理调度机制，这是其他应用
--软件难以企及的。绝大多数情况下，SQL能够很好做自我调节，满足用户需求，维护SQL性能优化




------------------内存使用分类--------------------------------------------------------
--按用途分:

--（1）database cache
--1存放8KB数据页面的缓存区,8KB是数据访问的最小单元

--2用户写数据到数据页面，等到checkpoint或lazy write才把数据页面写回硬盘

--3用户使用完页面之后，SQL如果没有内存压力，不会马上删除页面，等以后用户
--再访问这个页面，不用到磁盘读取。所有数据页面都缓存在内存里，等到checkpoint
--的时候把修改过的页面写入硬盘



--（2）SQL的很多功能组件都需要申请内存来完成他们的任务，功能组件简称：consumer

--connection（连接池）:SQL为每一个连接分配一个数据结构，存储关于这个连接的信息。另外还会分配一个
--输入缓冲池，缓冲客户端发来的指令；一个输出缓冲池，存放SQL返回的结果集，等待客户端取走

--general:一组大杂烩，包括：语句的编译、范式化、每个锁数据结构、事务上下文、表格、索引的元数据

--query plan:语句和存储过程的执行计划。和database cache类似，如果SQL没有内存压力，他会保留每一个
--生成的执行计划，供以后用户使用，减少编译消耗。所以执行计划也是一块比较大的内存使用区域

--optimizer:SQL在生成执行计划的过程中需要消耗的内存

--utilities:像BCP、log manager、paraller queries并行查询、backup等比较特殊操作需要的内存


--（3）线程内存
--SQL会为进程内的每个线程分配0.5MB的内存，以存放线程的数据结构和相关信息


--（4）第三方代码申请的内存（COM,XP...）
--在SQL进程里，会运行一些非SQL自身的代码。例如：用户定义CLR或扩展存储过程EXEC sys.xp_cmdshell
--代码，链接服务器linked server需要加载的数据连接驱动，调用SQL Mail功能需要加载的MAPI动态库等。
--这些代码运行也会申请内存，会算在SQL进程的虚拟地址空间里。但是由于他们不是SQL自身的代码，
--所以他们申请了多少内存SQL自己都不知道（但是作为一个进程，Windows能够知道SQL申请的所有内存）
--所以也能计算这一部分的内存使用




--按申请方式分类
--第一种：SQL内存申请预先reserve一块大内存，然后在使用时一小块一小块地commit
--第二种：内存申请直接从地址空间里commit ,这种称为stolen

--database cache:预先reserve一块大内存，然后在使用时一小块一小块地commit
--其他：内存申请直接从地址空间里commit ,这种称为stolen，不是内存泄漏，也是正常使用

--AWE扩展出去的内存只能用来存放database cache，其他内存还是要在2GB内存里想办法


--按申请大小分类
--SQL有两种内存申请单位
--1所有可以分成小于或等于8KB一个单位的内存申请，SQL会分配一个页面8KB。所有这些页面都集中管理，这块
--内存被称为BUFFER POOL,一次一个页面的分配称为single page allocation

--2大于8KB单位的内存申请，SQL把他们集中在另外一个区域，成为multi-page/memtoleave，
--称为multi page allocation

--对于SQL，大部分内存需求都可以以8KB为单位，放在buffer pool里

--按用途分类：
--database cache:由于都是数据页面，所以都是以8KB为单位，存储在buffer pool里面

--其他consumer:
--connection（连接池）:和net work package size（客户端和SQL通信的每个数据包大小）有关系
--如果是默认4KB设置，输入输出缓存会放在buffer pool里，如果改成8KB或更大，输入输出缓存
--会放在 multi-page里
--net work package size的大小可以使用sp_configure来更改，但是任何一个客户端都可以指定
--他建立的连接自己的net work package size


--general:绝大部分内存，例如每个锁数据结构、事务上下文、表格、索引元数据等，都会以
--8KB为单位申请。但是如果偶尔一个语句特别长，他的编译和范式化需要使用大于8KB为单位
--的内存，会使用multi-page里的


--query plan、optimizer、utilities:和general类似，绝大部分内存使用会在buffer pool，
--一些特别长语句，会使用一部分的multi-page

--第三方的代码：由于这些代码申请的内存大小SQL自己不知道，所以SQL把他们的内存申请放在
--multi-page

--线程内存；每个以512KB为单位，自然放在multi-page里



-- 各种内存分类方法之间的关系
--                              database cache      consumer           3rd party code         threads     CLR code
--reserved/commit                 是                一般不是             一般不是              不是         是
--stolen                          不是              是                     是                    是         不是
--buffer pool(single page)        所有               绝大部分             没有                   没有       没有
--memtoleave(multi page)          没有               一小部分             所有                  所有        所有


--各部分内存的分布和大小限制

--在32位SQL里面，虚拟地址空间为2GB（除掉核心态的2GB）。这个2GB被SQL分成两部分：

--8KB为单位的buffer pool也称BPool

--不以8KB为单位分配的部分，memtoleave multi-page(之所以叫memtoleave，是因为SQL启动的时候会把这部分虚拟
--地址预留出来，地址空间是有上限的，SQL2005以上，memtoleave的默认大小是256MB加上sp_configure配置的最大
--线程数（服务器属性-》处理器-》最大工作线程数）乘以512KB，就是256MB+256(个threads)*0.5MB=384MB),
--剩下就是2GB-384MB=1664MB,就是buffer pool的最大值

--线程内存：SQL进程为每个线程分配0.5MB的内存，以存放线程的数据结构和相关信息

--SQL启动时buffer pool很少，随着缓存数据越来越多，SQL会不断commit内存给buffer pool，buffer pool
--大小达到max server memory或1664MB，SQL无法申请内存了

--memtoleave也一样，但是他的上限是SQL启动时算出来的，跟max server memory无关



--X64位系统 32位SQL：因为X64支持WOW，由于64位核心态已经不占用32位应用程序的进程的虚拟地址空间，所以
--WOW的32位应用程序进程用户态可以到4GB。memtoleave-MULTI-PAGE算法不变，还是默认的384MB。
--buffer pool可以达到3.6GB。如果开启了AWE，这3.6GB就可以基本都给buffer pool里的stolen memory使用了


--如果用户频繁使用linked server或扩展存储过程EXEC sys.xp_cmdshell等会使用multipage的功能
--multi-page会变得非常繁忙，SQL会先使用默认的那一块256MB。如果这里使用完了，SQL会看看预留给
--线程那一块内存是不是用完。一般SQL不会把256个线程都创建出来，所以这里还是有地方
--SQL会在这里存放multi-page的申请，直到把这一块内存用完

--SQL会记录这些缓存被引用的次数，每个缓存页面和执行计划都有一个权值。每隔一段时间，权值减一
--当系统内存有压力时，SQL根据权值是0的那个缓存（即在一段时间没有被引用过）从内存里清理出去
--这个清理出去的动作由lazy writer完成，如果lazy writer经常被触发表明系统有内存压力

--memtoleave multi-page里的执行计划也归这个机制管理，当buffer pool有压力时，memtoleave里的
--一部分缓存也会被清理出去



--64位操作系统，SQL虚拟地址空间扩展了很多，不会再为multi-page预留一块地址空间。就没有了
--memtoleave这个概念。不管single page 还是multi page，都可以无约束地申请虚拟地址空间里的
--地址资源。所以在64位操作系统上会遇到一些32位不会遇到的问题





-----------------------database cache的上限一般由max server memory来控制-------------------------------------------


--64位SQL，stolen内存没有限制，只要物理内存足够，buffer pool还没有达到max server memory的限制
--就可以不断地缓存执行计划，尤其是小型数据库内存量比较大的服务器，stolen内存数量一般会超过
--database cache的数量。这种变化DBA要注意