Mar 19

Written by: willgillen
3/19/2007

If you have ever run a DotNetNuke site for many months, or even several years, you've probably noticed that the SQL Database can start to underperform after a lot of traffic and updates.  At first, you might think that it is because so much new data has been entered, and so many changes have been made so you 'expect' it to run a little slower.  Well, in most instances this is not the case.

One thing that could be happening (as happens on most SQL databases after a given time with lots of inserts and deletes) is that the database's table indexes can become bloated and fragmented.  This can cause severe performance problems (especially if it starts happening on tables that are frequently hit during normal processing).  When these table indexes become fragmented, the SELECT queries start taking longer and longer to find the right records, because the index (like a phone book) become out of sync with the data.

One way to get this back under control is to rebuild the indexes periodically so that the indexes are fresh.  This makes all your queries run much faster (if of course the indexes are setup correct in the first place ;)  We actually worked with a client recently who was having major performance problems with the Forums module in DotNetNuke about once a day.  This forum was being hit very heavily, and after some sleuthing we found that the indexes on the forum tables were getting wayyyyyyy fragmented during the day.  So, we setup a scheduled job on SQL to rebuild the indexes once a night, and the performance has been miraculous since. 

Now in most situations, rebuilding the indexes daily is probably not necessary, but you should rebuild at least once a week or every other week.  You can use this script either directly, or put it in a stored proc and schedule it to run periodically to rebuild your SQL indexes.  Remember, that while you can run this process with the DB live, it is NOT RECOMMENDED.  It will slow things down while it is running.  Instead, schedule it to run in OFF PEAK hours.

USE DotNetNuke
DECLARE @ATable sysname
DECLARE @indid int
DECLARE cur_table CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table'
OPEN cur_table
FETCH NEXT FROM cur_table INTO @ATable
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cur_ind CURSOR FOR
SELECT indid FROM sysindexes WHERE id = OBJECT_ID (@ATable) and keycnt > 0
OPEN cur_ind
FETCH NEXT FROM cur_ind INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
  IF @indid <> 255 DBCC INDEXDEFRAG (DotNetNuke, @ATable, @indid)
  FETCH NEXT FROM cur_ind INTO @indid
END
CLOSE cur_ind
DEALLOCATE cur_ind
  FETCH NEXT FROM cur_table INTO @ATable
END
CLOSE cur_table
DEALLOCATE cur_table

Once you get this script scheduled, you'll wonder why you didn't do this before!

Let me know if you have better scripts for rebuilding / defraging indexes on MSSQL.

-- W.G.

Tags: