Forum Discussion
DBATony
Mar 20, 2025Copper Contributor
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 had only 25,597 rows, so normally an index creation on that should be very fast in a sql server instance. This is the TSQL used.
CREATE NONCLUSTERED INDEX [NCI_BUSINESS_DATE] ON [bnbi_retail].[FACT_NON_MERCH_DISCOUNT]
(
[BUSINESS_DATE] ASC
)WITH (DROP_EXISTING = OFF)
GO
I did a second test and cancelled after 20 minutes. Can you tell me what could be causing a small new index to run so long? Attached are some screen prints showing the resources are good and the locks created. A larger table recommended has over 61 million rows, so I need to know the root cause for the excessive runtime of an index creation, before attempting anything that large. Also, attached is the DDL for the existing table with the existing Index and the distribution settings that pre-existed for the [bnbi_retail].[FACT_NON_MERCH_DISCOUNT] table.
Here is the reversed DDL for the preexisting table I tried to add the nonclustered index to.:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [bnbi_retail].[FACT_NON_MERCH_DISCOUNT]
(
[TRAN_KEY_ID] [bigint] NOT NULL,
[NON_MERCH_SEQ_NUM] [int] NOT NULL,
[NON_MERCH_DISCOUNT_SEQ_NUM] [int] NOT NULL,
[BUSINESS_DATE_INT] [int] NULL,
[SK_MEMBERSHIP] [bigint] NULL,
[DISCOUNT_ID] [varchar](20) NULL,
[DISCOUNT_AMT] [decimal](9, 2) NULL,
[DISCOUNT_PCT] [int] NULL,
[DISCOUNT_TYPE] [int] NULL,
[DISCOUNT_CALCULATION] [int] NULL,
[DISCOUNT_SOURCE] [int] NULL,
[PROMO_ID] [char](10) NULL,
[DISCOUNT_TYPE_EXTENDED] [int] NULL,
[ENTITLEMENT_TYPE] [char](10) NULL,
[SK_SOURCE_FEED] [bigint] NULL,
[CREATE_DATE] [datetime2](7) NULL,
[UPDATE_DATE] [datetime2](7) NULL,
[CREATE_USER_ID] [varchar](100) NULL,
[UPDATE_USER_ID] [varchar](100) NULL,
[FILE_NAME] [varchar](250) NULL,
[ETL_LOAD_ID] [int] NULL,
[BUSINESS_DATE] [date] NULL,
CONSTRAINT [PK_] PRIMARY KEY NONCLUSTERED
(
[TRAN_KEY_ID] ASC,
[NON_MERCH_SEQ_NUM] ASC,
[NON_MERCH_DISCOUNT_SEQ_NUM] ASC
) NOT ENFORCED
)
WITH
(
DISTRIBUTION = REPLICATE,
CLUSTERED COLUMNSTORE INDEX
)
GO
- olafhelperBronze Contributor
Can you tell me what could be causing a small new index to run so long?
DDL commands like creating an index requires exclusive access on the table; so may the table is in use and this blocks your index creation.
- rodgerkongIron Contributor
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.