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.

Filed under: Microsoft SQL Server, Pass, Performance, Problem Resolution, SQLServerPedia Syndication Tagged: microsoft sql server, Pass, Performance, problem resolution, Sql Saturday, testing