sql server学习基础之内存初探

一. 前言

 对于sql server 这个产品来说，内存这块是最重要的一个资源， 当我们新建一个会话，相同的sql语句查询第二次查询时间往往会比第一次快，特别是在sql统计或大量查询数据输出时，会有这么感觉。除了第一次要编译生成执行计划,  在CPU,I/O 的影响外，最主要的是第二次查询是从内存缓存中读出，为什么是这样，sql server 内存里存储了什么，它与windows内存又有什么区别？  参考了一些资料 下面来试着讲讲。   

二. 内存和硬盘

为什么内存是宝贵的，在每个系统上都是有限的，就像你看到的1 tb的硬盘，但是你通常看到的是50-200 G的内存， 物理内存的访问速度非常快，不能超过一定的限制。在内存有限的情况下，如果所有的进程都使用了有限的内存，并且新的进程将无法为他们找到任何内存，这就出现了虚拟地址空间的概念（也称为VAS）。

Virtual Address Space(虚拟地址空间)

是指一个应用程序能够申请访问的最大地址空间。32位寻址空间最大是4G，   64位寻址空间最大是8TB。

VAS作为中间的抽象层的,  不是所有的请求都直接映射到物理内存,它首先映射到VAS然后映射到物理内存。因此，它可以更协调的方式管理对内存的请求，而不是让进程去做，如果不是这样，它很快就会导致内存崩溃。

在Windows操作系统中，VAS 的内核进程与用户进程之间的划分是相同的。对于32位系统，最大的VAS 是4 G的内核/ 2 G到应用程序的中，在这里，SQL Server是应用程序进程，当我使用word进程时，它意味SQL Server进程差不多一样，将得到2 G的VAS。因此，从理论上讲，这意味着任何应用程序进程在32位上运行的都将拥有最大限度的2 G。

三 sql server 内存 架构

sql server 内存管理，在sql server 2012发生了重大改变，对内存重新实现了一遍。  先看下版本之间内存管理图的区别

  　　名词术语

　　　3.1 BufferPool　　　　　　

　　　　SQL Server使用BufferPool缓冲池来有效地管理SQL Server进程的内存请求。它是SQL Server的最大内存消耗者。缓冲区是内存中的一个8 KB的页面，与数据或索引页面大小相同，您可以将缓冲区看作是一个框架，它在从磁盘到内存的时候保存数据和索引页。

　　　　SQL Server缓冲区管理器管理将数据页读入缓冲池的任务，并将其写入磁盘。它是SQL Server的预留内存存储，如果您不为它设置值，它将占用尽可能多的内存。因此，在spconfigure中为max server内存设置最佳值总是被推荐为一种良好的实践。缓冲池只将内存分配给需要少于8 KB页面的请求。

　　　　对于大于8 KB内存的所有请求，都是由windows API直接分配的。所有缓存存储计划、数据和索引页都存储在这个缓冲池中。当用户请求row/rows时，如果缓冲区池中没有，则使该页面从磁盘进入内存。这种输入/输出可能在繁忙的系统上特别昂贵，因此尽可能减少SQL服务器缓存的大小，这可能会被用户看作是内存泄漏或SQL Server占用大量内存，但实际上它提高了性能，实际上这个特性是通过设计实现的。

　　　　下面这些内存不是来自缓冲池：
　　　　　　SQL LCR
　　　　　　扩展存储过程
　　　　　　链接服务器分配的内存
　　　　　　内存管理器完成的大页面分配（大页面为任意页面＞8 KB）
　　　　　　COM对象

         3.2   single-page

                     这块内存是<=8kb 的存储，适用于sql server 2008及以前， 属于buffer pool 缓冲池来分配。有存储数据页面，Consumer功能组件。

         3.3 multi- page

                     这块内存是>8kb的 存储，适用于sql server 2008及以前， 不属于buffer pool 缓冲池来分配,  有存储Consumer功能组件, 第三方代码, Threads线程。

    　 3.4  any size page

                      这个适用于sql server 2012及以上，整合了single-page,multi-page 统称pages。

四. sql server 2008 内存

从内存图我们可以看到有 page reservation  需预先申请的内存， 有momory objects 从windows api申请的内存，  有clr第三方申请的内存。

内存的分类方式有很多，下面介绍三种方式：

　　1. 按用途分类

                 1.1 Database Cache(数据页面缓冲区)

                          当用户修改了某个页面上的数据时，sql server会在页存中将这个页修改。但不会立刻将这个页面写回硬盘，而是等后面的checkpoint 或lazy write集中处理。

                 1.2 各类Consumer功能组件

                            Connection 连接：包括输入缓冲池和输出缓冲池, 用来存储用户指令和返回结果。

                            General ：一组大杂烩: 语句，语句编译，范式化，锁数据结构，事务上下文，表格，索引的元数据等。

                            Query paln：语句和存储过程的执行计划。

                            Optimizer：sql server在生成执行计划的过程中需要消耗的内存。

                            Utilities：像BCP, Log Manager,Parallel Queries,Backup

                 1.3    线程内存

                             为每个线程分配0.5MB的内存

                 1.4    第三方代码申请的内存

　　　　　　　　如用户定义的CLR,Linked Server分布式查询从远程数据库取回大量数据。

　　2. 按申请方式分类

 　　　  申请方式是指要先预先Reserve一块大的内存，然后再一小块一小块的commit。对Database Cache是会先Reserve，再commit。

　　　　其他所有内存使用，基本都是直接commit，都叫Stolen。

　　3. 按申请大小分类（上面的内存图就是这种分类）

　　　　有二种内存申请单位:  一种是小于或等于8KB的，称为Buffer Pool，一次一个页面的这种分配，被称为single page allocation.

 　　　  一种是大于8kb的，称为Multi-page（以前叫MemToLeave）,这种分配，被称为 Multiple Page Allocation.

　　　　注意这里的很大一部分内存不受 sql server本身控制.因为第三方代码申请的内存都放在Multi-page里.

 　内存分类方法之间的关系

五.sql server 2012 内存

在 sql server 2012里，single page  allocator 和multi page allocator 统一起来了，叫做any size page allocator。max server memory 不再像以前的版本那样，只控制buffer pool的大小，也包括那些大于8kb 的内存请求。也就是max server memory 能够更准确地控制SQL Server 的内存使用了。

　　如下图所示：

         使用dmv 来查看当前实例的总内存空间，以及占用内存空间

　　　　--Target Server Memory (KB)最多能申请的内存量
　　　　--Total Server Memory (KB) 目前使用了多少内存量

         从下面的空间占用也可以看出来， 给sql server有分配多少内存， 它就会占用多少内存，以达到性能的最优。

select counter_name, ltrim(cntr_value*1.0/1024.0/1024.0)+'G' 
as memoryGB from master.sys.dm_os_performance_counters 
where counter_name like '%target%server%memory%'or counter_name like '%total%memory%'

 六  总结

当您启动Microsoft SQL Server时，SQL Server内存使用量可能会继续稳步增长，而不是减少，即使服务器上的活动很低。此外，任务管理器和性能监视器可能显示，计算机上可用的物理内存会逐渐减少，直到可用内存在4 MB到10 MB之间。这种行为本身并不表示内存泄漏。这种行为是典型的，并且是SQL Server缓冲池的预期行为。

默认情况下，SQL Server根据操作系统报告的物理内存负载动态地增长和缩小缓冲池（缓存）的大小。只要有足够的内存（4 MB和10 MB）可以防止分页，那么SQL Server缓冲池就会继续增长。当与SQL Server在同一台计算机上分配内存时，SQL Server缓冲管理器将根据需要释放内存。SQL Server可以每秒释放数兆字节的内存。这允许SQL Server快速地适应内存分配更改。

您可以为SQL Server数据库引擎使用最小服务器内存和最大服务器内存配置选项使用多少内存（缓冲池）设置上限和下限

请注意，通过上图设置内存最大 max 只限制SQL Server缓冲池的大小。不限制SQL Server为其他组件分配的剩余未保留内存区域，如扩展存储过程、COM对象、非共享dll、EXEs和MAPI组件。由于之前的分配，SQL Server私有字节的数量超过了最大服务器内存配置。

后面章节在详细介绍内存的查看分析

参考文献：

• 　 SQL Server Memory and Troubleshooting
•       Microsoft SQL Server企业级平台管理实践
•       SQL Server 2012 内存管理 (memory management) 改进