April 2015 - Posts

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 http://blogs.msdn.com/b/craigfr/archive/2007/04/17/parallel-query-execution-presentation.aspx

With the amazing Paul “query optimiser for a brain” White has a further list http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx

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.

Posted by simonsabin | 3 comment(s)

This is a quick note to myself that to download the publish settings file go to



call the powershell cmdlet



On many sites the link is to he following but that seems to be old and not work.

Link that doesn’t work https://windows.azure.com/download/publishprofile.aspx

Posted by simonsabin | with no comments
Filed under: ,