SQL Server Azure Index Defragmentation and performance tuning

IndexFragmentation

Reorganising and rebuilding indexes is crucial to maintaining database performance; however on SQL Azure indexes are much less transparent & the standard “DBCC DBREINDEX” approach isn’t supported on the platform.

However; Chris Pietschmann has posted an article on rebuilding all the indexes in a database here.  The operation to rebuild an index is:

ALTER INDEX ALL ON [dbo].[MyTable] REBUILD

In order to do this for all indexes within the database; you can use the below script (This is a modified version of Chris’s which supports tables that are on different schemas).

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)
EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s