笔记26-徐 SQLSERVER内存分配和常见内存问题

笔记26-徐 SQLSERVER内存分配和常见内存问题

--SQLSERVER内存分配和常见内存问题

--SQLOS:SQL 把他对系统资源调度尤其是对内存和CPU的调度的功能组件，称为SQLOS
--SQL是一个非常喜欢用内存的应用，如果内存不够，SQL一定会运行得非常艰难
--而且在内存使用上，容易出的问题也比较多


--1、SQL所占用的内存数量从启动以后，就不停地增长

--2、在Windows2003以上版本运行SQL，内存使用量突然急剧下降
--errorlog:
--spid1 a singnificant part of sqlserver process memory has been paged out
--this may result in a performance degradation 退化降级
--duration:0 seconds working set:1086400  commited:2160928 memory utilization:50%

--这类问题不是SQL自己导致的，而是Windows感觉到急迫的内存压力，迫使SQL释放内存


--3、用户在做操作时，遇到内存申请失败
--错误号：701（SQL2005或以上）     17803（SQL2000）    
--    8645（2000、2005）         17802（SQL2000）           17189（SQL2005或以上）
--SQL内存分成很多部分，不是用户想申请多少就申请多少的
--在内存分配上，SQL2005比SQL2000有了更明显的变化。SQL2000简单一些，SQL2005依然使用
--SQL2000的整体框架结构，但有了更复杂的实现方式。所以SQL2005以后的内存问题比SQL2000
--相比有了一些变化。


--4、内存压力导致的性能下降
--如果内存紧张，SQL会马上作出反应，进行自我调节，所以申请内存失败的几率
--不是太高。但是内存对性能的影响会非常显著。同一条语句，在内存充裕和
--紧张的情况下，速度可能会相差几十倍，甚至上百倍。通过性能监视器和
--动态管理视图来解决内存压力


--从操作系统层面看SQL内存分配
--各个应用程序包括SQL，都在通过VirtualAlloc之类的API向Windows申请内存。
--SQL申请内存都是通过一些公开的API完成的，Windows不会给SQL任何特殊照顾

--系统不缺内存不代表SQL就一定不缺内存

--Windows的一些内存术语
--虚拟地址空间virtual address space
--寻址空间最大是4GB ，在缓存文件paging file  跟物理内存里

--物理内存physical memory
--频繁访问的数据对象放在物理内存里，达到最优化

--保留内存reserved memory
--应用程序通过一些特殊API，首先保留一块内存地址空间，以供将来使用
--如果某块地址已经被其他对象保留，你去访问他，就会收到一个访问越界（AV ACCESS VIOLATION）
--错误，像WIN32的VirtualAlloc  和VirtualAllocEx之类的函数，就能提供这些功能
--需要说明的是这里保留的只是虚拟地址空间上的一段地址，而不是真正的物理内存空间



--提交内存commited memory
--将预先保留的内存页面正式提交使用。提交的页面在访问时最终转换到物理内存
--中的有效页面。也就是说，正式在物理内存中申请一段空间，向页面中存入数据

--分两步来预留和提交内存，通过推迟页面提交来减少物理内存的使用。对于
--潜在、大量、连续的内存缓存区的应用程序是很有用的。地址空间可以被保留
--在需要的时候再提交，而不是为了整个区域提交页面。这种技术在SQL中被
--广泛使用，用以缓存数据页面



--共享内存 share memory
--共享内存定义为对一个以上的进程都是可见的内存，或存在于多个进程的虚拟地址
--空间。例如，如果两个进程使用相同的DLL，只把DLL的代码页装入内存一次，其他
--所有映射这个DLL的进程只要共享这些代码页就可以了

--private bytes
--某个进程提交的地址空间commited memory中，非共享的部分

--working set
--某个进程的地址空间中，存放在物理内存的那一部分

--页面访问错误page fault soft/hard
--访问一个存在于虚拟地址空间但不存在于物理内存working set的页面
--就会发生一次page fault。
--Windows内存管理组件会处理每个页面访问错误，首先判断是不是访问越界
--如果不是，两种情况
--hard fault:目标页面存在于硬盘上，例如page file，这种访问会带来硬盘的读写
--soft fault:页面存在于物理内存中，还没有直接放在这个进程的working set下
--需要Windows重新定向一次，这种访问不会带来硬盘操作


--system working set
--Windows的working set的类型：system cache  paged pool  non page pool  system mapped views
--可以通过Windows性能监视器里的memory：cache bytes,发生在系统内存上的page fault 可以通过
--memory :cache fault/sec 看到



--系统高速缓存system cache
--用于映射在系统高速缓存中打开的文件页面，以提供磁盘I/O速度。
--性能监视器：memory:cache resident bytes 监控


--非换页区non paged pool
--包括一定范围的系统虚拟地址的内存交换区，保证在任何时候它都驻留在物理内存中
--可以在没有I/O调页的情况下从任何地址空间访问。非页交换区可以在系统初始化时
--创建，并且在内核模式组件用来分配系统内存。
--性能监视器：memory:pool nonpaged bytes 监控

