question

RameshBabu-6697 avatar image
0 Votes"
RameshBabu-6697 asked KCHENNAIAH-5266 commented

Rebuild or Re-organize Indexes on Azure Synapse Analytics or Azure dedicated SQL pool

Can some one help me to find T-SQL commands to pull fragmentation report and T-SQL commands to execute Rebuild or Re-organize and update stats on Azure Synapse Analytics or Azure dedicated SQL pool(Formerly SQL DW). Databases?

azure-synapse-analytics
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered KCHENNAIAH-5266 commented

Hello anonymous user-6697,

Thanks for the question and using MS Q&A platform.

UPDATE: The information in the below article does not apply to a dedicated SQL pool in Azure Synapse Analytics. For information on index maintenance for a dedicated SQL pool in Azure Synapse Analytics, see Indexing dedicated SQL pool tables in Azure Synapse Analytics.

202754-image.png

[Rebuild_Index_SQL] SQL to rebuild columnstore index for a table

The column Rebuild_Index_SQL in the vColumnstoreDensity view contains an ALTER INDEX REBUILD statement that can be used to rebuild your indexes. When rebuilding your indexes, be sure that you allocate enough memory to the session that rebuilds your index. To do this, increase the resource class of a user that has permissions to rebuild the index on this table to the recommended minimum. For an example, see Rebuilding indexes to improve segment quality later in this article.

For a table with an ordered clustered columnstore index, ALTER INDEX REBUILD will re-sort the data using tempdb. Monitor tempdb during rebuild operations. If you need more tempdb space, scale up the database pool. Scale back down once the index rebuild is complete.

For a table with an ordered clustered columnstore index, ALTER INDEX REORGANIZE does not re-sort the data. To re-sort data, use ALTER INDEX REBUILD.



This article - Optimize index maintenance to improve query performance and reduce resource consumption helps you decide when and how to perform index maintenance. It covers concepts such as index fragmentation and page density, and their impact on query performance and resource consumption. It describes index maintenance methods, reorganizing an index and rebuilding an index, and suggests an index maintenance strategy that balances potential performance improvements against resource consumption required for maintenance.

Checkout the examples - Maintaining indexes using Transact-SQL and also ALTER INDEX (Transact-SQL) statement for syntax and examples.

Hope this will help. Please let us know if any further queries.


  • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how

  • Want a reminder to come back and check responses? Here is how to subscribe to a notification

  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators


image.png (12.6 KiB)
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @PRADEEPCHEEKATLA-MSFT , Thank you for prompt reply. I gone though the link you have provided, Seems that article is meant for regular SQL Servers and Azure SQL Databases. Unfortunately I couldn't find any relevant DMVs for Measuring index fragmentation and page density for Azure Synapse Databases from the provided link. Actually I m looking for database maintenance solution for Azure Data warehouse databases, hope you got my requirement .kindly suggest if there any references, thank you.

0 Votes 0 ·

Hello anonymous user-6697,

Yes, you are correct. Please check the updated answer.

The information in the above article does not apply to a dedicated SQL pool in Azure Synapse Analytics. For information on index maintenance for a dedicated SQL pool in Azure Synapse Analytics, see Indexing dedicated SQL pool tables in Azure Synapse Analytics.

1 Vote 1 ·

Excellent information Pradeep.

0 Votes 0 ·

Hi @PRADEEPCHEEKATLA-MSFT, Excellent information and sure it will be most helpful, I will keep posted my results, thank you again for your time.

0 Votes 0 ·