一. 前言

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

 内存数据库,指的是将数据库的数据放在内存中直接操作。相对于存放在磁盘上,内存的数据读写速度要高出很多,故可以提高应用的性能。微软的SQL
Server 2014已于2014年4月1日正式发布,SQL
2014一个主要的功能即为内存数据库。

函数是由一个或多个Transact-SQL
语句组成的子程序,可用于封装代码以便重新使用。用户自定义函数(User
Defined Functions)是SQL Server
的数据库对象,它不能用于执行一系列改变数据库状态的操作,但它可以像系统函数一样在查询或存储过程等的程序段中使用,也可以像存储过程一样通过EXECUTE
命令来执行。用户自定义函数中存储了一个Transact-SQL
例程,可以返回一定的值。
在Sql Server中根据函数返回值形式的不同将用户自定义函数分为三种类型:
1.标量型函数(Scalar functions)
  标量型函数返回一个确定类型的标量值其返回值类型为除TEXT、 NTEXT、
IMAGE、CURSOR、 TIMESTAMP 和TABLE
类型外的其它数据类型。函数体语句定义在BEGIN-END语句内,其中包含了可以返回值的Transact-SQL
命令。Code is
cheap.举个例子来说,我们平时在设计数据库的时候,设计主键的方法通常有三种,自增字段,guid和由年月日时分秒微秒加上几位(3到5)位内的随机数组成的长整数,比如20092699035787这种形式,下面就通过网上的和自己的总结来实现第三种主键的生成函数:

–缓冲区池消耗内存总量
SELECTSUM(multi_pages_kb +
virtual_memory_reserved_kb +
shared_memory_reserved_kb
+awe_allocated_kb) AS[Used by
BPool,kb]
FROM sys.dm_os_memory_clerks
WHERE type=’MEMORYCLERK_SQLBUFFERPOOL’

  SQL2008在数据库引擎方面的亮点是:

二. 内存和硬盘

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

目前来说,数据库镜像和复制是无法与内存优化表兼容的,但AlwaysOn,日志传送,备份还原是完整支持。

图片 1图片 2Code
USE [b2b]
GO
/****** Object:  UserDefinedFunction [dbo].[CreateTid]    Script Date: 02/06/2009 19:55:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[CreateTid](@date datetime) 
–参数的类型和长度必须指定大小如果不指定会出现值的丢失 
–返回值的长度要指定
–需要注意的是,系统函数中,有两个常用的,是不可以在函数中使用的,分别是newid和rand

–查询确认哪些内部组件窃取了缓冲区池中大部分的页面
SELECTTOP10 type,
SUM(single_pages_kb) AS
stolen_mem_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(single_pages_kb) DESC

  1、压缩(行压缩和页压缩)
  2、TVP
  3、Merge语句
  4、备份压缩
  5、基于文件系统的lob存储(filestream)
  6、备份加密
  7、空间数据类型
  8、稀疏字段
  9、资源调控器
  ……

  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。

 

–由于函数内不能调用GetDate()以及Rand()这样的不确定函数
–所以只有变通的去实现年月日时分秒 就直接由GetDate()做为参数 传入该Function
–而1~1000间的随机数 就借助于存储随机数的视图V_RandNum来实现

–缓冲区池外分配了内存的内部组件
SELECTTOP10 type,
SUM(multi_pages_kb) AS
menory_allocated_kb
FROM sys.dm_os_memory_clerks
GROUPBY type
ORDERBYSUM(multi_pages_kb) DESC

  而且sql2008的SQL Server Management
Studio很好用,支持单步调试和IntelliSense(智能语法提示)。编写SQL很舒服。  但是请同时安装sql2008
sp1,否则报表会内存溢出,SQL Server Management
Studio在表过多的时候会无法显示。

三 sql server 内存 架构

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

           
  图片 3图片 4

由于内存表数据的存放机制和普通表(基于磁盘的表)完全不同,因此内存表的数据需要一个特别的文件夹(注意不是文件哦)来存放

RETURNS bigint
BEGIN
declare  
–@tmpRandNum  随机数
@tmpRandNum  int, 
@tid bigint
SELECT @tmpRandNum=tmpRndNum FROM V_RandNum
set @tid=convert(varchar(4),DatePart (Year,@date))+convert(varchar(2),DatePart (Month,@date))+convert(varchar(2),DatePart (Day,@date))+convert(varchar(2),DatePart (Hour,@date))+convert(varchar(2),DatePart (Minute,@date))+convert(varchar(2),DatePart (Second,@date))+convert(varchar(5),@tmpRandNum)
RETURN @tid
END

    名词术语 

USE [master]
--创建数据库
CREATE DATABASE [TestDB]
ON  PRIMARY 
( NAME = N'TestDB', FILENAME = N'D:\SQL2104\SQLData\TestDB.mdf' , 
SIZE = 204800KB , MAXSIZE = UNLIMITED, FILEGROWTH = 204800KB ) 
LOG ON 
( NAME = N'TestDB_log', FILENAME = N'D:\SQL2104\SQLData\TestDB_log.ldf' , 
SIZE = 204800KB , MAXSIZE = 2048GB , FILEGROWTH = 204800KB )
GO
--创建内存表使用的文件组
ALTER DATABASE [TestDB] ADD FILEGROUP [TestDB_MFG1] CONTAINS MEMORY_OPTIMIZED_DATA
GO
--创建内存表使用的文件夹
ALTER DATABASE [TestDB] 
ADD FILE ( NAME = N'TestDB_MDir1', FILENAME = N'D:\SQL2104\SQLData\TestDB_MDir1') 
TO FILEGROUP [TestDB_MFG1]
GO

上面函数已经注释的很明白,函数需要辅助视图来实现rand()函数的功能,下面是视图的创建方法:

   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对象

对于每个数据库,只能创建一个内存优化文件组(Memory-Optimized
Filegroup),而对于每个内存优化文件组,可以创建多个关联的文件夹。

图片 5图片 6Code
USE [MyTestDb]
GO

         3.2   single-page 

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

 PS:内存优化文件组基与FILESTREAM文件组,但是无需为内存优化文件组来启用FILESTREAM。

/****** Object:  View [dbo].[V_RandNum]    Script Date: 02/06/2009 20:10:43 ******/
SET ANSI_NULLS ON
GO

         3.3 multi- page

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

