Minimum plan threshold, how to configure it?
-
Hi,
I have a server with 4 cpus with 2 gbs and use the minimum plan threshold cost as 5 (default). When I change for a new server with 8 cps and 4 gbs I change this property value to 3, expecting better performance with parallelism. Unhappily I think this not work properly because some SPIDs have more than 15 threads at some time. How can I determine the better value? Is it normal or I'm really losing performance with my change?
Regards.
-
Believe it or not, there is some evidense that 4 cpus is optimal for SQL. I believe Gartner Group may have some data on this, but the jist is that the overhead of additional CPUs beyond 4 dones not always transalte into greater performance.
-
As far as that setting, you'd need to do some testing to see what works better with your system. I'd test the performance of some procs with the settings at different levels. You might even want to run a profile and capture some actual queries and replay them with different settings.
You can also read this: http://www.sqlservercentral.com/columnists/hroggero/managingmaxdegreeofparallelism.asp
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply