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">
병렬 실행 계획이 생성되지 않은 이유
- MaxDOPSetToOne: Max Degree of Parallelism set to 1 at query or server level
- NoParallelDynamicCursor: Dynamic cursor doesn’t support parallel plan
- NoParallelFastForwardCursor: Fast Forward cursor doesn’t support parallel plan
- NoParallelCreateIndexInNonEnterpriseEdition: We don’t’ support parallel index operations for non Enterprise editions
- NoParallelPlansInDesktopOrExpressEdition: No parallel plan for express edition (SQL 2000 desktop edition is the same as
express edition for later builds) - TSQLUserDefinedFunctionsNotParallelizable: Scalar TSQL user defined function used in the query
- 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.
- NoParallelForMemoryOptimizedTables: This is for any query accessing memory optimized tables (part of SQL 2014 in-memory OLTP feature)
'SQL Server 2012' 카테고리의 다른 글
sys.dm_exec_describe_first_result_set (0) | 2014.01.20 |
---|---|
SQL2012에서 SHA2_256, SHA2_512 추가 (0) | 2013.10.07 |
SQL2012 Server 리붓 후 identity 갭 현상 (0) | 2013.08.23 |
스케쥴링 할당 방법 SQL2012 EE 버젼 변경 (0) | 2013.08.14 |
SQL Server 2012 Memory (0) | 2013.05.27 |