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

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.

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

Bob_Cai_1-1604124635180.png

 

 

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.

 

Bob_Cai_2-1604124635201.png

 

 

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

 

Bob_Cai_3-1604124635214.png

 

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.

 

Bob_Cai_4-1604124635260.png

 

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.

 

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.