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里.

 　内存分类要领之间的关系

类型	Database cache 数据页面缓冲区	Consumer 功能组件	3 Party code 第三方代码	Threads 线程
Reserved/Commit	是	个别不是	个别不是	不是
Stolen	不是	是	是	是
Buffer Pool (single- page)	所有	绝大局部	没有	没有
MemToLeave (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) 改善