sql server degree of parallelism and parallel query plans
TRANSCRIPT
![Page 1: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/1.jpg)
SQL Server & Parallel Queries
iKosmik
Crafted for the love ♥ of SQL Server
![Page 2: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/2.jpg)
How does SQL Server choose a
Parallel Query Plan to run you query?
iKosmik
![Page 3: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/3.jpg)
… Based on how many cores
your machine has
More cores is a reason to celebrate! This means there is greater chance that SQL Server will consider the Parallel Query Plan to execute your query!
iKosmik
![Page 4: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/4.jpg)
… Are there enough number of threads for the parallel run?
That’s self-explanatory. SQL Server looks at the current load on the database and if it can allocate enough number of threads to make this parallelism happen.
iKosmik
![Page 5: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/5.jpg)
… What query processing
are you doing in there?
SQL Server is real smart here! It looks at what kind of processing / computation are you really doing in your query. If it’s a CPU hungry query, say, something like creating/rebuilding an india, or maybe sorting, then it decides to run it in parallel.
iKosmik
![Page 6: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/6.jpg)
… are there enough rows for processing?
If there are “not” enough rows to be processed, then SQL Server may drop the parallelism idea.
iKosmik
![Page 7: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/7.jpg)
… Is the distribution stats up-to-date?
SQL Server looks for an up-to-date distribution statistics in order to consider running a query with a parallel plan.
iKosmik
![Page 8: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/8.jpg)
when does SQL Server choose a
Parallel Query Plan to run you query?
iKosmik
![Page 9: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/9.jpg)
… Depends on the Cost Threshold for Parallelism
setting
So SQL Server compares the cost of running the query using its Serial Query Plan with the Cost Threshold for Parallelism value set in your database.
If
Cost of Serial Plan > Cost Threshold for Parallelism
there is a great chance that SQL Server will run your query in using the highest degree of parallelism possible!
iKosmik
![Page 10: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/10.jpg)
When is it a sure NO NO?
![Page 11: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/11.jpg)
Got a dynamic cursor?
You got a dynamic cursor in there?
Oh no! You lose the chance to run parallel! Sorry!
iKosmik
![Page 12: Sql Server Degree of Parallelism and Parallel Query Plans](https://reader033.vdocuments.us/reader033/viewer/2022042723/58e5f36b1a28ab09478b490d/html5/thumbnails/12.jpg)
Happy DBA’ing!
See you soon with another interesting crucial SQL Server concept!
Until then …
iKosmik
http://www.sqlserverapp.com/