병렬플랜 vs 멀티쓰레드 같은 의미인데~ 흠..

Parallel Query Execution.pdf

http://blogs.msdn.com/b/psssql/archive/2012/12/21/uneven-query-executions-with-parallelism.aspx

※ 2013.02.18

주디아줌마) 병렬로 풀리더라도 아래와 같은 몇가지 이슈가 나오면 시리얼 플랜으로 풀린다.

Forcing a Parallel Query Execution Plan

http://sqlblog.com/blogs/paul_white/archive/2011/12/23/forcing-a-parallel-query-execution-plan.aspx

Parallelism-Inhibiting Components

There are many things that prevent parallelism, either because they make no sense in a parallel plan, or because the product just does not support them yet.  Some of these force the whole plan to run serially, others require a ‘serial zone’ – a part of the plan that runs serially, even though other parts may use multiple threads concurrently.

That list changes from version to version, but for example these things make the whole plan serial on SQL Server 2008 R2 SP1:

• Modifying the contents of a table variable (reading is fine)
• Any T-SQL scalar function (which are evil anyway)
• CLR scalar functions marked as performing data access (normal ones are fine)
• Random intrinsic functions including OBJECT_NAME, ENCYPTBYCERT, and IDENT_CURRENT
• System table access (e.g. sys.tables)

Inconveniently, the list of intrinsic functions is quite long and does not seem to follow a pattern.  ERROR_NUMBER and @@TRANCOUNT also force a serial plan, @@ERROR and@@NESTLEVEL do not.  The T-SQL scalar function restriction is also a bit sneaky.  Any reference to a table with a computed column that uses such a function will result in a serial plan, even if the problematic column is not referenced in the query.

These query features are examples that require a serial zone in the plan:

• TOP
• Sequence project (e.g. ROW_NUMBER, RANK)
• Multi-statement T-SQL table-valued functions
• Backward range scans (forward is fine)
• Global scalar aggregates
• Common sub-expression spools
• Recursive CTEs