MAXDOP – SQL Server’s Way To Avoid Parallelism


SQL Server comes with built-in support for using parallelism for query execution. That means that SQL Server will split up the execution of a query among different CPUs if it thinks that it can get the query results faster that way. The default setting in SQL Server will use the number of CPU cores on the system as the maximum number of parallel processors to use. Okay… this is great, right?

The Grass Isn’t Always Greener
Although the thought of running 1 SQL query but using multiple processors sounds great, sometimes it can actually sometimes cause slower queries. A couple issues I have ran in to in our production environment are:

  • Sometimes the parallelism can take away CPU from other processes that are trying to run at the same time.
  • Sometimes the time that it takes to reassemble the results is longer than the not using parallelism. What this means is that when a query is split among different processors, it will need to merge them back together before it sends it to you. Sometimes this can be a longer task then just using 1 processor the whole time.

MAXDOP is a query hint that can be added to the end of your SQL query to control the amount of parallelism that happens. When you use MAXDOP, you will specify the maximum number of parallelism threads that SQL Server uses for that query. In fact, the word MAXDOP stand for “Maximum Degree Of Parallelism”.

Here is an example of how to use the MAXDOP query hint to limit the number of parallel threads to 1 (essentially disabling the parallelism):

To show how this affects the query, let me show you the execution plan when I supply the MAXDOP 1.
MAXDOP No Parallelism

Here is a screenshot of the execution plan of that exact same query without specifying the MAXDOP. You can see that it now uses parallelism.

MAXDOP Parallelism

Will Removing MAXDOP Help My Queries Run Faster
Is MAXDOP a silver bullet for making queries faster? No. In most cases you will just want SQL Server to manage the parallelism. This is usually the fastest/best way for your query to execute. But occasionally if you are having a slow query, you can try changing the MAXDOP setting. You will need to test your query before and after to see if there is significant improvement.

How Do You Know If Your Query Is Using Parallelism?
Here are a couple ways that I use to find out if parallelism is happening to a query.

  1. Check out the actual execution plan. After you run the query and the execution plan shows up, you will see a “Parallelism” step. You can see the screenshot above for what that looks like.
  2. Monitor the Activity Monitor in SQL Server Management Studio. This is a great way to detect if your production applications are causing parallelism. It’s really easy to see in Activity Monitor because it looks like the same line repeated many times.
    MAXDOP Activity Monitor


SQL Server 2014 Feature – Non-Clustered Indexes For Table Variables


