CODEWITHSUNDEEP
azure-sql-databaseclustered-index
Ros
Ros

Reputation: 634

SQL Azure - Clustered Index Fragmentation

I've inherited an azure database. I'm a web developer and have limited experience of SQL. The website is running slow and in particular 2 stored procedures.

I took a back up of the azure database and imported into my local pc. I have recompiled the stored procedures this did not help. After further investigation, the stored procedures are linked to 6 tables which each have a clustered index.

I have checked the clustered index properties and fragmentation is sitting at 80% and greater.

I'm presuming the indexes need rebuilt/reorganize. I have several questions regarding this:

  1. Does the website need to be offline?
  2. Does the indexes need to be rebuilt/reorganize in any particular order and will any data be lost?
  3. In my local copy I can right click the index and click rebuild or reorganize but in Azure I can't.

Upvotes: 0

Views: 885

Answers (1)

Leon Yue
Leon Yue

Reputation: 16401

To rebuild a clustered columnstore index, SQL Server:

  1. Acquires an exclusive lock on the table or partition while the rebuild occurs. The data is "offline" and unavailable during the rebuild.
  2. Defragments the columnstore by physically deleting rows that have been logically deleted from the table; the deleted bytes are reclaimed on the physical media.
  3. Reads all data from the original columnstore index, including the deltastore. It combines the data into new rowgroups, and compresses the rowgroups into the columnstore.
  4. Requires space on the physical media to store two copies of the columnstore index while the rebuild is taking place. When the rebuild is finished, SQL Server deletes the original clustered columnstore index.
  5. For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REBUILD will re-sort the data. Monitor tempdb during rebuild operations. If you need more tempdb space, you can scale up the data warehouse. Scale back down once the index rebuild is complete.

For more details, please see: Rebuilding Indexes. It also supported by Azure SQL database.

According above, about your questions:

1.Does the website need to be offline?

No, it doesn't.

2.Does the indexes need to be rebuilt/reorganize in any particular order and will any data be lost?

Yes, since you have checked the clustered index properties and fragmentation is sitting at 80% and greater,we suggest you to rebuilt/reorganize the indexes. Your data will not be lost. You can reference this document: Before Reorganize and Rebuild Indexes.

3.In my local copy I can right click the index and click rebuild or reorganize but in Azure I can't.

Azure SQL database supports Reorganize and Rebuild Indexes. Before you rebuild the indexs, you can test first by remove fragmentation.

Hope this helps.

Upvotes: 1

Related Questions