配置SQL Server服务帐户特权

SQL Server服务帐户中有两个特权(选项)通常应该被配置,"锁定内存页"和"执行卷维护任务"."锁定内存页"允许SQL Server阻止缓冲页的缓存,"执行卷维护任务"允许SQL Server使用即时文件初始化功能.在开始配置和测试之前,先来看看SQL Server的缓冲管理器怎么管理缓冲内容,并且SQL Server数据库文件如何增长

内存缓冲
Sqlserver 执行查询时,执行引擎从缓冲管理器中请求数据,缓冲管理器管理着内存中的数据页并且追踪他们的使用情况.

如果数据页已经存在于缓冲池中,他们会立即被(缓冲管理器)传送到执行引擎中,否则数据会被先读取到缓冲池中.
所有的数据页都会被尽可能多的存储到缓冲池中,不过最终情况往往是一部分数据页必须从缓冲池中被删除以便为其他数据页让出空间.

SQL Server使用一种基于使用次数和时间的方法来判断究竟哪些数据页不太可能被再次使用,并且根据这个方法在需要的时候从缓冲池中删除数据页

但是缓冲管理器有一个缺点,他并不知道数据页究竟存在于物理内存还是虚拟内存中.当外部程序造成内存不足时,Windows可能把一些SQL Server的内存移动到页面交换文件中,一个程序(进程)通常通过交换区或者页面交换文件被获知.但缓冲管理器并不知道,仍然会去内存中访问那些已经被移走的数据页,因为它认为他们还在那.(因为没人通知他,地位太低了^^)下次执行引擎请求这些数据页时,缓冲管理器从内存中选取数据页时,所以Windows不得不再次去检索这些页面交换文件.大部分时候,数据交换文件被存储在系统(启动)区,而这往往是服务器最慢的一个驱动器.因此,当我们存取被windows缓冲的数据页(非物理内存)时,我们不仅必须从磁盘上检索数据,而且一般是要从非数据库所在的磁盘上(系统启动盘)检索数据.这样我们就会感到性能的严重下降.

我们能做的就是防止Windows把数据页从缓冲池移动到页面交换文件(注:这里似乎值得商榷,可以试试设置临时文件和磁盘交换文件到一个性能很好的驱动器),可以通过修改SQL Server“锁定内存页”选项这个方法.SQL Server2000企业版,SQL Server 2005标准版 SP3 CU4,SQL Server 2008 SP1 CU2和SQL Server 2008 R2通过跟踪标记845实现.

数据文件
大多数的情况下,SQL Server被配置成自动增长.存储在物理磁盘同一个位置的老的数据都会清空掉,对于数据文件,SQL Server使用"全局分配映射表"(GAM)来标识是否此区域(临近页)已被分配或可使用.这样严格来说,SQL Server不再需要重新清空最近的已保留空间..但是日志文件并不使用"全局分配映射表",所以已分配的日志空间将一直在使用前被清除.

如果给予SQL Server"执行卷维护任务"权限,将允许SQL Server为数据文件使用"即时文件初始化"的功能."即时文件初始化"可以在瞬间对数据文件进行初始化(不用0填充,类似于磁盘的快速格式化)。在某些情况下,比如你有一个多用户的数据库实例,这可能会引起安全问题.当数据文件增长的时候,可能被分配到以前已经被分配到的磁盘空间,即便是不同的实例,也可能被拥有系统管理员角色的用户用DBCC PAGE命令来获取到前一个数据库的内容.所以不要随便配置"瞬时文件初始化".当然,如果你以后关闭这个功能,不会造成已分配(但未使用)数据页的丢失.

注:参考http://msdn.microsoft.com/zh-cn/library/ms175935.aspx

权限配置
这个选项可以通过本地安全策略(secpol.msc)来配置,或者也可以通过组策略.在后一种情况(组策略),你可以通过 "计算机管理-策略-windows设定-安全设定-本地安全策略"来找到.在域环境下,通过设置组策略对象(GPO)可以大幅缩减管理费用.在上面的例子里,请注意组策略对象被应用到计算机帐户.并且权限必须在分配到SQL Server的服务帐号,如果想更方便的管理这一功能,推荐为你的SQL Server服务帐户创建一个活动目录组,然后赋予这个活动目录组相应的权限.如果这是个普通的用户组,实际上你可以使用一个单GPO来覆盖整个域森林.

如果你因为安全策略不能给活动目录分配这个权限,你可能不得不创建几个GPO或者到每台服务器去配置了.在下面的例子里,SQL Server在"Network SERVICE"帐号下运行,并且已经对此帐号赋予了权限"锁定内存页"和"执行卷维护任务"

在支持此功能的sqlserver标准版本上,你需要配置SQL Server启动的时候追踪845标志.

确认包括分号,并且不要有空格存在,否则会造成SQL Server启动失败.

最终测试

你需要重启SQL Server服务来使更改生效.如果"锁定内存页"正确配置,执行下列命令,SQL Server将会在错误日志中记录下缓冲池使用的"锁定内存页"的情况.

exec master.dbo.xp_readerrorlog 0, 1, 'Using Locked Pages For Buffer Pool';

检测是否"执行卷维护任务"被正确配置,可以设置标记3004来跟踪"即时文件初始化"的相关信息.3605标记会把结果记录到SQL Server错误日志.如果你创建数据库的时候设置了这两个跟踪标记,SQL Server错误日志将会显示日志文件被清空,但数据库文件不受影响.以下的脚本会告诉你是否"即时文件初始化"正被使用.

dbcc traceon(3004,-1);
dbcc traceon(3605,-1);

create database InstantFileInitialization;
go

drop database InstantFileInitialization;
go

create table #TempLog (
  LogDate datetime,
  ProcessInfo varchar(20),
  [Text] varchar(max)
);

declare
  @Count int;

insert into #TempLog
exec master.dbo.xp_readerrorlog 0, 1, 'InstantFileInitialization';

select @Count=Count(*) from #TempLog where [TEXT] like 'Zeroing %.MDF'

drop table #TempLog

if @Count = 0
select
  case
    when @Count = 0 then 'Using Instant File Initialization for data files'
    else 'Instant File Initialization is not being used for data files'
  end as [Status]
go

dbcc traceoff(3004,3605,-1);

原文地址:http://www.sqlservercentral.com/articles/Lock+Pages+in+Memory/87213/

作者:Ole Kristian Velstadbråten Bangås, 2012/02/28