SQL SERVER – Using MAXDOP 1 for Single Processor Query – SQL in Sixty Seconds #008 – Video

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)

Database, SQL in Sixty Seconds, SQL Scripts
Previous Post
SQL SERVER – #TechEdIn – Presenting Tomorrow on Speed Up! – Parallel Processes and Unparalleled Performance at TechEd India 2012
Next Post
SQL SERVER – Transcript of Learning SQL Server Performance: Indexing Basics – Interview of Vinod Kumar by Pinal Dave

Related Posts

4 Comments. Leave new

  • Keyur Thakkar
    March 23, 2012 7:19 pm

    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.


  • 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?


Leave a ReplyCancel reply

Exit mobile version