SQL Server On Linux（18）—— SQL Server On Linux性能（4）——针对性能的配置（实例层面）

内容简介：本人新书上市，请多多关照：配置很多时候直接影响了后续的稳定性和性能，因此很有必要在初期就应该做必要的配置，同时随着使用的实际情况进行调整。本文以性能为出发点讲解一下关于性能配置方面的问题。

本人新书上市，请多多关照：《SQL Server On Linux运维实战 2017版从入门到精通》

配置很多时候直接影响了后续的稳定性和性能，因此很有必要在初期就应该做必要的配置，同时随着使用的实际情况进行调整。本文以性能为出发点讲解一下关于性能配置方面的问题。

从配置来说，Linux上的SQL Server通常使用mssql-conf（Linux命令）、T-SQL命令中的sp_configure和ALTER SERVER CONFIGURATION，通常使用SSMS客户端来实现。但是要提醒一下，配置通常是全局的，所以影响面非常大，如有可能必须测试和监控。

接下来的三篇文章将从实例层面、数据库层面和 Linux 层面进行介绍。

SQL Server实例配置

实例层面针对性能的配置通常包括内存、并行执行、处理器掩码、跟踪、线程、plan cache和tempdb及用户数据库文件的部署几方面。通常这一层的配置都需要重启SQL Server服务，也建议通过重启来检查效果。

在Linux层，SQL Server默认只分配OS识别的80%的物理内存，但是对于超大内存的系统比如TB以上，显然会有点浪费。这个时候可以使用mssql-conf配置memorylimitmb选项。但是也别设置得太厉害，否则会出现oom killer（内存溢出），可以参考一下本人另外一篇文章：如何保护SQL Server免受Linux上的OOM-killer杀掉。

在SQL Server内部，也有自己的内存限制配置选项，使用sp_configure来管理，通常只需要设置max server memory这个值。默认值为0，也就是80% 物理内存。随着整套系统的运行，SQL Server会增长使用率直到这个上限，不管是Windows还是Linux平台，这个SQL Server层的值都是有必要设置的。

使用下面命令把Linux层面的内存限制打开并设置适当的值。

sudo /opt/mssql/bin/mssql-conf set memory.memorylimitmb 1024
sudo systemctl restart mssql-server

使用下面命令把SQL Server层面的内存限制打开并设置适当的值。

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'max server memory', 4096;  
GO  
RECONFIGURE;  
GO

使用下面命令查询当前内存分配情况。

SELECT 
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, 
    large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, 
    locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
    virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, 
    virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, 
    virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
    page_fault_count AS sql_page_fault_count,
    memory_utilization_percentage AS sql_memory_utilization_percentage, 
    process_physical_memory_low AS sql_process_physical_memory_low, 
    process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

并行执行

在多CPU环境下，达到一定的条件，SQL Server就会把任务拆分成多个子任务来一起执行，这种情况叫并行执行。由于行为和数据量的不同，对于OLTP系统来说，并行并不是好的选择，但是对于OLAP/DW类的系统而言，却可以增加效率和资源利用。在实例层面，这个行为由两个配置项来控制：max degree of parallelism和cost threshold for parallelism：

max degree of parallelism：用于控制并行任务的最大数量。主要针对索引重建、DBCC CHECKDB、并行插入、联机列修改、统计信息更新等。默认为0，即由SQL Server根据可用的CPU数量自己控制。但是长期以来，到底是否需要保持默认配置一直都没有一个定论。因为这个数量个实际负载有非常大的关系。根据本人工作经历，刚入职时一个系统因为没有控制这个数量，加上本身语句、索引的不合理，导致一个操作就把所有CPU占满，整个系统经常完全卡住。在优化语句、索引及调整最大并行度之后，CPU几乎没有出现超过40%的情况。另外对于某些国外专家的经验，建议对8个CPU以上的系统才修改，这里有一篇更新到 SQL 2012为止的建议：建议和准则在 SQL Server 中的"最大并行度"配置选项。修改命令如下：

EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO  
EXEC sp_configure 'max degree of parallelism', 8;  
GO  
RECONFIGURE WITH OVERRIDE;  
GO

cost threshold for parallelism：前面一个是设置“用不用”的问题，如果改成1则为不用，而这个配置是设置“什么时候用”，很显然，只有max degree of parallelism设置不为1时或因为SQL Server识别出不少于一个逻辑CPU时，cost threshold for parallelism才有意义。默认值为5。但是这个值是在SQL Server 7.0时代经过测试得出的，明显已经不是非常适合今时今日的情况。很多国外专家建议从25~30作为起点，本人在实际工作中通常使用25作为起点，暂时没有非常明显的异常。使用下面语句检查物理CPU和逻辑CPU个数：

SELECT (cpu_count / hyperthread_ratio) AS PhysicalCPUs,   
cpu_count AS logicalCPUs   
FROM sys.dm_os_sys_info

修改该配置的命令如下：

EXEC sp_configure 'show advanced options', 1 ;  
GO  
RECONFIGURE  
GO  
EXEC sp_configure 'cost threshold for parallelism', 10 ;  
GO  
RECONFIGURE  
GO

处理器掩码

前文提到，SQL Server自动从NUMA架构中获益，但是可能在某些情况或者应用中，需要绑定特定的NUMA节点或者CPU，比如非专用服务器上，为了确保其他进程也能得到CPU响应。这个时候就需要通过ALTER SERVER CONFIGURATION这个T-SQL命令来修改，注意这个配置只能使用ALTER SERVER CONFIGURATION来修改。我们可以使用类似以下命令，在8个CPU的Linux服务器上，只允许SQL Server使用4~7的CPU（CPU从0开始算起，即最后4个CPU）：

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = 4 TO 7
GO

如果想让SQL Server使用所有可用的CPU，可以使用下面语句实现：

ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU = AUTO
GO

为了获取最大性能，建议针对所有NUMA都要设置最少一个CPU关联。或者由SQL Server自动配置（即设置为AUTO）。

虽然跟踪（SQL Trace）今时今日已经不再是什么推荐功能，但是其最重要的一个“default trace”依旧是很多系统的救命稻草，毕竟不是每个系统都有完整的监控体系。default trace会自动配置和跟踪一些基础信息，当初刚接手一个系统的时候，也是通过检查default trace发现数据库的LDF文件设置太小频繁增长导致频繁卡顿（trace文件记录了增长的时间点，通过与卡顿时间对比，发现重叠，然后进行LDF文件配置的检查，果然发现配置为1MB/次，修改成128MB/次之后，现象解除）。不过如果发现default trace已经明显影响到I/O性能，那也可以进行关闭，使用sp_configure 配置default trace enabled选项即可。后期可以配置扩展事件来实现同样的功能。

线程（threads）用来服务如登录、查询等任务。为了避免无限制的线程导致资源，SQL Server会设置线程池的最大数量。默认情况下SQL Server会使用在Linux上能侦测到的CPU数量，可以从sys.dm_os_sys_info中的max_workers_count列中获取实际数量。在优化不足的高并发系统中，可能因为线程被占用过久，导致其他任务无法得到CPU资源从而形成等待，最终影响性能。如果一开始配置的数量不多，但是又出现了相关等待，可以使用sp_configure 修改max worker threads配置来提高数量。但是这个配置需要重启SQL Server。具体建议可以参阅官方文档：配置 max worker threads 服务器配置选项

Plan cache

plna cache的目的是减少同样的语句每次都进行编译并生成执行计划的开销。但是很多系统，会存在偶发性的查询，也就是说通常只会执行一次，这种查询如果都生成执行计划并缓存，那很容易出现内存占用，比如在troubleshooting时，经常会select count(1) from 表 where xxx这类执行，看看是否存在与另外一个系统（特别是数据仓库同步过程）相同数量的数据。这种实际上是没有必要缓存执行计划的。这个时候，optimize for ad hoc workloads配置就能起到作用。使用sp_configure来开启之后，会在第二次执行相同sql_handle的语句才会开始缓存。这就减少了很多即席查询带来的内存压力。当然如果你的服务器内存非常充足，也可以不开启，毕竟这个选项是针对所有ad hoc的，也就是说那些后续需要频繁执行的语句只能在第三次执行的时候才能得到提升。

TempDB 及用户数据库文件

TempDB是实例级别共享的数据库，SQL Server用户应该很熟悉。也或多或少听过TempDB经常会出现性能问题。即使你不明确使用临时表/表变量，一些大数据量关联、order by都会借助TempDB来实现。虽然SQL 2017开始的Windows 版在安装过程就会提醒你可以配置多个数据文件（注意配置日志文件是没有性能提升的），但是Linux版是没有这个选择的。所以在Linux版本中，需要后续配置。国外专家在github中提供了一个脚本来实现TempDB文件拆分，可访问 TempDB文件拆分（SQL Server on Linux）。而至于拆分多少个及每个多大，可以参考下面的两篇文章：