Why can’t I get a parallel plan when rebuilding my index?

아티클
01/09/2017

In support, we frequently have customers who call us and want to find out why a particular query doesn’t use parallelism. Recently, we had a user who wanted to know why his index rebuild didn’t use parallelism. In some cases, NonParallelPlanReason in query plan tells you the reason.

But in this case, the query plan just say “CouldNotGenerateValidParallelPlan” like <QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="184" CompileTime="2" CompileCPU="2" CompileMemory="512">.

Upon closer look, it’s customer’s data distribution. Thought the index is a unique clustered index (primary key) with multiple key columns, the leading column has just one value.

https://technet.microsoft.com/en-us/library/ms189329(v=sql.120).aspx and https://technet.microsoft.com/en-us/library/ms191292(v=sql.105).aspx have documentation on how parallelism works with index operations. On a very high level, the leading key of the index of non-partitioned tables need to have multiple distinct values in order for parallelism to work. Essentially, each parallel thread gets a set of value ranges and work on them and later ‘merge’ them together. So if you just have one value for the leading key column, only one thread can work on it. In other words, you won’t get a parallel plan.

This customer wanted parallelism regardless. So we did some testing to see if a parallel plan actually helps. Even if you force a parallel plan, it won’t make your index rebuild (create) faster. In the testing scenario, I first populated the leading column with lots of unique values. I was able to get a parallel plan and then change the leading column to just one distinct value. The result is that parallel plan didn’t make it faster.

Here were the steps to do it

script out the statistics which have good data distribution with leading column being fairly unique. See https://support.microsoft.com/en-us/kb/914288 for more details
Update the leading column with same value for all rows.
update statistics scripted out form step 1 using UPDATE STATISTICS WITH STATS_STREAM =<binary value). This would make optimizer thinks the leading column has lots of distinct values.
run index rebuild. Note that I did get a parallel plan. But index rebuild didn’t run any shorter.

From the actual execution plan, you can see why. Below was the scan operation from the plan. Note that only thread 1 did all the work of 2416640 rows. All other threads were doing nothing.

</RunTimeInformation>

Another question is what is the usefulness of an index if leading column has just one value? So slow rebuilding index won’t be the only problem to solve.

Demo

use master
go
create database testdb2
go
use testdb2
go
create table t (c1 int, c2 int, c3 int)

set nocount on
begin tran

declare @i int = 0
while @i < 10000000
begin
insert into t values (@i, @i, @i)
set @i = @i + 1
end
commit tran

create unique clustered index ix on t(c1,c2,c3)

go
--this will use parallel plan
ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)

go
--set leading column as dup
update t set c1 = 1

go
update statistics t with fullscan
go
--this will use serial plan
ALTER INDEX ix on [dbo].t rebuild WITH (maxdop=10, online=on)

go
--dbcc show_statistics (t, ix)

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus

Comments

Anonymous
January 16, 2017
Hello JackThanks for this nice article. Bear in mind that this MS-SQL Server behavior doesn't reproduce in Oracle. I will be blogging about that in a couple of days (or weeks). I was thinking that parallelism algorithm are almost identical in Oracle and MS-SQL Server.Best regardsMohamed
Anonymous
March 21, 2017
The comment has been removed
Anonymous
August 07, 2017
Hello Jack,I have finally managed to blog about using parallelism when rebuilding unique indexes in both MS-SQL and Oraclehttps://hourim.wordpress.com/2017/08/05/parallel-index-rebuild-oracle-versus-ms-sql-server/ Oracle works differently in this case.Best regardsMohamed Houri

다음을 통해 공유

Why can’t I get a parallel plan when rebuilding my index?

Demo

Comments

추가 리소스