Today, I worked on a service request that our customer reported that running a complex query this is executing in parallel but having more than 2 vCores in Azure SQL Database this query is not using parallelism.
During the troubleshooting process we suggested multiple tips and tricks, but any of them made that Azure SQL Engine uses parallelism:
- Update the statistics and rebuild the indexes.
- Use Recompile or MAXDOP, grant memory hint
- Use the latest compatibility model
- Clear buffer pool.
- Clear procedure cache.
- Comparing the same automatic statistics, comparing rows, etc..
- Plan guides.
However, running in OnPremise the same query runs in parallel. Logically, the execution time in OnPremise is less that Azure SQL Database.
In this situation, we asked the execution plan for both executions and we found something that paid our attention.
- In Azure SQL Database we found:
<StmtSimple StatementCompId="1" StatementEstRows="9" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="140" StatementSubTreeCost="310.55" StatementText="...."
StatementType="SELECT" QueryHash="0x6EED46FD....." QueryPlanHash="0xD01F6...." RetrievedFromCache="false" StatementSqlHandle="0x09004811D......." DatabaseContextSettingsId="39"
ParentObjectId="0" StatementParameterizationType="0" SecurityPolicyApplied="true"><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NonParallelizableIntrinsicFunction" MemoryGrant="546128" CachedPlanSize="200" CompileTime="23" CompileCPU="23" CompileMemory="1816">
- In OnPremise we found:
<StmtSimple StatementCompId="1" StatementEstRows="9" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="140" StatementSubTreeCost="257.52" StatementText="................"
StatementType="SELECT" QueryHash="0x......" QueryPlanHash="0x21...." RetrievedFromCache="true"
SecurityPolicyApplied="false"><QueryPlan DegreeOfParallelism="4" MemoryGrant="1529520" CachedPlanSize="408" CompileTime="115" CompileCPU="32" CompileMemory="2568">
Comparing both execution we found a difference in the parameter of SecurityPolicyApplied that in Azure SQL Database is true and OnPremise is false. Also, we found that the main reason that Azure SQL DB is not using parallelism is due to NonParallelPlanReason="NonParallelizableIntrinsicFunction".
With this information we review the execution plan of Azure SQL DB and we found a filter operator filtering data defined in Row Level Security and this operation is affecting somehow to execute in parallel the query, however, in OnPremise we didn't see this filter.
Once we found this and after remove the security policy of Row Level Security, Azure SQL Database started to use Parallelism reducing the execution time of this query.
So, very good lesson learned today, check Row Level Security!!!...
Enjoy!
Microsoft