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


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.