Hey guys - I am wondering if anyone can suggest some good literature on determining the proper Maxdop, Parallel Threshold, and # of worker threads to run.
We were in the MS Lab out in San Jose last week benchmarking our app against SQL 2k and SQL 2k5, and it seems that CXPackets were killing us in 2k5 (running against 4 dc opterons). Turning off Maxdop helped, but we have a pretty wide range of queries in our application - stuff ranging from a couple of milliseconds to an occasional 10k ms. I would like to use maxdop, but I am wondering how to approach finding the ideal threshold (other than just benchmarking it to death).
Also, same kind of question on worker threads - is there a methodology for finding the right setting for my environment, or is it pin the tail on the donkey?
Our app typically runs against 16 or 32 way boxes with tons of memory.
Hi,
There is a rule of thumb concerning the worker threads in the BOL of SQL Server 2005.
In SQL Server 2005 this is 'self tuning' by default.
I don't know of any 'wonder' formulas to determine the other settings (except benchmarking it to death) :-)
If I do find something I'll let you know.
Kr
No comments:
Post a Comment