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 :(

https://technet.microsoft.com/en-us/library/ms178065(v=sql.105).aspx

https://technet.microsoft.com/en-us/library/ms188611(v=sql.105).aspx

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

https://manage.windowsazure.com/PublishSettings/index?Client=&SchemaVersion=&DisplayTenantSelector=true

or

call the powershell cmdlet

Get-AzurePublishSettingsFile

 

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: ,