To Frederico's point, here's my entry on the post he provided a link to above...
Just my entry on this post…
When I first laid eyes on the servers at where I currently work full time, I changed them all from 5 to 25. It was a pretty good guess because if I go much above that on the big server (combined OLTP and heavy processing jobs), things started to slow down substantially.
I tried the same #, 25, at a different company I was part-timing at and it didn’t make much of a difference until I got close to 50 on one server and worked fine at 25 on another. It seems to be what types of work load you have going on and what the “split” between OLTP and big processes is.
As with all else in SQL Server, “It Depends” and “Must look eye”! ??
About the only thing that is certain when it comes to the CToP setting is that a setting of 5 has about a 99.99% chance of being the wrong setting for your system for all the reasons that Erik Darling posted in the article at the aforementioned link including what your MAXDOP setting is (as another variable in the mix).
For me, "tuning the engine by ear" worked. I used PerfMon to watch the system to watch for CPU usage, file usage, blocking, and a couple of other settings as I tweaked the CToP setting in a kind of "binary search" between 5 and 100 and then settled for the "best" value rounded up to the nearest 5 just to keep people from asking questions about what made me so sure that (for example) 23 was going to be the best setting.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)