My favourite SQL meme of 2015
One of the things that frustrates any DBA is bad
advice, especially when it appears to 'fix' a problem when in actual fact it
could well be making things much worse.
Why you shouldn't blindly follow advice is covered in
my SQL’s 50 Shades of Grey Area's article but for now, here’s a couple of
examples of how problems can be made worse by applying a quick fix:
I’m seeing
lots of CXPACKETS waits, how do I fix these?
Bad Advice = Set the maximum degree of
parallelism to 1, no more CXPACKET waits!
I’m seeing
index fragmentation occur quite frequently on some of my indexes, what should I
do?
Bad Advice = Set fill factor on the server to
70, no more fragmentation problems!
I’m worried about TempDB contention. What should I do?
Bad Advice = Set the number of files to the
number of cores, no more contention issues!
Yes all of the above might look like they have magically made your system better. The bad news is, they haven't. To make matters worse, without taking the time to correctly diagnose (and understand) an issue you are putting your systems at risk.
Setting MAXDOP to 1 will stop CXPACKET waits, but were
they really a problem in the first place? Now with a setting of 1 any queries
that might benefit from parallelism can’t. That means anything with even a slight level of
complexity is going to be slower.
Blindly setting fill factor will leave space at
the page level when an index is rebuilt which will reduce fragmentation. That “room”
though will eat up space on disk and in memory, this can and will cause
performance problems.
So what about TempDB? Well, what if you have 32 cores?! I’ve seen this and sure enough there was no evidence of contention but the
performance impact of having so many files was quite evident.
Here's the point. Each of these cases could have been resolved correctly with a
bit of research.
So what about those CXPACKET waits? Well they are a natural product of parallelism and
often could be considered benign. You should though check your MAXDOP and cost threshold for parallelism settings to see if they are appropriate for your workload. Only in specific cases should a MAXDOP setting of 1 be used.
A high frequency of index fragmentation should never be
‘fixed’ by a server side setting of fill factor. Instead monitor any potentially troublesome indexes closely and decrease the individual fill factor (on the index) by 5% at a
time.
For TempDB you can read more here SQL 2016: Number of TempDB files again it’s a question of performing analysis to find the right balance between the number of files and
reducing PAGELATCH waits.
So one of the golden rules for a DBA is never blindly use a setting within SQL Server, especially when looking to fix a issue. Always look to understand the problem, what actually is it and why is it occurring?
Then take an evidence based approach to appropriately tune your instance.
If you are going to strive for as much automation as possible, then you are going to have to work with some arbitrary standards for all of these settings and more.
ReplyDeleteOften, it is a decision made by consensus of team members, but, as with doctors, our first intention should be to do no harm.
Very true, automation does require quick deployment, reuse of config scripts etc and I'm a big advocate of those.
DeleteIt does though become a problem when every system is "the same" when the workloads and throughput are vastly different.