Configuration: sp_configure Settings
For the most part, you can next-next-next your way through SQL Server’s setup and it’ll work well enough for most shops. However, with just a few tweaks, you can get a setup that’s faster and more reliable.
Here’s the script I discuss in the video:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
EXEC sys.sp_configure N'show advanced options', N'1'; GO RECONFIGURE GO EXEC sys.sp_configure N'max degree of parallelism', N'8'; EXEC sys.sp_configure N'backup checksum default', N'1'; /* 2014 & newer only */ EXEC sys.sp_configure N'cost threshold for parallelism', N'50'; EXEC sys.sp_configure N'remote admin connections', N'1'; EXEC sys.sp_configure N'backup compression default', N'1'; /* Set max server memory to 90% of server memory */ DECLARE @StringToExecute NVARCHAR(400); SELECT @StringToExecute = N'EXEC sys.sp_configure N''max server memory (MB)'', N''' + CAST(CAST(physical_memory_kb / 1024 * .9 AS INT) AS NVARCHAR(20)) + N''';' FROM sys.dm_os_sys_info; EXEC(@StringToExecute); GO RECONFIGURE; GO |