0

We just noticed recently that some of our Azure Synapse tables are performing very slow. When we looked at the index information we saw HEAP indexes. We're not sure how they were formed in the first place. But after going through the microsoft documentation, I understood that HEAP indexes are formed when a table is created without a CLUSTERED COLUMNSTORE index.

The below is an example table with HEAP index (as well as other indexes) from our database on Synapse (also, why are there duplicates in the object_id field??) :

object_id   index_name                                      table_name          index_type
123582548   NULL                                            daily_sales         HEAP
123582548   Cnstr_e808ee8e05c94eb39ce93c5b6abc042x          daily_sales         NONCLUSTERED
486209858   ClusteredIndex_9336ef41b4424fd0a554f6964799x77x daily_sales         CLUSTERED COLUMNSTORE
486209858   Cnstr_x235c6b022b74af2be06e541281b625f          daily_sales         NONCLUSTERED

I want to drop these HEAP indexes to improve the performance but I'm unable to do so because, apparently, HEAP indexes don't come with a name attached (hence a NULL in the name). And I do need a name to drop the index as shown in the below syntax:

DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }

This is the first time I'm encountering a HEAP index and I have no idea how to handle them. So, I just want to ask the below:

MS Documentation suggests to create a CLUSTERED COLUMNSTORE index on the table that only has a HEAP index. As shown in the above example, we do have CLUSTERED index as well on the table but still the table doesn't perform. So, I'm under the impression that dropping the HEAP index will improve the performance. Is this assumption even valid?

If so, is there a way to DROP the HEAP indexes on Synapse tables to improve the performance? How to do it?

But if dropping a HEAP index is not a thing, then what can I do to make sure the HEAP index doesn't hamper the query's performance?

At this point, the only solution we're able to look at is to backup the table data, then create a new table with a CLUSTERED COLUMNSTORE index and then insert the data from the backup table into this new table and then drop the original table that has HEAP index. But, to be honest, this doesn't sound like a clean way of solving this problem and we have many tables with HEAP index. So, this approach is not scalable.

What's best way to solve this problem? Any help, please?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
LearneR
  • 2,351
  • 3
  • 26
  • 50
  • On object 123582548 you have a NONCLUSTERED index, but you need a CLUSTERED index to make sure your table is not a HEAP – Wouter Jul 29 '22 at 13:38
  • 1
    A heap is not an index, it's "just a bunch of rows". Essentially it's the absence of a clustered index (either regular or columnstore). You should create a clustered index on the table (I advise a regular one unless you know otherwise). You have two entirely different tables called `daily_sales `presumably in different schemas See also https://learn.microsoft.com/en-us/sql/relational-databases/indexes/heaps-tables-without-clustered-indexes?view=sql-server-ver16 and https://www.brentozar.com/blitz/heaps-tables-without-primary-key-clustered-index/ – Charlieface Jul 29 '22 at 13:38
  • An option to consider is recreating the existing nonclustered index as a clustered one. Also see https://www.sqlskills.com/blogs/kimberly/the-clustered-index-debate-continues/ – Charlieface Jul 29 '22 at 13:41

0 Answers0