美创运维日记|SQL Server安装后，需立即修改的三个参数

导读：SQL Server的默认安装在很多方面的配置是错误的，为了获得更快的性能，在SQL Server安装完成后，有三个需要立即修改的参数配置：并行的开销阈值、最大服务器内存、最大并行度。

 

当大家用MSSQL的安装向导“下一步”、“下一步”安装了全新的SQL Server软件，最后你点击了完成安装按钮。

欧耶~~~ 大功告成！！！

现在我们可以把我们的服务器放入生产了！

 

-对不起，那并不是真的

-why？

-因为你的刚刚安装的SQL Server数据库的默认配置是错误的！！！

-Are you kidding me？？

 

是的，你没看错，小编向你说的就是：SQL Server的默认安装在很多方面的配置是错误的。

 

在今天的文章里，美创运维团队向你展示，为了更快的性能，在SQL Server安装完成后，有三个需要立即修改的参数配置。

 

那，我们开始吧！

 

 

01 并行开销阈--Cost Threshold for Parallelism

第一个你需要修改的配置选项是SQL Server处理并行开销的阈值。

 

首先，我们根据官方文档的定义来理解下并行开销阈值的含义。

cost threshold for parallelism 选项指定 SQL Server 创建和运行并行查询计划的阈值。仅当运行同一查询的串行计划的估计开销高于在“并行的开销阈值” 中设置的值时，SQL Server 才创建和运行该查询的并行计划。成本指的是在特定硬件配置中运行串行计划估计需要花费的成本，而不是时间单位。“并行的开销阈值” 选项可设置为0 到 32767 之间的任何值。默认值为 5。

 

并行意味着SQL Server能透过多个工作线程运行执行计划里的运算符。并行的目的是提高你查询的吞吐量。SQL Server里第1个影响并行的配置选项是所谓的并行开销阈值：

 

并行开销阈值

 

 

这里你配置的数字定义查询成本，查询优化器用它来找更便宜的并行执行计划。如果找到的并行计划更便宜，这个计划会被执行，不然串行计划会被执行。从刚才的图你可以看到，SQL Server默认配置使用5的成本阈值。当你的串行计划查询成本大于5，然后查询优化器再次运行查询优化来找更便宜并行执行计划的可能。

 

然而遗憾的是，5的成本值当下来说是个很小的数字。因此SQL Server会太快尝试并行你的执行计划。

 

而实际上呢，当你处理更大的查询时，并行才有意义——例如报表或数据仓库情形。在纯OLTP情形下，并行计划象征着糟糕的索引设计，因为当你有缺失索引时，SQL Server需要扫描你的整个聚集索引，因此你的查询成本越来越大，它们超过成本阈值，最后查询优化器经过判断给你并行计划。当人们看到并行计划出现时，总会担心数据库性能是不是有问题！但问题根源其实是缺失非聚集索引。

 

对于并行的成本阈值，我推荐至少20，甚至50。那样的话，你确保SQL Server只会对更大的查询进行并行。即使在你面前有个并行计划，你也应该考虑下是否可以通过增加一个支持的非聚集索引来使这个查询的成本更低。另外，CXPACKET（并行度）并不意味着在你的系统里你有并行问题！

 

 

 

02 最大服务器内存--Max Server Memory

 

现在在你面前你应该有个64位的SQL Server。64位意味着你可以理论上访问2^64的内存大小——那是10亿GB！因为这些巨量的内存，计算机供应商当前限制64位系统的地址总线“只有”48位——完全64位没有真正意义。用48位的地址空间，你可以访问256TB的内存——那还是大量的空间！！！

 

你可以使用最大服务器内存配置选项来配置SQL Server可以消耗的内存大小。下图显示的是在64位系统上SQL Server默认安装后的配置选项。

 

最大服务器内存

 

从刚才的图片你可以看到，SQL Server默认配置是可以消耗上至2147483647MB的内存。嗯，用48位的地址总线我们只能物理访问256TB的内存，现在SQL Server可以消耗上至20亿MB的内存？这里有什么东西不对……最大服务器内存设置比32位最大整形值还大——2147483647。不考虑别的因素消耗，但是因此SQL Server可以消耗比物理地址更多的内存？这是一个很不好的默认配置。SQL Server默认可以吃光你整个物理内存！

 

你总应该修改下这个配置选项，这样的话你可以给操作系统一些内存，让它可以活着喘口气吧？

 

一般来说（在服务器上没有其它程序/进程）你也应该给系统至少10%的物理内存。这就是说你需要调低最大服务器内存设置。例如，有64GB的物理内存我会配置最大服务器内存为56GB，这样的话操作系统可以用剩下的8G来消耗和工作。

 

03 最大并行度--Max Degree of Parallelism (MAXDOP)

 

当在SQL Server里一个执行计划进入并行，最大并行度定义了执行计划里每个并行运算符可用工作线程。下图显示了这个选项的默认配置。

 

最大并行度

 

 

​如你所见，SQL Server使用默认值0。这个值意味着SQL Server尝试并行化你的执行计划超过分配给SQL Server的所有CPU内核（即使默认情况所有内核都分配给SQL Server！）。你应该能看出这样的设置没有意义，尤其当你有大量CPU内核的系统。并行化本身带来负担，一旦你使用越多的工作线程，这个负担越大。

 

一个建议是设置最大并行度为在一个NUMA结点里拥有的内核数。因此在查询执行时，SQL Server会尝试在一个NUMA结点里保持并行计划，这也会提高性能。

 

有时你也会看到建议去设置最大并行度为1。这个是不好的建议，因为这个使你的“整个”SQL Server 单线程！即使维护操作（例如索引重建）已单线程执行，这会严重伤及性能！

 

 

小结

 

在你安装完SQL Server后，DBA的真正工作才开始：你需要配置你的SQL Server安装到你的硬件配置。在这篇文章里你已看到，SQL Server的默认配置是明显错误的。因此在安装后立即修改一些配置选项非常重要。我已经见过生产环境里SQL Server使用我这里提到的默认选项，因为它们“稍后“会被配置，“稍后”就从未发生了……

 

美创运维中心数据库服务团队拥有Oracle ACE 1人、OCM 10余人、数十名Oracle OCP、MySQL OCP、红帽RHCA、中间件weblogic、tuxedo认证、达梦工程师 ，著有《Oracle DBA实战攻略》，《Oracle数据库性能优化方法和最佳实践》，《Oracle内核技术揭秘》等多本数据运维优化书籍。目前运维各类数据库合计2000余套，精通Oracle、MySQL、SQLServer、DB2、PostgreSQL、达梦等主流商业和开源数据库。并成为首批国内达梦战略合作伙伴之一，拥有海量经验和完善的人员培养体系。并同时提供超融合，私有云整体解决方案。