SQL Server 即时文件初始化

简介: 原文:SQL Server 即时文件初始化一.本文所涉及的内容(Contents) 本文所涉及的内容(Contents) 背景(Contexts) 基础知识(Rudimentary Knowledge) 实现过程(Process) 疑问(Questions) 参考文献(References) 二.
原文: SQL Server 即时文件初始化

一.本文所涉及的内容(Contents)

  1. 本文所涉及的内容(Contents)
  2. 背景(Contexts)
  3. 基础知识(Rudimentary Knowledge)
  4. 实现过程(Process)
  5. 疑问(Questions)
  6. 参考文献(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组的;

wps_clip_image-982

(Figure1:gpedit.msc)

wps_clip_image-1353

(Figure2:执行卷维护任务)

接着运行services.msc进入服务设置,查看到SQL Server (MSSQLSERVER)的登陆用户是【网络服务】(如Figure3所示),这就是造成初始化40GB文件花费了30多分钟的原因了,因为【网络服务】不具备SE_MANAGE_VOLUME_NAME的权限(可查看Administrators组成员);

wps_clip_image-21225

(Figure3:SQL Server (MSSQLSERVER))

双击SQL Server (MSSQLSERVER)服务进入设置,在登陆的选项卡中可以看到帐号是:NT AUTHORITY\NETWORKSERVICE,如Figure4所示。

wps_clip_image-1487

(Figure4:NT AUTHORITY\NETWORKSERVICE)

直接勾选【本地系统帐号】,在重启SQL Server (MSSQLSERVER)时遇到了下图的错误:

wps_clip_image-9402

(Figure5:重启服务报错)

查看了相关的系统事件日志,在SQL Server服务无法使用管理员身份启动 中解决了(禁用掉SQL Server的协议Shared Memory),设置完之后重启服务SQL Server (MSSQLSERVER)。

而另外一种思路就是把NT AUTHORITY\NETWORKSERVICE加入到Administrators组中,如Figure6所示。注意:这种方式一样需要重启SQL Server (MSSQLSERVER)服务。

wps_clip_image-16463

(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

wps_clip_image-26367

(Figure7:之前创建时间)

wps_clip_image-8020

(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权限详解

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据查询
【数据库SQL server】关系数据库标准语言SQL之数据查询
58 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之视图
【数据库SQL server】关系数据库标准语言SQL之视图
57 0
|
1月前
|
SQL 人工智能 算法
【数据库SQL server】传统运算符与专门运算符
【数据库SQL server】传统运算符与专门运算符
35 0
|
1月前
|
SQL 数据库 数据安全/隐私保护
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
【操作宝典】SQL巨擘:掌握SQL Server Management的终极秘籍!
51 0
|
2月前
|
SQL 流计算
Flink CDC 1.12版本引入了对SQL Server的支持
【1月更文挑战第26天】【1月更文挑战第124篇】Flink CDC 1.12版本引入了对SQL Server的支持
24 1
|
12小时前
|
SQL 数据库 数据安全/隐私保护
SQL Server的安装步骤_kaic
SQL Server的安装步骤_kaic
|
10天前
|
SQL 存储 数据安全/隐私保护
|
12天前
|
SQL 存储
SQL Server基本函数
SQL Server基本函数
|
1月前
|
SQL 存储 数据库
【数据库SQL server】自学终极笔记
【数据库SQL server】自学终极笔记
76 0
|
1月前
|
SQL 算法 数据库
【数据库SQL server】关系数据库标准语言SQL之数据更新
【数据库SQL server】关系数据库标准语言SQL之数据更新
24 0