sql server best practice

本文最后更新于：2024年8月9日晚上

CPU最佳实践。

根据CPU架构和核数配置MaxDop.
https://learn.microsoft.com/zh-CN/sql/database-engine/configure-windows/configure-the-max-degree-of-parallelism-server-configuration-option?view=sql-server-ver16

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

内存最佳实践。

Sqlserver的内存尽量占用至主机的85%~80%之间。

sp_configure 'show advanced options',1      ----打开高级配置选项
go
reconfigure                                 ----确认上述配置
go
sp_configure 'max server memory (MB)',400000 ----例如，配置‘max Server memory’内存为400000 MB
go
reconfigure                                 ----确认上述配置
go

日常管理相关建议。

日志文件和数据文件尽量分别放置在不同的磁盘上有利于磁盘带宽的分配。
对于数据库文件较大的数据库，如果空间使用率很高。对于这方面的优化建议定期检查文件大小及空间使用率预先手动给数据文件分配空间，比如与增长20%的sugg。或是直接将文件增长设为500MB。
ALTER DATABASE db2 MODIFY FILE (NAME = db2,SIZE =MB)
对与数据库的日志文件过大，不能截断。建议定期backup log，使日志文件大小保持恒定。

对于数据库中有比较多的表有较大的碎片率，建议定期整理。

--使用‘文本方式运行结果集’模式生成批量运行的语句，并结果拷贝至SSMS中运行。
SELECT 'ALTER INDEX '+b.name +' ON '+schema_name(o.schema_id)+'.'+o.name+' REBUILD'+char(13)+'Go'
FROM sys.dm_db_index_physical_stats (DB_ID(), null, NULL, NULL, NULL) AS a  
JOIN sys.indexes AS b 
ON a.object_id = b.object_id AND a.index_id = b.index_id
join sys.objects as o 
on o.object_id=a.object_id
where avg_fragmentation_in_percent>30 and b.name is not null

Tempdb相关建议。

将tempdb放置在非常快速的磁盘上。
每一个资源消耗较大的语句都很有可能会使用tempdb。Tempdb的性能对全局性能至关重要。
给tempdb数据库足够数量的文件数量。由于CPU在使用tempdb是以文件为单位的，因此如果比较繁忙时有可能产生有多个CPU核等在同一个文件上产生争用。
通常情况下，我们可以给tempdb 8个数据文件。可以使tempdb文件数量和单一NUMA节点内的CPU核数相同。甚至可以使tempdb文件数量和CPU的核数相同。
给tempdb数据库足够的大小尽量避免语句在使用时产生文件的扩张导致执行时间变长。

备份相关建议。

基本建议

方案一：每天一次全库备份，每个小时一次日志备份。
方案二：每周一次全库备份，每天一次增量备份，每个小时一次日志备份。

备份策略设计原则：

根据您的最大允许丢失数据时间。如上述方案中，最大允许丢失数据时间为1h。如果想将此时间提升到15min，则您需要每15min进行一次日志备份。
备份文件的大小。如果每天一次全备则需要占用更大的空间，因此可以考虑每周一次全备，每天一次增量备份。方案一每天产生的大小约为全备+1天的日志变化量。方案二一周需要消耗的空间为一个全备+7天的数据变化量+7天的日志文件。方案一将消耗更多空间。
恢复时间。方案二将会消耗更长的时间。

锁相关建议。

禁用锁升级的trace flag 1211来降低deadlock或阻塞的概率。建议只有在发现有明确非常多deadlock或是阻塞的情况下可以使用。
https://www.microsoftpressstore.com/articles/article.aspx?p=2233327&seqNum=5
使用snapshot的隔离级别来降低读写之间的争用。

其他建议。

对于从2008大版本升级至2016以上版本，如果在2008上SQL server状态运行良好，可以考虑暂时禁用new_ce可能带来的语句性能下降。
https://blogs.technet.microsoft.com/dataplatform/2017/03/22/sql-server-2016-new-features-to-deal-with-the-new-ce/

在语句级别禁用new_CE.
使用hint 'FORCE_LEGACY_CARDINALITY_ESTIMATION'
在数据库级别禁用new_CE. 如下两种方式。
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION=ON;
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110;
在实例级别禁用new_CE。使用trace flag 9481.