I’ve been fighting with a DotNetNuke install hosted on Azure for a while. We’ve been testing as there’s a lot we like about Azure but the performance when editing DotNetNuke has caused us to go a different route…but that’s another story. In our test site, the SQL database size just kept growing. To find out the culprit, I wanted to know the size of each table. This post from Alexandre Brisebois did just what I needed. My only tweak was to Order By the size.
SELECT o.name AS [table_name], sum(p.reserved_page_count) * 8.0 / 1024 / 1024 AS [size_in_gb], p.row_count AS [records] FROM sys.dm_db_partition_stats AS p, sys.objects AS o WHERE p.object_id = o.object_id AND o.is_ms_shipped = 0 GROUP BY o.name , p.row_count ORDER BY size_in_gb DESC
For me, it was pretty clear that I needed to truncate the DotNetNuke EventLog and ScheduleHistory. In production, you would want to schedule this as it’s amazing how quickly these can grow. We hadn’t put this site into production yet but the EventLog was 20 GB after just a few months.
truncate table EventLog truncate table ScheduleHistory
This may or may not be related to anything you need, but for me in this SQL Azure situation, I noticed that even though I had truncated about 20 GB’s of data the Azure Dashboard didn’t reflect that. Digging around it seems to be related to my indexes being fragmented. So, following Dilkush Patel’s post I ran this query to see my fragmentation. (Note: I did make a minor change to Order By the % 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 ips.avg_fragmentation_in_percent desc, ps.object_id, ps.index_id
For me, I had several tables over 60% fragmented. So I ran Dilkush’s script:
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 to rebuild') EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD') End Catch FETCH NEXT FROM TableCursor INTO @TableName END CLOSE TableCursor DEALLOCATE TableCursor
However, after running this (several times in fact) the usage in my Azure Dashboard has not changed. I’ll wait and see if it changes and update this post if it does.