Non Parallelizable operations in SQL Server

Anyone working on SQL Server will have banged their head against a wall at one point or another trying to figure why a query hitting millions of rows isn’t being done in parallel.

Well there have been a number of blogs posts about this, Craig Freedman’s excellent series on the query engine being the first

With the amazing Paul “query optimiser for a brain” White has a further list

There are some details in MSDN/BOL although these pages appear to have been removed for SQL 2012 and 2014 in the redesign of BOL :(

Nicely in SQL Server 2012 a reason was added to the execution plan, but the list of reasons wasn’t document. Well here it is

      L"MaxDOPSetToOne"                                                       // DOP configuration option is set to 1
      L"NoParallelPlansDuringUpgrade"                                         // no parallel plans during upgrade
      L"ParallelismDisabledByTraceFlag"                                       // the 8687 traceflag is on to disable parallelism
      L"EstimatedDOPIsOne"                                                    // the estimated DOP is 1
      L"NoParallelDynamicCursor"                                              // no parallel plans for dynamic cursor
      L"NoParallelFastForwardCursor"                                          // no parallel plans for fast forward cursors
      L"NoParallelCursorFetchByBookmark"                                      // no parallel plans for cursors that fetch by bookmark
      L"NoParallelCreateIndexInNonEnterpriseEdition"                          // no parallel index creation in non-enterprise edition
      L"NoParallelPlansInDesktopOrExpressEdition"                             // no parallel plans for desktop or express editions
      L"NonParallelizableIntrinsicFunction"                                   // a non-parallelizable intrinsic function
      L"UpdatingWritebackVariable"                                            // no parallel plans while updating a writeback variable
      L"CLRUserDefinedFunctionRequiresDataAccess"                             // CLR UDF requires data access
      L"TSQLUserDefinedFunctionsNotParallelizable"                            // T-SQL Udfs are not parallelizable
      L"TableVariableTransactionsDoNotSupportParallelNestedTransaction"       // Table variable transactions do not support parallel nested transactions
      L"DMLQueryReturnsOutputToClient"                                        // DML query returns output to client, which forces to yield the transaction and doesn't work with parallel plans
      L"MixedSerialAndParallelOnlineIndexBuildNotSupported"                   // Query Execution does not support mixed serial and parallel plans for a single online index build tree
      L"CouldNotGenerateValidParallelPlan"                                    // Verifying parallel plan failed and we had to fall back to serial
      L"NoParallelForMemoryOptimizedTables"                                   // no parallel plans for Hekaton/In-memory tables

If you are smart you will see NonParallelizableIntrinsicFunction. This hides a multitude of functions like the encryption ones that it would be great to enable or at least document. I’m working on it but I understand its a fairly long list, Paul’s blog has some details.

Published Sunday, April 26, 2015 6:17 PM by simonsabin


Monday, April 27, 2015 10:03 AM by SimonS Blog on SQL Server Stuff

# Non Parallelizable operations in SQL Server

Anyone working on SQL Server will have banged their head against a wall at one point or another trying

Saturday, May 2, 2015 11:59 AM by BPOTW 2015-05-01 | SQL Notes From The Underground

# BPOTW 2015-05-01 | SQL Notes From The Underground

Pingback from  BPOTW 2015-05-01 | SQL Notes From The Underground

# Parallel execution, part 2 « Sunday morning T-SQL

Pingback from  Parallel execution, part 2 « Sunday morning T-SQL