This is truly an amazing feature to add to SQL Server 2014. I use table variables all the time and this was the only thing I didn’t like about them. Up to this point, SQL Server did not support having non-clustered indexes on table variables (the one with the name that starts with the @). If you wanted to do this, you had to create/use a temp table (the one with the name that starts with #).

With SQL Server 2014, they changed this and now allow the table variables to have non-clustered indexes as well. To do this, we simply add a little bit of extra code after the column declaration.

In the sample above, we create a table variable called @AnimalTableVar. Then when we declare the column AnimalName, we add an index called IX_AnimalTempTable_AnimalName.

Here is how selecting from this table shows up in the execution plan!
Non-Clustered Indexes For Table Variables




When querying out data, SQL Server has a ton of different query hints. One less commonly known query hint is FORCE ORDER. So here we will talk about what this query hint does and how we can use it efficiently.

When you put this query hint on to your query, it tells SQL Server that when it executes the statement to not change the order of the joins in the query. It will join the tables in the exact order that is specified in the query. Normally the SQL Server optimizer will rearrange your joins to be in the order that it thinks will be optimal for your query to execute.

To use the FORCE ORDER query hint, you will want to add this at the end of your query.

Example Without FORCE ORDER

This is the actual execution plan without the FORCE ORDER hint. You can see that SQL Server starts at the table Animal and AnimalHabitat tables, then goes to the Habitat table. If you look at our query above, we clearly list the tables in the order of Habitat, AnimalHabitat, then Animal.

Execution Plan Without Force Order

Example With FORCE ORDER

In the screenshot below, it shows the actual execution plan with the FORCE ORDER hint. In the new query, you can see that SQL Server starts with the Habitat and AnimalHabitat tables and then moves to the Animal table. It does this because when we turn on the FORCE ORDER option, it uses the order of the tables specified in the query syntax.

Execution Plan With Force Order

What Is This Used For
The FORCE ORDER query hint is only used when you want to override the way that SQL Server wants to execute this query. Normally you will just let SQL Server figure out how to get the data from the database. It does such a good job at it, that you do not usually need to override this functionality.

Occasionally SQL Server will not figure out the most optimal way to pull this data out. In this case you may want to try this query hint.

Option Recompile


Option recompile is one of those hidden gems when it comes to increasing stored procedure performance.  When used in the right spot, this can speed up a never ending stored procedure call to instantaneous.  If you don’t know what option recompile is… it’s just a query hint that can be added to the end of a sql query.

What Option Recompile Does

When option recompile is added to the query hints, it does a couple things.  The first thing that it does is it tells SQL Server to discard any stored execution plan for that query.  This will cause SQL Server to rebuild the execution plan for this query.  The next thing it does is the magical part.  When SQL Server goes to build the execution plan, it will replace the local variables with the actual values in those variables.  This allows SQL Server to pull the data SUPER fast.


Keep in mind that many times the slowdown in a stored procedure call is just one statement inside the stored procedure.

The easiest way that I have found to figure out if this will help a slow stored procedure is to decompose the stored procedure in to separate sql statements.  I then run the sql statements to see what one is the slow one.  If I run a statement that is slow and uses a local variable, this may be a good candidate.  Next I replace the local variables in that statement with the explicit values.  If I rerun the statement and it performs fast, I know that this could use option recompile.


Rebuild Execution Plan


One way that SQL Server optimizes stored procedure execution time is by caching the execution plan.  This functionality is built-in to SQL Server and is done every time that you create a stored procedure.

What Is An Execution Plan?

An execution plan in SQL Server is simply the way that SQL Server will go about getting the data for the client.  When a query is executed in SQL Server, it will generate an execution plan.  This execution plan is a set of steps that is the most efficient way to get this data from the database for the user.

How Do You Know If You Need To Rebuild An Execution Plan?

Okay, this is not very scientific.  Usually what I’ve found in the past is that when a stored procedure runs fast one day… and the next day it is running slow… one cause might be that the execution plan needs to be rebuilt.

How To Rebuild An Execution Plan?

If you think that a stored procedure in your database has an execution plan that is not the most efficient, here are a couple easy ways to rebuild the execution plan.  Keep in mind that the following queries do not actually rebuild the execution plan.  They merely force the stored procedure to rebuild the execution plan next time that it is executed.

Rebuild For One Specific Stored Procedure

In the example below, CustOrderHist is the name of the stored procedure we want to rebuild the execution plan for.

Rebuild All Execution Plans For All Stored Procedures On Your Server

NOTE: The WITH NO_INFOMSGS option will suppress a generic message that comes up after executing the FREEPROCCACHE command.

Why Does This Happen?

As data changes in your database, the data in the index will change as well.  As the statistics for an index change, what used to be the fastest way to pull data may no longer be the fastest way.

Automatic Rebuild Of The Execution Plan

Of course SQL Server does not require you to always rebuild the execution plans manually.  There are triggers in the database that will cause an automatic rebuild of the execution plan.

  • Alterations to the table, view, stored procedure, or indexes used by the stored procedure
  • Updating statistics on the table or index that the stored procedure uses
  • Dropping an index used by the stored procedure
  • A lot of inserts or deletes from a table (causing a large change in the keys)
  • Executing a stored procedure with the WITH RECOMPILE option

Reference:  –  sp_recompile (Transact-SQL)
Reference:  –  DBCC FREEPROCCACHE (Transact-SQL)