Non-Default sp_Configure Setting
Blitz Result: Non-Default sp_Configure Setting
“I wonder what this button does…”
This part of our SQL Server sp_Blitz script checks sys.configurations looking for settings that have been changed away from their default values.
This doesn’t necessarily indicate a problem, but to explain it, we like to talk about modern airplanes. They’re so advanced that they practically fly themselves. In fact, airplane cockpits have two things: a pilot and a dog. It’s the pilot’s job to feed the dog, and it’s the dog’s job to bite the pilot if he touches anything.
Unfortunately, SQL Server doesn’t come with a dog, so people end up poking around in all kinds of settings that they probably shouldn’t change. When in doubt, back it out.
To Fix the Problem
These settings may not be a problem, but you’ll want to know that they’re in place, and start asking questions about why they were changed.
1 Comment.
Hi Brent,
I believe CheckID 22 (Non-default config prameteres) of the blitz script needs to be corrected. There is a small typo (semicolon included with few of the parameters names making checks inaccurate for the below ones:
VALUES ( ‘fill factor (%);’, 0 ) ;
VALUES ( ‘ft crawl bandwidth (max);’, 100 ) ;
VALUES ( ‘ft crawl bandwidth (min);’, 0 ) ;
VALUES ( ‘ft notify bandwidth (max);’, 100 ) ;
VALUES ( ‘ft notify bandwidth (min);’, 0 ) ;
VALUES ( ‘index create memory (KB);’, 0 ) ;
VALUES ( ‘max server memory (MB);’, 2147483647 ) ;
VALUES ( ‘max text repl size (B);’, 65536 ) ;
VALUES ( ‘min memory per query (KB);’, 1024 ) ;
VALUES ( ‘min server memory (MB);’, 0 ) ;
VALUES ( ‘network packet size (B);’, 4096 ) ;
VALUES ( ‘PH timeout (s);’, 60 ) ;
VALUES ( ‘query wait (s);’, -1 ) ;
VALUES ( ‘recovery interval (min);’, 0 ) ;
VALUES ( ‘remote login timeout (s);’, 20 ) ;
VALUES ( ‘remote query timeout (s);’, 600 ) ;
Above are the parameters where param name is suffixed with an “extra” semicolon. E.g. max server memory (MB); should actually be max server memory (MB)
Just thought of bringing this into your notice. The script is great otherwise!