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

Question

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?

Answer 1

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.

[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 or upvote 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