First let’s find size of all tables in database, most of the people use database size feature of SQL Azure management portal to find out size , I would say instead use SQL Query to find perfect number.

Query:

select obj.name, sum(reserved_page_count) * 8.0 as "size in KB" from sys.dm_db_partition_stats part, 
sys.objects obj where part.object_id = obj.object_id group by obj.name

This query gives size of each table in given database.

Connect to database in concern and run this query.

select sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats

This query gives size of overall database… In case you are not interested in size of each table!

Now next task is finding out % of fragmentation…

For that also we have query which uses SQL Azure DMV’s to find out % of fragmentation

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

Running this query will give you result something like…

So it gives,

1)      Database Name

2)      Table Name

3)      Index Name

4)      Index type

And

5)      % of Fragmentation for that index

Any index which shows more than 10% of fragmentation needs rebuild or reorganization of index.

If fragmentation is less than 30% we can go with reorganization of index and if fragmentation is more than 30% then we need to rebuild indexes.

When we rebuild indexes we can do it with ONLINE=ON option which will keep table live while rebuilding happens, it will slow down overall rebuilding process.

There is a limitation that when we have any table which has columns which has large data like

varchar(50) varbinary(max) etc , we cannot do rebuild operation with ONLINE=ON .

For such table when we do rebuild of indexes table won’t be accessible.

Now question is how to do rebuild or reorganization of indexes…?

We have query for that also…

Query:

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

You need to connect to database which is having fragmentation problem and run this query, it will do rebuild on all tables present in database.

If it is possible to rebuild indexes while keeping database online it will do that else it will run without online=on option.

Output of this query will be something like,

Once this query finishes executing run query to find database size again and see the magic!

Send some gifts if this blog help you in saving some money!!! J

Query to find fragmentation on specific database and specific table

SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'<Database name>'), OBJECT_ID(N'<table name>'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO