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.