Forum Discussion
Synapse SQL - Index creation is extremely slow for a small table
I attempted to create a nonclustered index on a small table in synapse. It was running for was running 15 mins and started to block sessions, so I cancelled it. The FACT_NON_MERCH_DISCOUNT table ha...
The problem might be caused by the following reasons:
- The table [bnbi_retail].[FACT_NON_MERCH_DISCOUNT] uses a CLUSTERED COLUMNSTORE INDEX (CCI), which is optimized for analytics but not for rowstore index creation. Creating a nonclustered rowstore index [NCI_BUSINESS_DATE] on a CCI table requires converting columnar data to rowstore format, this takes time.
- [bnbi_retail].[FACT_NON_MERCH_DISCOUNT] is REPLICATE. Replicated tables on Synapse store a full copy of the data on each node. When you create an index, it must be built on every copy of the replicated table across all nodes and distributions. This leading to longer runtime.
You can try to create a normal test table, copy data to the test table, then create the nonclustered index.