原 MSSQL新建实例后需要做哪些基本优化操作
Tags： 原创MSSQLSQL Server优化小麦苗常用新建实例基本优化配置
👉 本文共约634个字，系统预计阅读时间或需3分钟。
目录
OS配置
安装
1 |
参数配置
修改内存、CPU、并行阈值等
1、最大服务器内存--Max Server Memory
2、最大并行度--Max Degree of Parallelism (MAXDOP)
3、并行开销阈--Cost Threshold for Parallelism
使用SQL语句修改，也可以图形化界面直接操作：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO -- 配置最大内存为1024M sp_configure 'max server memory (MB)',1024 go -- CPU数无限制 sp_configure 'max degree of parallelism', 0; GO -- 串行开销阈值大于20才使用并行 sp_configure 'cost threshold for parallelism', 20; GO -- 使设置生效 RECONFIGURE WITH OVERRIDE; GO -- 查询 SELECT * FROM sys.configurations where name in ('max server memory (MB)','max degree of parallelism','cost threshold for parallelism') ORDER BY name ; |
参考：https://www.dbaup.com/mssqlanzhuanghouxuyaodiaozhengdejigecanshu.html
数据库层面的并发度
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | USE [db1] GO ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8; GO SELECT configuration_id, name, value, value_for_secondary FROM sys.database_scoped_configurations WHERE name = 'MAXDOP'; SELECT name, compatibility_level,'USE '+d.name + char(10)+ ' GO '+ char(10) + 'ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 8;' FROM sys.databases d where d.name not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') ; |
修改兼容性级别
1 2 3 4 5 6 7 8 | SELECT name, compatibility_level, 'ALTER DATABASE '+name+' SET COMPATIBILITY_LEVEL = ' + (select convert(varchar,compatibility_level) from sys.databases b where b.name='master') +';' sql1 FROM sys.databases d where d.name not in('master','tempdb','model','msdb','ReportServer','ReportServerTempDB') and compatibility_level!=(select compatibility_level from sys.databases b where b.name='master'); |
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