创建内存表

SET QUOTED_IDENTIFIER ON
GO

      3.4  any size page

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

创建内存表限制比较多:

CREATE VIEW [dbo].[V_RandNum]
AS
SELECT CAST(RAND()*80000+10000 as int) AS tmpRndNum

  四. sql server 2008 内存

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

1.
支持的数据类型:

GO

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

  1. 内存优化表支持自增,但唯一允许用于 seed 和 increment 的值为
    1;(1,1) 是 seed 和 increment 的默认值;

  2. 内存优化表不支持CHECK约束,在非hash索引列上也不行;

  3. 内存优化表不支持使用的排序规则所具有的代码页并非 1252 的数据类型
    char(n) 和 varchar(n);

  4. 内存优化表不支持nvarchar(max)或varchar(max)

  5. 内存优化表索引不支持未使用 *_BIN2
    排序规则的字符列上的索引,在非hash索引列上也不行;

  6. 内存优化表上的索引不支持索引键中有可为 Null
    的列,在非hash索引列上也不行;

  7. 内存优化表必须有至少一个索引或主键

调用函数的方式: select dbo.CreateTid(getdate()) as tid 
建议你测试一下该函数,这个函数生成的tid可能会重复(??),期待有缘人留言给出改进的方法。
2.内联表值型函数(Inline table-valued functions)
 
内联表值型函数以表的形式返回一个返回值,即它返回的是一个表内联表值型函数没有由BEGIN-END
语句括起来的函数体。其返回的表由一个位于RETURN
子句中的SELECT命令段从数据库中筛选出来。内联表值型函数功能相当于一个参数化的视图。好了,看例子吧:

  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分布式查询从远程数据库取回大量数据。

 创建内存优化表Demo

图片 7图片 8Code
USE [MyTestDb]
GO
/****** Object:  UserDefinedFunction [dbo].[f_Split]    Script Date: 02/06/2009 19:43:44 ******/
SET QUOTED_IDENTIFIER ON
GO
— 自定义字符串拆分函数 类似c#的string.split()方法
CREATE Function [dbo].[f_Split] 

@content varchar(8000), 
@seperator varchar(20) 

RETURNS @temp table 

Id int identity(1,1), 
Value varchar(8000) 

BEGIN 
declare @CurrIndex int, @NextIndex int 
select @CurrIndex = 1 
select @NextIndex = 1 
select @NextIndex=charindex(@seperator, @content) 
while (@NextIndex > 0) 
begin 
if (@NextIndex >= @CurrIndex+1) 
begin 
insert into @temp (Value) values (substring(@content, @CurrIndex, @NextIndex-@CurrIndex)) 
end 
select @CurrIndex = @NextIndex + 1 
select @NextIndex=charindex(@seperator, @content, @CurrIndex) 
end 
if @CurrIndex <= datalength(@content) 
insert into @temp (Value) values (substring(@content, @CurrIndex, datalength(@content)-@CurrIndex+1)) 
return 
END

  2. 按申请方式分类

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

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

CREATE TABLE [dbo].[TB1_IM]
(
    [c1] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY 
    NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000), 
    [c2] [nchar](200)  COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL 
    INDEX ix_c2 NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
    [c3] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    [c4] [nvarchar](200) COLLATE Chinese_PRC_Stroke_90_BIN2 NOT NULL,
    INDEX ix_c3_c4 NONCLUSTERED HASH(c3,c4) WITH(BUCKET_COUNT=1000000),
    INDEX ix_c2_c3  NONCLUSTERED (c2,c3)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

调用的方式:select value from dbo.f_Split(‘a,b,c,d,e,f,123′,’,’) as
tmp
试试看吧,很简单的。
3.多声明表值型函数(Multi-statement table-valued functions)
 
多声明表值型函数可以看作标量型和内联表值型函数的结合体。它的返回值是一个表,但它和标量型函数一样有一个用BEGIN-END
语句括起来的函数体,返回值的表中的数据是由函数体中的语句插入的。由此可见,它可以进行多次查询,对数据进行多次筛选与合并,弥补了内联表值型函数的不足。
这种函数类型的实例可以参考,不在赘述了。
期待留心的你和热心的你和我多多交流和总结。  

  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)

没有

一小部分

所有

所有

 

对于内存优化表,有两种持续性可以选择:SCHEMA_ONLY(非持久表)和SCHEMA_AND_DATA(持久表),SCHEMA_ONLY
选项会导致数据在实例重启后丢失;而对于SCHEMA_AND_DATA(持久表),又可以设置完全持久行还是延迟持续性,延迟持续性选项允许在事务提交时可以不立即将日志写入磁盘,从而提升性能,当然代价就是发生故障时可能丢失数据。

五.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
的内存使用了。

  如下图所示:

    图片 9

         使用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%'

     
  图片 10

 

 六  总结

  当您启动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)
改进

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图