I’ve been on my project for over a year and it has significantly grown as an application and in data during the year. It’s been nonstop new features. I’ve rarely gone back and refactored code. Last week I noticed some of the data heavy pages were loading slowly. At the worst case one view could take up to 30 seconds to load. 10 times over my maximum load time…
Call me naive, but I didn’t consider index fragmentation in SQL Azure. It’s the cloud! It’s suppose to be immune to premise issues… Apparently index fragmentation is also an issue in the cloud.
I found a couple of queries on an MSDN blog, that identify the fragmented indexes and then rebuilds them.
After running the first query to show index fragmentation I found some indexes with over 50 percent fragmentation. According to the article anything over 10% needs attention.
First Query Display Index Fragmentation
--Get the fragmentation percentage SELECT DB_NAME() AS DBName ,OBJECT_NAME(ps.object_id) AS TableName ,i.name AS IndexName ,ips.index_type_desc ,ips.avg_fragmentation_in_percent FROM sys.dm_db_partition_stats ps INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id CROSS APPLY sys.dm_db_index_physical_stats(DB_ID(), ps.object_id, ps.index_id, null, 'LIMITED') ips ORDER BY ps.object_id, ps.index_id
Second Query Rebuilds the Indexes
--Rebuild the indexes DECLARE @TableName varchar(255) DECLARE TableCursor CURSOR FOR ( SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName] FROM INFORMATION_SCHEMA.TABLES IST WHERE IST.TABLE_TYPE = 'BASE TABLE' ) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT('Rebuilding Indexes on ' + @TableName) Begin Try EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)') End Try Begin Catch PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild') EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') End Catch FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor