Stick with the defaults. In fact, as Andy Warren has told me many times, unless you know better and have a reason to not do so, always go with the defaults. I think that’s good advice, and it’s why I think that defaults in applications, especially SQL Server, should be set for the values that are appropriate 80% of the time.
I saw a post on memory management that got me thinking. Someone was asking if settings have changed in 2008 and I needed to look around a bit to see if that was the case. I hadn’t seen much on this lately, and I need to write a short piece on that.
However in looking around, I stumbled across this article from Technet on How to determine proper SQL Server configuration settings. As I read through it, there was a nice trend:
In actual practice, if you change the affinity mask setting from the default it only rarely helps performance, and will frequently degrade performance.
Based on actual production experience, you do not need to use Fiber mode except in very rare circumstances.
In SQL Server 2000 or in SQL Server 2005, you cannot change the max async IO configuration setting. SQL Server 2000 or SQL Server 2005 automatically tunes this setting.
If you configure a number of worker threads to a value that is greater than the default, it is almost always counterproductive and slows performance because of scheduling and resource overhead.
These quotes from the various sections of the article seem to show that you don’t want to mess with the tuning of these “knobs” very often in SQL Server. Near the beginning of this article has this quote:
If you do make a configuration change, you must perform rigorous methodical performance testing both before and after the change to assess the degree of improvement.
This sums things up nicely. Rigorous and methodical testing should be performed or you’ll likely be making things worse and potentially not realize it at that time. For the majority of people, you should stick with the defaults unless you know better.