A customer reported that they found creating indexes sometimes become very slow in SQL2017. We analyzed this issue and found below symptom
This issue happens when creating index on partition table. But all rows are in one partition.
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.