【0】查看服务器信息相关DMV
【0.1】限制和局限
- 使用 sp_configure 时,必须在设置配置选项之后运行 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE。 RECONFIGURE WITH OVERRIDE 语句通常专门用来设置那些使用起来应当十分小心的配置选项。 但是,RECONFIGURE WITH OVERRIDE 可用于所有的配置选项,并且可以用它代替 RECONFIGURE。
备注
RECONFIGURE 在事务内部执行。 如果任意重新配置选项失败,则所有重新配置操作都将失效。 - 有些属性页会显示通过 Windows Management Instrumentation (WMI) 获得的信息。 若要显示这些页,WMI 必须安装在运行 SQL Server Management Studio的计算机上。
【0.2】Security权限
有关详细信息,请参阅 服务器级别角色。
默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。
若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。
ALTER SETTINGS 权限由 sysadmin 和 serveradmin
【0.3】使用T-SQL操作查看修改服务器信息
--【1】查看
--(1)SERVERPROPERTY
SELECT CONVERT( sysname, SERVERPROPERTY('servername'));
GO
参考:https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver15
中文:https://docs.microsoft.com/zh-cn/sql/t-sql/functions/serverproperty-transact-sql?view=sql-server-ver15
--(2)sys.servers 目录视图查
USE AdventureWorks2012;
GO
SELECT name, server_id, provider FROM sys.servers ;
GO
--(3) sys.configurations 目录视图
USE AdventureWorks2012;
GO
SELECT name, description, is_advanced FROM sys.configurations ;
GO
--【2】使用 sp_configure 修改
Use AdventureWorks2012;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'fill factor', 100;
GO RECONFIGURE;
GO
--【3】查看服务状态(引擎、代理、全文索引)
select * from sys.dm_server_services;
参考官网:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-dynamic-management-views/sys-dm-server-services-transact-sql?view=sql-server-ver15
--【4】查看sql server相关注册表
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N'%ControlSet%';
通过使用 SERVERPROPERTY 内置函数查看服务器属性
您可以使用 SQL Server 或 sp_configure 系统存储过程通过配置选项来管理和优化 SQL Server Management Studio 资源。
大多数常用的服务器配置选项可以通过 SQL Server Management Studio来使用;而所有配置选项都可通过 sp_configure 来访问。
在设置这些选项之前应该认真考虑这些选项对系统的影响。 有关详细信息,请参阅查看或更改服务器属性 (SQL Server)。
重要说明!! 高级选项只能由有经验的数据库管理员或认证的 SQL Server 技术人员更改。
【1】配置选项的分类
配置选项在下面两种情况下生效:
- 在设置选项并发出 RECONFIGURE (在某些情况下为 RECONFIGURE WITH OVERRIDE)语句之后立即生效。 重新配置某些选项可使计划缓存中的计划失效,并编译新计划。 有关详细信息,请参阅 DBCC FREEPROCCACHE (Transact-SQL)。
-或- - 执行以上操作并重新启动 SQL Server实例之后生效。
需要重新启动 SQL Server 的选项最初只在 value 列中显示更改后的值。 在重新启动后,新值将出现在 value 列和 value_in_use 列中。
但有些选项需要在重新启动服务器后,新的配置值才能生效。 如果设置了新值并在没有重新启动服务器的情况下运行 sp_configure,则新值将出现在配置选项的“value”列中,而不是出现在“value_in_use”列中。 重新启动服务器之后,新值出现在“value_in_use”列中。
自配置选项是指 SQL Server 根据系统需要进行调整的选项。 大多数情况下,这使您无需手动设置值。 例如,“最大工作线程数” 选项和“用户连接数”选项。
【2】使用与配置选项表
【2.1】修改配置选项实操
--案例,开启agent xps
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
【2.2】配置选项表
下表列出了所有可用的配置选项、可能的设置范围及其默认值。 配置选项按以下字母代码标记:
- A = 高级选项,仅应由有经验的数据库管理员或认证的 SQL Server 专业人员更改,并且需要将“显示高级选项”设置为 1。
- RR = 需要重新启动 数据库引擎的选项。
- RP = 需要重启 PolyBase 引擎的选项。
- SC = 自配置选项。表 1
配置选项 | 最小值 | 最大值 | 默认 |
访问检查缓存桶计数(A) | 0 | 16384 | 0 |
访问检查缓存配额 (A) | 0 | 2147483647 | 0 |
即席分布式查询 (A) | 0 | 1 | 0 |
affinity I/O mask(A,RR) | -2147483648 | 2147483647 | 0 |
affinity64 I/O mask(A,仅适用于 64 位版本的 SQL Server) | -2147483648 | 2147483647 | 0 |
affinity mask (A) | -2147483648 | 2147483647 | 0 |
affinity64 mask (A,RR),仅适用于 64 位版本的 SQL Server | -2147483648 | 2147483647 | 0 |
代理 XP (A) | 0 | 1 | 0 (当 SQL Server 代理启动时,更改为 1。 如果 SQL Server代理设置为在安装过程中自动启动,则默认值为 0。) |
允许更新 (已过时。请勿使用。 将在重新配置期间导致错误。) | 0 | 1 | 0 |
0 | 1 | 0 | |
0 | 1 | 0 | |
0 | 1 | 0 | |
0 | 86400 | 0 | |
c2 审核模式 (A,RR) | 0 | 1 | 0 |
0 | 1 | 0 | |
clr 严格安全性 (A) 适用于:SQL Server(SQL Server 2017 (14.x) 及更高版本)。 | 0 | 1 | 0 |
0 | 1 | 0 | |
0 | 1 | 0 | |
并行的开销阈值 (A) | 0 | 32767 | 5 |
0 | 1 | 0 | |
cursor threshold (A) | -1 | 2147483647 | -1 |
0 | 1 | 0 | |
0 | 2147483647 | 2052 | |
0 | 9999 | 0 | |
0 | 1 | 1 | |
0 | 1 | 0 | |
0 | 1 | 0 | |
外部脚本已启用 (SC) **需要重新启动 **:SQL Server(SQL Server 2016 (13.x) 和 SQL Server 2019 (15.x))。 | 0 | 1 | 0 |
0 | 2 | 0 | |
填充因子 (A,RR) | 0 | 100 | 0 |
ft crawl bandwidth (max),请参阅 ft crawl bandwidth(A) | 0 | 32767 | 100 |
ft crawl bandwidth (min),请参阅 ft crawl bandwidth(A) | 0 | 32767 | 0 |
ft notify bandwidth (max),请参阅 ft notify bandwidth(A) | 0 | 32767 | 100 |
ft notify bandwidth (min),请参阅 ft notify bandwidth(A) | 0 | 32767 | 0 |
Hadoop 连接 (RP) 适用于:SQL Server(SQL Server 2016 (13.x) 及更高版本)。 | 0 | 7 | 0 |
index create memory(A,SC) | 704 | 2147483647 | 0 |
0 | 2 | 0 | |
lightweight pooling(A,RR) | 0 | 1 | 0 |
locks (A,RR,SC) | 5000 | 2147483647 | 0 |
0 | 32767 | 0 | |
0 | 256 | 4 | |
max server memory(A,SC) | 16 | 2147483647 | 2147483647 |
0 | 2147483647 | 65536 | |
128 | 32767 (对于 32 位 SQL Server,建议最大为 1024;对于 64 位 SQL Server,建议最大为 2048。) 注意: 是 32 位操作系统支持的最新版本SQL Server 2014 (12.x)。 | 0 归零操作会根据处理器的数量自动配置最大工作线程数,可以使用公式(256 + (处理器数> -4) * 8)来计算 32 位 < 的线程数,使用公式(512 + ( 处理器数> -4) * 8)来计算 64 位 SQL Server 的线程数 < SQL Server。 注意: 是 32 位操作系统支持的最新版本SQL Server 2014 (12.x)。 | |
media retention (A,RR) | 0 | 365 | 0 |
512 | 2147483647 | 1024 | |
min server memory(A,SC) | 0 | 2147483647 | 0 |
0 | 1 | 1 | |
512 | 32767 | 4096 | |
0 | 1 | 0 | |
open objects (A,RR,已过时) | 0 | 2147483647 | 0 |
针对即席工作负荷进行优化 (A) | 0 | 1 | 0 |
PH_timeout (A) | 1 | 3600 | 60 |
已启用 polybase (RR) 适用对象: SQL Server 2019 (15.x) | 0 | 1 | 0 |
precompute rank (A) | 0 | 1 | 0 |
priority boost (A,RR) | 0 | 1 | 0 |
0 | 2147483647 | 0 | |
查询等待 (A) | -1 | 2147483647 | -1 |
恢复间隔 (A,SC) | 0 | 32767 | 0 |
远程访问 (RR) | 0 | 1 | 1 |
0 | 1 | 0 | |
0 | 1 | 0 | |
0 | 2147483647 | 10 | |
0 | 1 | 0 | |
0 | 2147483647 | 600 | |
0 | 1 | 0 | |
scan for startup procs(A,RR) | 0 | 1 | 0 |
0 | 1 | 1 | |
set working set size(A,RR,已过时) | 0 | 1 | 0 |
0 | 1 | 0 | |
SMO 和 DMO XP (A) | 0 | 1 | 1 |
0 | 1 | 0 | |
两位数年份截止 (A) | 1753 | 9999 | 2049 |
user connections(A,RR,SC) | 0 | 32767 | 0 |
0 | 32767 | 0 | |
xp_cmdshell (A) | 0 | 1 | 0 |
另请参阅
sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL)DBCC FREEPROCCACHE (Transact-SQL)
参考文档: