SQL Server Performance Blog

By Michael Campbell, March 14, 2011

Performance tuning tips come in many different shapes and sizes. Some represent technical ‘deep-dives’ into aspects of how SQL Server’s storage engine or query process tackles certain tasks – while others represent simple (or even complex) best practices for avoiding certain behaviors or problems. The key thing that all viable tuning tips share is that they’ll help you save time – and are usually brought to light by virtue of something someone has mistakenly understood, done, or tweaked in the past.

To that end, one of the very first things that I do when performance tuning a new server for my SQL Server consulting clients is establish which server configuration settings, options, knobs, levers and buttons may have been pushed, adjusted, tweaked, or cajoled.

And, the sad reality is that I take this approach to ‘base-lining configuration settings’ precisely because failure to do so in the past resulted in too many red-herrings. And, as anyone who’s familiar with performance tuning on something as complex as SQL Server knows: avoiding distractions or ‘false positives’ can help put you well ahead of the game.

Validating Settings:  sp_configure and sys.configurations

Most Database Engine level SQL Server configuration settings and options can be set using sp_configure as outlined in Books Online. Consequently, it’s possible to interrogate or query current values simply by firing off a ‘query’ against sp_configure – with no arguments specified, like so:

EXEC sys.sp_configure
GO

Only, a cleaner and easier way to query this same information is to use the sys.configurations catalog view – which provides roughly the same information:

SELECT * from master.sys.configurations
GO

Either way you slice it though, querying either source results in roughly 70 rows of data being pulled back – each row representing a different setting or option. So, unless your idea of a good time is memorizing the default values for each of these entries, trying to spot deviations from the default value can be a pain.

Which is why I’ve created a brute-force approach of my own that specifies the default values for all of these settings, then queries the existing settings, and then JOINs them against the defaults in order to show me any values that aren’t set to the default.

An example is listed below:

-- Server Configuration (find any non-standard settings)
--        for SQL Server 2008.
DECLARE @config_defaults TABLE (
    name nvarchar(35),
    default_value sql_variant
)

INSERT INTO @config_defaults (name, default_value) VALUES
('access check cache bucket count',0),
('access check cache quota',0),
('Ad Hoc Distributed Queries',0),
('affinity I/O mask',0),
('affinity mask',0),
('affinity64 I/O mask',0),
('affinity64 mask',0),
('Agent XPs',1),
('allow updates',0),
('awe enabled',0),
('backup compression default',0),
('blocked process threshold (s)',0),
('c2 audit mode',0),
('clr enabled',0),
('common criteria compliance enabled',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),
-- NOTE: SQL Server may change the min server
--   memory value 'in flight' in some environments
--    so it may commonly show up as being 'non default'
('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)',20),
('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),
('SQL Mail XPs',0),
('transform noise words',0),
('two digit year cutoff',2049),
('user connections',0),
('user options',0),
('xp_cmdshell',0)

SELECT c.name, value, value_in_use, d.default_value
from sys.configurations c
INNER JOIN @config_defaults d ON c.name = d.name
where
    c.value != c.value_in_use
    OR c.value_in_use != d.default_value
GO

Using this code I’m able to quickly query a server and spot any and all configuration changes that aren’t set to default – including ‘show advanced settings’, ‘priority boost’, ‘user options’, fill factor, memory considerations, and a host of other settings or configurations that may have been changed or modified.

Knowing which configuration settings have been changed – if any – lets me get a quick feel for the potential skill sets of people who’ve maintained or worked on the server before. It also lets me spot any big potential deviations from best practices or operational standards that would be fatal to miss (like lightweight pooling). And, while I could easily check for any ‘big’ considerations just by querying and manually scanning, this more automated approach results in less potential for error and takes considerably less time.

Consequently, I’d recommend taking this script for a test drive on your own servers – what you’ll find might surprise you.

About the Author:
Michael K Campbell (mike@overachiever.net) is a contributing editor for SQL Server Magazine and a consultant with years of SQL Server DBA and developer experience. He enjoys consulting, development, and creating free videos for www.sqlservervideos.com.