This week I am highlighting the top items that I see on almost every SQL Server I run across that need to be changed in almost every instance.
Instance level settings
Many of you (most?) are familiar with instance level settings such as MAXDOP, Backup Compression and Max Server Memory…and you should be. Good for you!
Are you also familiar with ‘Cost Threshold for Parallelism’ and ‘Optimize for Ad Hoc Workloads’? I’m guessing not as much as the others.
The default settings on these are what most clients I see are running, and they are usually WRONG.
Cost Threshold for Parallelism – deals with the question ‘How ugly does this query have to be before I use more than one processor?’ The default is ‘5’, but for most servers today it should be 25 or even 50 to get the best balance of processor usage at the query level.
Optimize for Ad Hoc Workloads – this setting may or may not apply to your server – it depends on the workload and the type of queries you run from your apps. If its not set properly, you may be churning your plan cache, which can be a performance killer.
Thanks for reading!