Yesterday I wrote about writing applications using multi-threading, reminding us that parallel execution does not always bring about the advantages we might think. In response to that editorial AZ Jim wrote some helpful reminders about configuring parallel execution in SQL Server.
Parallel execution in SQL Server is similar to using parallel capabilities in software of any kind. The difference is that in a database engine, you do not have the ability to turn it on or off for any particular query. The configuration (as I understand it) is global to the instance of SQL Server. It is the job of the query optimizer to determine if a query benefits from parallel execution or not.
Having worked with some very high volume database instances I have found that the max degrees of parallelism and how well it performs depends on load, and the specific load. It can really make a single query sing. Then load increases and it can actually slow performance down.
The main thing I have learned is that the performance is difficult to predict and has to be measured and tweaked. Moreover, the actual performance may depend on so many factors that the sweet spot may vary throughout the day or some longer cycle.
Any guidance for tuning parallelism? Share your experience in custom or canned software with youcrcomment here or by email to btaylor@sswug.org.
Cheers,
Ben