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:

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

EXEC sys.sp_configure N'show advanced options', N'1';

RECONFIGURE

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);

RECONFIGURE;

Configuration: sp_configure Settings

Leave a Reply Cancel reply