Today’s SQL in Sixty Seconds video is inspired from my presentation at TechEd India 2012 on Speed up! – Parallel Processes and Unparalleled Performance.
There are always special cases when it is about SQL Server. There are always few queries which gives optimal performance when they are executed on single processor and there are always queries which gives optimal performance when they are executed on multiple processors. I will be presenting the how to identify such queries as well what are the best practices related to the same.
In this quick video I am going to demonstrate if the query is giving optimal performance when running on single CPU how one can restrict queries to single CPU by using hint OPTION (MAXDOP 1).
More on Errors:
Difference Temp Table and Table Variable – Effect of Transaction
Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT
Debate – Table Variables vs Temporary Tables – Quiz – Puzzle – 13 of 31
I encourage you to submit your ideas for SQL in Sixty Seconds. We will try to accommodate as many as we can.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
Hi, pinal,,
i was at lalit 1 & 2 for this session,, to be very true,, loved your sppech and topic,,you make the difference,,,
hope i will see u next year,,,
Hi Pinal Dave Sir,
I am not commenting for the above reply.
I am commenting by over all about the site of you.
When ever we face issues on MSBI, if we search for solutions,
if your face / site comes before us then we are getting confident that we can have a solution.
This kind of confidence you build for the people who are indeed on MSBI.
Thanks,
Chetan
Brilliant Stuff!
Did you engineer the demo by manipulating DOP parameters and statistics or this can be reproed with out of the box configurations?
What is the plan warning (those exclamation marks on SELECT) you have in both the plans?
I believe that if you fix the warning, the optimizer will not choose a parallel plan for this query.
I was not able to repro this in any of my test environments even with a reduced cost threshold for parallelism.
In general, a parallel plan will have a bigger cost than a serial plan ONLY when the sum of the cost of the individual parallelism related operators (Repartition streams, gather streams etc) is greater than the sum of CPU cost reduction achieved in other operators due to parallelism. And in such scenarios the Optmizer generally favors the serial plan.
Full credits to you for coming up with such a special scenario.
It’ll be great if you can attach the xml plans to this post for getting the full picture :)
Short of testing every single query, how do we know when to use this?