I discuss an undocumented exception to the above in Properly Persisted Computed Columns, for those people interested in more details.
The T-SQL scalar function restriction is also a bit sneaky.Īny reference to a table (or view) with a computed column that uses a T-SQL scalar function will result in a serial plan, even if the problematic column is not referenced in the query. ERROR_NUMBER and force a serial plan, while and do not. Inconveniently, the list of intrinsic functions is quite long and does not seem to follow a pattern.
The list changes from version to version, but for example these things make the whole plan serial on SQL Server 2012: Some of these inhibitors force the whole plan to run serially, others require a ‘serial zone’ - a section of the plan that runs serially, even though other parts may use multiple threads concurrently. There are many things that prevent parallelism, either because they make no sense in a parallel plan, or because SQL Server just does not support parallelism for them yet.
PARALLEL TO SERIAL CONVERTER EXAMPLE CODE
Leaving aside the more trivial causes, such as the configuration setting max degree of parallelism being set to 1, running under a Resource Governor workload group with MAX_DOP = 1, or having only one logical processor available to SQL Server, the usual causes of a serial plan are parallelism-inhibiting operations, cardinality estimation errors, costing model limitations, and code path issues. Whenever the query optimizer produces a serial plan instead of the ‘obviously better’ parallel plan, there is always a reason. Why Expensive Queries Produce Serial Plans In any case, the resulting SQL is usually hard to read, and scary to maintain.
The result of all this is a great deal of wasted time trying increasingly obscure query syntax, until eventually the desired parallel plan is obtained, or the developer gives up in despair.Įven where success is achieved, the price is often fragile code that risks reverting to serial execution any time the indexing or statistics change. Sadly, there is currently no corresponding hint to force the optimizer to choose a parallel plan. One such query hint is the (over) popular OPTION (MAXDOP 1), which prevents the optimizer from considering plans that use parallelism. These hints are usually seen as a tool of last resort, because they can make code harder to maintain, introduce extra dependencies, and may prevent the optimizer reacting to future changes in indexing or data distribution. SQL Server provides a number of query and table hints that allow the experienced practitioner to take greater control over the final form of a query plan. This situation often occurs when making an apparently innocuous change to the text of a moderately complex query - a change which somehow manages to turn a parallel plan that executes in ten seconds, into a five-minute serially-executing monster. This article is for SQL Server developers who have experienced the special kind of frustration that only comes from spending hours trying to convince the query optimizer to generate a parallel execution plan.
0 kommentar(er)
