on Mar 24, 2018Hi All,
I'm wondering what people's experiences are with tuning performance of the SQL Server back-end?
We use SQL 2012 on an 8-core CPU VM. I have MAXDOP set to 8, and threshold to 50. I haven't noticed much difference raising or lowering MAXDOP, or even raising or lower the threshold. Most queries and reports just seem to use 1 thread.
I'd be interested to hear what other people have as their settings, and if they've found any that make any noticeable difference?
The general recommendation from Snow is to use the automatic maximum MAXDOP by setting it to 0 and the cost threshold to the default 5. If you enable and use indexing then degrees of parallelism can be set for those activities specifically, and should also be set to automatically use the full set.
In order for the threshold to make a difference here you may need to go lower than the default, as that will promote further parallelism.
Other considerations for performance are to turn off autoshrink, set autogrow to 10%, set simple recovery mode, and make sure to distribute the 2 databases and logs on different and fast drives. In virtual machines the last part can make a big difference, in which the chosen disks may have low data read and write speeds which affects database performance.
I hope this helps, I am not a SQL Server expert but this has helped before.