--这一块缓存可以被所有的进程共享，一个最常见的用途是存放所有对象的指针object handles

--页交换区paged pool
--系统空间中可以调入或调出系统进程工作集working set的虚拟内存区域。页交换区在系统
--初始化时候创建，被内存模式组件用来分配系统内存。
--性能监视：memory :pool paged bytes  ,pool paged resident bytes 监控

--栈stack
--每个线程有两个栈，一个给内核模式，一个给用户模式。每一个栈是一块内存空间。
--存放线程运行的过程或函数的调用地址，以及所有参数的值


--in process
--运行在同一个进程的地址空间里,例如一个进程需要加载一个DLL文件,这个DLL文件里
--的代码也会去申请内存.如果运行在同一个进程的地址空间里,最大的好处是速度快
--不需要做上下文切换context switch。但是明显缺点是DLL在内存使用上管理不善，
--出现严重错误会影响整个进程的安全性


--out of process
--运行在不同的进程地址空间里，以上面的DLL为例，像OLEDB这样的驱动程序可以配置
--成运行在DLLHOST.exe的进程空间里

--内存泄漏memory leak
--一直不断地提交或保留内存资源，哪怕它们不再使用，也不释放给其他用户重用
--SQL的内存泄漏有两种：
--1、SQL作为一个进程，不断地向Windows申请内存资源，直到整个Windows内存耗尽
--2、SQL内部某个SQL组件不断地申请内存，直到把SQL能申请到的所有内存都耗尽
--使得其他SQL功能组件不能正常使用内存。

--由于SQL完善的内存管理机制，前一种比较少见，而且问题往往不是SQL自身，常见的是
--后一种泄漏，而且这种泄漏与客户端的操作有直接关系


--32位下Windows地址空间和AWE
--32位Windows用户进程会有4G虚拟地址空间，其中2G给内核态，2G给用户态
--这两部分严格分开。Windows不会因为其中某一块内存地址空间用尽而将
--另外一块空间让出。

--由于SQL的绝大部分指令都运行在用户态下，所以SQL的用户态地址空间资源也只有2G


--在32位Windows中，2G地址空间使得SQL最多只能使用2G内存，严重阻碍了SQL有效利用硬件资源

--方法一：在Windows2003的Boot.ini文件里使用/3GB参数
--企业版下使用这个参数，使得Windows将核心态地址空间由2G降到1G，将用户态空间由
--2G升到3G，加起来还是4G地址空间
--后果是大大缩小system cache里面的一些结构大小，导致核心态内存空间耗尽，而核心态出问题
--会影响整个服务器的稳定。



--方法二：地址空间扩展address windowsing extensionsAWE。允许32位应用程序
--分配64G物理内存，并把视图或窗口映射到2G虚拟地址空间的机制

--使用AWE，解决了2G地址空间的限制，使得一个应用程序能够访问最多达64G的物理内存

--SQL2000的企业版，SQL2005/2008的企业版和标准版都支持这个技术，也能够享受
--这个技术带来的好处。
EXEC sys.sp_configure @configname = 'AWE Enabled', -- varchar(35)
    @configvalue = 1 -- int
RECONFIGURE
GO

--重启SQL服务即可


--(1)开启这个功能需要SQL启动帐户在Windows上的lock pages in memory权限。没有这个权限，
--AWE就不能成功被开启。启动的SQL这时候只能使用2GB的地址空间。
--所以DBA要确认一下SQL的errorlog里有没有相关的信息
--成功开启：server  Address Windowing Extensions enabled
--消息
--Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
--开启失败：Cannot use Address Windowing Extensions because lock memory privilege was not granted

--(2)这个功能是在应用层面有意识地使用，而不是在Windows层面实施的。也就是说SQL在申请内存时，
--通过特殊API调用申请到的，如果SQL不调用这个功能，就还会在普通的2GB虚拟地址空间申请内存。
--在SQL中不是所有的内存申请都会调用AWE技术，只有先reserve,再commit的内存调用，SQL才使用AWE
--让他们使用到扩展的内存。其他方式申请的内存只能使用普通的2GB地址空间。


--正因为这样，AWE不能称为解决SQL地址空间不足的最终解决方法。
--使用64位服务器，虚拟地址空间可以达到8TB，大于绝大多数物理内存数，我见过最多的物理内存128GB
--在64位下运行的SQL，其性能往往比在32位上有比较明显的提高。

--各个版本Windows上支持的最大内存数
--配置                                               应用虚拟地址空间大小        最大物理内存数         是否支持AWE/locked pages support
--32位SQLSERVER                                              2GB                         64GB                   YES
--32位SQLSERVER    + /3GB boot.ini参数                       3GB                         16GB                   YES
--32位SQLSERVER   应用在x64位操作系统（WOW）                 4GB                         64GB                   YES
--32位SQLSERVER   应用在IA64操作系统（WOW）                  2GB                         2GB                    NO
--64位SQLSERVER   应用在x64操作系统                          8TB                         2TB TERABYTES          YES
--64位SQLSERVER   应用在IA64操作系统                         7TB                         2TB                    YES