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 (http://blog.sqlauthority.com)

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

  1. 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,,,


  2. 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.



  3. 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 :)


  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #021 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s