SQL Server 即时文件初始化

ycycoco

一.本文所涉及的内容（Contents）

• 本文所涉及的内容（Contents）
  
• 背景（Contexts）
  
• 基础知识（Rudimentary Knowledge）
  
• 实现过程（Process）
  
• 疑问（Questions）
  
• 参考文献（References）
  

二.背景（Contexts）
　　    数据库服务器在为表分配初始值的时候很慢，分配初始值40GB的数据文件，花了30多分钟，一开始的时候一直认为是服务器磁盘的写入速度太慢造成的，后来经过北京-宋沄剑的提醒：即时文件初始化(Instant File Initialization)，设置这一选项之后，速度提升到了19秒，下面将描述这个优化的设置过程。

三.基础知识（Rudimentary Knowledge）
　　就数据库而言，以下几种情况需要对文件初始化：
　　1. 创建数据库；
　　2. 向现有数据库中添加文件、日志或数据；
　　3. 增大现有文件的大小（包括自动增长操作）；
　　4. 还原数据库或文件组；
　　　　执行上面的操作，操作系统需要用零来填充文件进行初始化。在 SQL Server 中，可以在瞬间对数据文件进行初始化。即时文件初始化将回收已使用的磁盘空间而不使用零来填充该空间。而在向文件中写入新数据时，磁盘内容将被覆盖。日志文件不能立即初始化。
　　　　即时文件初始化功能仅在向 SQL Server (MSSQLSERVER) 服务帐户授予了 SE_MANAGE_VOLUME_NAME 之后才可用。Windows Administrator 组的成员拥有此权限，并可以通过将其他用户添加到【执行卷维护任务】安全策略中来为其授予此权限。

四.实现过程（Process）
　　首先运行gpedit.msc，按照Figure1的路径，找到【执行卷维护任务】，如Figure2所示，默认的情况下它已经设置了Administrators组的；
　　
　　（Figure1：gpedit.msc）
　　
　　（Figure2：执行卷维护任务）
　　接着运行services.msc进入服务设置，查看到SQL Server (MSSQLSERVER)的登陆用户是【网络服务】（如Figure3所示），这就是造成初始化40GB文件花费了30多分钟的原因了，因为【网络服务】不具备SE_MANAGE_VOLUME_NAME的权限（可查看Administrators组成员）；
　　
　　（Figure3：SQL Server (MSSQLSERVER)）
　　双击SQL Server (MSSQLSERVER)服务进入设置，在登陆的选项卡中可以看到帐号是：NT AUTHORITY\NETWORKSERVICE，如Figure4所示。
　　
　　（Figure4：NT AUTHORITY\NETWORKSERVICE）
　　直接勾选【本地系统帐号】，在重启SQL Server (MSSQLSERVER)时遇到了下图的错误：
　　
　　（Figure5：重启服务报错）
　　查看了相关的系统事件日志，在SQL Server服务无法使用管理员身份启动 中解决了（禁用掉SQL Server的协议Shared Memory），设置完之后重启服务SQL Server (MSSQLSERVER)。
　　而另外一种思路就是把NT AUTHORITY\NETWORKSERVICE加入到Administrators组中，如Figure6所示。注意：这种方式一样需要重启SQL Server (MSSQLSERVER)服务。
　　
　　（Figure6：Administrators组）
　　下面我们就来测试下创建40GB文件的性能对比：



--测试即时文件初始化
CREATE DATABASE [TestFileInit] ON  PRIMARY
( NAME = N'TestFileInit', FILENAME = N'F:\DBBackup\TestFileInit.mdf' , SIZE = 41943040KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'TestFileInit_log', FILENAME = N'F:\DBBackup\TestFileInit_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
　　
　　（Figure7：之前创建时间）
　　
　　（Figure8：之后创建时间）
　　注意：禁用即时文件初始化功能，要想让这个禁用生效，一样需要重启SQL Server (MSSQLSERVER)服务。

五.疑问（Questions）
　　1. 在安装SQL Server的时候，如何设置会使得SQL Server服务是以【网络服务】登陆的？
　　2. 禁用掉SQL Server的协议Shared Memory，这个协议是用来干嘛的？有什么作用？
　　3. 如果把NT AUTHORITY\NETWORKSERVICE加入到Windows组里面有什么不安全隐患嘛？
　　4. 当启用 TDE 时，即时文件初始化功能不可用。什么是TDE？

六.参考文献（References）
　　数据库文件初始化
　　为SQL Server 2005配置Windows即时初始化
　　Local System/Network Service/Local Service权限详解