왜 병렬로 안 풀리니?

http://blogs.msdn.com/b/psssql/archive/2013/08/28/i-want-to-know-why-my-query-is-not-parallelized.aspx

SQL2012에서 강화된 기능인데요. 병렬로 플랜이 왜 안풀리는지 XML 실행 계획으로 확인할 수 있다.
Query Plan "NonParallelPlanReason" 속성을 찾아봐라..


<Statements>     

<StmtSimple StatementText="select
* from sys.objects option (maxdop 1)" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.107922" StatementEstRows="2201" StatementOptmLevel="FULL" QueryHash="0xC34FFA269AC9A844" QueryPlanHash="0x39C2C734F752156C" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">

         
<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

         
<QueryPlan NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="96" CompileTime="6" CompileCPU="6" CompileMemory="824">


병렬 실행 계획이 생성되지 않은 이유

  1. MaxDOPSetToOne:   Max Degree of Parallelism set to 1 at query or server level
  2. NoParallelDynamicCursor:  Dynamic cursor doesn’t support parallel plan
  3. NoParallelFastForwardCursor:  Fast Forward cursor doesn’t support parallel plan
  4. NoParallelCreateIndexInNonEnterpriseEdition:   We don’t’ support parallel index operations for non Enterprise editions
  5. NoParallelPlansInDesktopOrExpressEdition:  No parallel plan for express edition  (SQL 2000 desktop edition is the same as
    express edition for later builds)
  6. TSQLUserDefinedFunctionsNotParallelizable:  Scalar TSQL user defined function used in the query
  7. CLRUserDefinedFunctionRequiresDataAccess:  If a CLR user defined function ends up access data via context connection, the query can’t be parallelized.  But a CLR user defined function that doesn’t do data access via context connection can be parallelized.
  8. NoParallelForMemoryOptimizedTables:  This is for any query accessing memory optimized tables (part of SQL 2014 in-memory OLTP feature)