sqlserver配置实践

对于一套新的sqlserver服务器，我们首先要对它做一些必要的优化配置，确保在生产上比较长的时间段内可以比较稳定的，良好的运行。

新的sqlserver服务器上安装的sqlserver版本，可以选择sqlserver2014 企业版本，这个版本相对稳定了，相对也比较新的版本。

sqlserver配置优化：

1、最小，最大内存配置

最小内存一般设置1~2G，满足最小运行的状态的配置。

最大内存，这个一定要设置，一般如果是独立的一个数据库服务器，规划操作系统的内存使用资源，其他全部给到sqlserver，这个如果不

设置默认是服务器的最大内存，如果出现一个峰值，正好突破服务器的最大内存，那么数据库服务器有宕机的可能性。

2、内存锁定页

关于内存锁定页的作用和配置的步骤，我们直接可以参考下官方文档：

启动内存锁定页

如果sqlserver服务器是一个独立的数据库服务器，这个配置不设置也没问题

3、配置“对即时负载的优化”

SP_Configure 'show advanced options', 1

Reconfigure

GO

EXEC sp_configure 'optimize for ad hoc workloads',1

Reconfigure

 当新查询执行时，query_hash值会在内存中生成，而不是整个执行计划，当相同的查询第二次执行的时候，SQLServer会查找是否已经存在这个query_hash，如果不存在，执行计划将保存在缓存中。这样就使得仅执行一次的查询将不会保存执行计划到缓存中。所以强烈建议打开这个配置。这个配置不造成任何负面影响，但是可以节省计划缓存的空间

 

4、数据文件，日志文件，tempdb文件分离

一般数据库服务器有多个物理磁盘，可以将数据文件，日志文件，tempdb文件分散到不同的磁盘上去，从而增加I/O能力。

特别对于tempdb文件，尽量放在性能比较高的磁盘上

 

5、索引文件，数据文件分离

默认索引文件，数据文件是放在同一个数据文件组的，可以分成不同的文件组，从而实现索引文件与数据文件分离，索引文件可以放在

性能比较高的磁盘上，这样对查询效率会有比较好的提高。这个根据实际情况来做，因为这个需要把原有的索引全部重新建立一遍。

 

6、日志文件

 日志文件的管理，大家可以看下官方文档 管理事务日志的大小 

 日志文件要根据实际的生产上的增长情况，设定初始大小和增长的大小，如果是数据库迁移，可以根据正在运行的生产环境的情况，

 来设定日志文件的初始大小，增加的方式与大小

 可以根据 建议脚本 来设定。

 

7、tempdb文件

tmpdb是比较重要的，临时表，建立索引，数据库运行中的中间变量都会用到tempdb

tempdb的文件建立个数，要按照实际的生产环境情况来定，下面有个参照表：

基于 DTU 的服务层的 tempdb 大小

SLO	最大 tempdb 数据文件大小 (MB)	tempdb 数据文件数	最大 tempdb 数据大小 (MB)
“基本”	14,225	@shouldalert	14,225
S0	14,225	@shouldalert	14,225
S1	14,225	@shouldalert	14,225
S2	14,225	@shouldalert	14,225
S3	32,768	@shouldalert	32,768
S4	32,768	2	65,536
S6	32,768	3	98,304
S7	32,768	6	196,608
S9	32,768	12	393,216
S12	32,768	12	393,216
P1	32,768	12	393,216
P2	32,768	12	393,216
P4	32,768	12	393,216
P6	32,768	12	393,216
P11	32,768	12	393,216
P15	32,768	12	393,216
高级弹性池（所有 DTU 配置）	14,225	12	170,700
标准弹性池（所有 DTU 配置）	14,225	12	170,700
基本弹性池（所有 DTU 配置）	14,225	12	170,700

 

   DTU是一个资源度量单位，详细可以参考 这个文章 DTU和eTUD

   一般可以先尝试建8个对应的tempdb的文件。

   下面的脚本可以查询当前tempdb的大小和增长参数，迁移数据的时候，可以根据下面的脚本查询，来设定

   新的数据库的tempdb的初始大小，和增长的大小

        SELECTnameAS FileName,

size*1.0/128AS FileSizeinMB, CASE max_size WHEN0THEN'Autogrowth is off.'WHEN-1THEN'Autogrowth is on.'ELSE'Log file grows to a maximum size of 2 TB.'END, growth AS'GrowthValue', 'GrowthIncrement' = CASEWHEN growth = 0THEN'Size is fixed.'WHEN growth > 0AND is_percent_growth = 0THEN'Growth value is in 8-KB pages.'ELSE'Growth value is a percentage.'ENDFROM tempdb.sys.database_files; GO

 

8、建立自动化维护job

可以建立一些日常的自动化维护job，比如自动清理索引碎片，自动更新索引统计信息等等

这些可以参考一些其他sqlserver管理方面的资料，不再详细叙述.

 

9、其他

sqlserver里面还有很多其他一些配置项，比如cpu的使用个数，并行度设置，索引创建时的内存等等。

可以通过查询sys.configurations 

查询所有的sqlserver的配置项，一般这些配置项可以先按默认值，根据生产中的实际情况再调整.