If you're a SQL Server DBA, then you know sp_configure. If not, well... it's time to learn. sp_configure is a system stored procedure which allows you to view or change SQL Server configuration settings. Min/max memory, backup compression, fill factor (%), etc. There are many different opinions on which configurations should be changed to what, but that's not what this post is about. I just want to show you how to quickly list all non-default SQL Server configuration settings. Why? Well, maybe you just inherited a server, or maybe you're troubleshooting a problem and want to see if any configurations could be contributing -- or any that could help! This returns ONLY the non-defaults. Much easier than running 'EXEC sp_configure' and then going through all configurations...
/* Query SQL Server for any non-default configuration settings. */
SET NOCOUNT ON;
-- declarations
DECLARE
@version VARCHAR(128
),
@charindex BIGINT,
@majversion VARCHAR(MAX),
@temp SQL_VARIANT;
-- set local variables
SELECT @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(128
));
SELECT @charindex = CHARINDEX('.', @version);
SET @majversion = SUBSTRING(@version, 1
, @charindex-1
);
IF @majversion IN (9
,10)
SET @temp = 20
ELSE SET @temp = 10
-- load all defaults into table variable
DECLARE @defaults TABLE (
id int IDENTITY(1
,1
),
config VARCHAR(128
),
value SQL_VARIANT
);
INSERT @defaults VALUES
('access check cache bucket count', 0
),
('access check cache quota', 0
),
('Ad Hoc Distributed Queries', 0
),
('affinity I/O mask', 0
),
('affinity64 I/O mask', 0
),
('affinity mask', 0
),
('affinity64 mask', 0
),
('Agent XPs',0
),
('allow updates', 0
),
('backup compression default', 0
),
('blocked process threshold', 0
),
('c2 audit mode', 0
),
('clr enabled', 0
),
('common criteria compliance enabled', 0
),
('contained database authentication', 0
),
('cost threshold for parallelism', 5
),
('cross db ownership chaining', 0
),
('cursor threshold', -1
),
('Database Mail XPs', 0
),
('default full-text language', 1033
),
('default language', 0
),
('default trace enabled', 1
),
('disallow results from triggers', 0
),
('EKM provider enabled', 0
),
('filestream_access_level', 0
),
('fill factor(%)', 0
),
('ft crawl bandwidth (max)', 100
),
('ft crawl bandwidth (min)', 0
),
('ft notify bandwidth (max)', 100
),
('ft notify bandwidth (min)', 0
),
('index create memory(KB)', 0),
('in-doubt xact resolution', 0),
('lightweight pooling', 0),
('locks', 0),
('max degree of parallelism', 0),
('max full-text crawl range', 4),
('max server memory(MB)', 2147483647),
('max text repl size(B)', 65536
),
('max worker threads', 0
),
('media retention', 0
),
('min memory per query(KB)', 1024
),
('min server memory(MB)', 0
),
('nested triggers', 1
),
('network packet size(B)', 4096
),
('Ole Automation Procedures', 0
),
('open objects', 0
),
('optimize for ad hoc workloads', 0
),
('PH timeout(s)', 60
),
('precompute rank', 0
),
('priority boost', 0
),
('query governor cost limit', 0
),
('query wait(s)', -1
),
('recovery interval(min)', 0
),
('remote access', 1
),
('remote admin connections', 0
),
('remote login timeout(s)', @temp),
('remote proc trans', 0
),
('remote query timeout(s)', 600
),
('Replication XPs', 0
),
('scan for startup procs', 0
),
('server trigger recursion', 1),
('set working set size', 0
),
('show advanced options', 0
),
('SMO and DMO XPs', 1
),
('transform noise words', 0
),
('two digit year cutoff', 2049
),
('user connections', 0
),
('user options', 0
),
('xp_cmdshell', 0
);
-- now pull back which ones you're using that aren't defaults
SELECT
s.name [Configuration],
s.value_in_use [Yours],
d.Value [Default]
FROM
@defaults d JOIN sys.configurations s
ON s.name LIKE '%' + d.config + '%'
AND d.Value <> s.value_in_use
WHERE
s.name <> 'show advanced options'
ORDER BY
s.name
These are the results from my own instance:
That's about it. Now there are many different opinions about which configs should be changed, and to what. I've got my changes that I make on every server I manage, and then there are other changes that I make, for different needs. Then, of course, there are countless other recommendations from the other SQL experts out there. These are two of my favorites:
www.brentozar.com - Five SQL Server Settings to Change
http://sqlblog.com - Did You Know? What settings to always change
I would also be sure to take a look at this one for more detail on sp_configure:
https://msdn.microsoft.com/en-us/library/ms188787.aspx