Test first; Mileage may vary;
2May 14, 2015 by Kenneth Fisher
I went to a SQL Saturday recently and saw a number of great sessions. If you haven’t been to a SQL Saturday before I highly recommend it. It’s a day of free training and networking put on by Pass. The training is done by members of the community who are volunteering their time to share their knowledge.
Of the sessions I want to this particular weekend a number of them were performance tuning tips and I noticed one very common suggestion. In fact if you read blogs by experienced DBAs you will see the same thing over and over again. So what is this amazing tip that everyone seems to agree on?
Test first
Here’s the thing. If a setting worked best one way or the other every time then it wouldn’t be a setting would it? So before you make any changes, make sure you test first. The wider the change (instance setting vs query hint for example) the more careful you need to be. A lot of setting changes will only help occasionally or in fringe situations. Frequently you will find that the change you made that dramatically sped up one query dramatically slowed down multiple others.
You will occasionally find a setting that everyone pretty much agrees on. For example the setting cost threshold for parallelism. Almost everyone feels that it should be increased. Well, to be honest, everyone I’ve talked to thinks it should be increased. But you never know, there might be someone who disagrees. But now ask what it should be increased to. If you do some searching you can find a wide range of numbers and a lot of suggestions to, you guessed it, do some testing.
SQL Studies 
This is so important. If you don’t have the data to back up your decisions it’s hard to be sure you made the right one. That’s why we have execution plans and other tools to capture what we’re doing.
I experienced this recently with my Tableau instance. I had reviewed “best practices” but I still couldn’t get my dashboards to perform like I wanted them to so I thought about it and then made the changes that made the most logical sense. The performance went from a render time of 1 minute down to 4 seconds. It was fantastic to check the admin panel and be able to show that the change was real. Then I was able to put the next version upgrade on the back burner because there’s no expected benefit to the upgrade.
I love reviewing the metrics after projects and being able to definitively say what change has really occurred.
And of course (something I don’t think I mentioned in the post) none of that is possible unless you have a baseline. You need to measure before and after to be sure you’ve gotten any improvement.