Creating index becomes extremely slow when all rows are in one partition

This article is contributed. See the original author and article here.

A customer reported that they found creating indexes sometimes become very slow in SQL2017. We analyzed this issue and found below symptom

1. This issue happens when creating index on partition table. But all rows are in one partition.


2. This issue happens when the database compatibility level is 140. When we change database compatibility level to 100, issue will disappear.

 

It seems it’s CE issue. We need to check execution plan. However, we are not able to get execution plan for ‘create index’ query in SSMS directly. Alternatively, we found below methods to get an ongoing actual execution plan.

 

1)  Choose ‘Include Actual Execution Plan’. Get session id =56

 

 

2) On another session , run this query every minutes to get ongoing actual execution plan

 

SELECT * FROM sys.dm_exec_query_statistics_xml(56);

 

 

New CE —  under 140 compatibility level

=====================================

This table has 100 partitions, but all rows are in one partition. We can see this table has 216213923 rows.

 

 

 

Then we got ongoing actual execution plan. We found ‘Actual Number of Rows’ were more than the total number of rows of entire table.

 

 

We captured Xevent trace as well. It seems SQL SERVER sort 216213923 rows again and again.  we guess the new CE did the sort 100 times for the entire table.

 

 

We checked source codes. We found new CE use a new function CSelCalcHistogramComparison to calculate partition selectivity. Since all rows are in one partition in our case, the selectivity was calculated to 1. Therefore it failed to push down the partition ID predicate to index scan. So it executed 100 times full table scan and sort.

 

Microsoft has noticed this issue and has fixed it. We can enable trace flag  -T4199 to fix this issue.

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.