Forum Discussion

DBATony's avatar
DBATony
Copper Contributor
Mar 20, 2025

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

  • olafhelper's avatar
    olafhelper
    Bronze 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.

  • rodgerkong's avatar
    rodgerkong
    Iron 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.

Resources