These settings are always confusing; especially since they should be changed out of the box. One of my clients has a constantly polling SQL Server and I had a chance to play with these settings (on a Sunday, of course).
I love SQL Server. I enjoy observing it and seeing how it behaves under different stresses and strains. But its one thing to think you know how it works as compared to how it actually works.
So, I created a little SQL code to capture our throughput, and I used Batch Requests/second as I kind of like it.
waitfor delay '00:00:05' declare @val1 bigint declare @val2 bigint select @val1 = cntr_value from sys.dm_os_performance_counters where counter_name like '%batch%requests%' waitfor delay '00:01:00' select @val2 = cntr_value from sys.dm_os_performance_counters where counter_name like '%batch%requests%' print @val2-@val1
and stepped through different combinations of Cost Threshold and Maximum Degrees. To the right at the bottom, I created an average of the whole set of MAXDOPs. Incidentally this beast had 24 cores with a hood of SQL 2008 R2.
| CTFP | MAXDOP | BR/Sec | ||
| 0 | 0 | 242377 | ||
| 5 | 0 | 214721 | ||
| 10 | 0 | 238521 | ||
| 15 | 0 | 233392 | ||
| 20 | 0 | 244839 | ||
| 25 | 0 | 251196 | ||
| 30 | 0 | 259546 | ||
| 35 | 0 | 243637 | ||
| 40 | 0 | 235743 | 240441.3 | |
| 0 | 2 | 271237 | ||
| 5 | 2 | 263595 | ||
| 10 | 2 | 260949 | ||
| 15 | 2 | 262912 | ||
| 20 | 2 | 255064 | ||
| 25 | 2 | 271242 | ||
| 30 | 2 | 271620 | ||
| 35 | 2 | 301223 | ||
| 40 | 2 | 287216 | 271673.1 | |
| 0 | 4 | 293555 | ||
| 5 | 4 | 294273 | ||
| 10 | 4 | 288692 | ||
| 15 | 4 | 279726 | ||
| 20 | 4 | 302638 | ||
| 25 | 4 | 292235 | ||
| 30 | 4 | 281196 | ||
| 35 | 4 | 270659 | ||
| 40 | 4 | 274915 | 286432.1 | |
| 0 | 6 | 257295 | ||
| 5 | 6 | 243047 | ||
| 10 | 6 | 267746 | ||
| 15 | 6 | 236602 | ||
| 20 | 6 | 264628 | ||
| 25 | 6 | 268295 | ||
| 30 | 6 | 256659 | ||
| 35 | 6 | 266866 | ||
| 40 | 6 | 283196 | 260481.6 |
Now what did I learn from this. Well, funnily enough the default of 5 and 0 came out with the lowest score. I swear I did nothing but report the facts here, maam.
Any BR/sec over 280,000 I have highlighted with a BOLD and an UNDERLINE.
To summarise though;
- 2 – 4 MAXDOP is best…
- 20-40 is better for CTFP
but if you want cold hard figures for an OLTP system, I’d say 22 for CTFP and 4 for MAXDOP.
Just a thought…