Question My Altiris Notification Server database is under performing (it runs slower). How can I defrag my database indexes to improve performance?
Update: A read-only Notification Server report (SQL Index Fragmentation.xml) is now available (see attachments on the right side of this article). The report will indicate if significant fragmentation exists. It does not require SQL knowledge to execute. Note: Be aware that running this report is very intense on SQL and should only be run off hours and not during any Notification Server maintenance windows.
For long term remediation, please utilize the SQL Server maintenance wizard to create a weekly maintenance task to perform an index rebuild.
Sample report screenshot
SQL query for Index Defrag The script provided below is primarily intended for a quick one-time fix. As indexes will re-fragment over time, a regularly scheduled maintenance task is the best solution.
Before running this script, back up your database (just in case); however, this script should not damage anything. It should only remove empty spaces in the database. Read the article about DBCC SHOWCONTIG (http://msdn2.microsoft.com/en-us/library/aa258803(SQL.80).aspx), and you will find more information about this script. The process will take few minutes, since the database is large, but if you want to run the defrag with less percentage of increase, change SELECT @maxfrag = 30.0 to 10.0 or something like that.
/* / Perform a 'USE <database name>' to select the database in which to run the script. */ -- Declare variables SET NOCOUNT ON DECLARE @tablename varchar (128) DECLARE @execstr varchar (255) DECLARE @objectid int DECLARE @indexid int DECLARE @frag numeric(8,3) DECLARE @maxfrag numeric(8,3)
-- Decide on the maximum fragmentation to allow -- Below is the percentage the index has to be fragmented -- Suggest to start at 30.0 (30%) SELECT @maxfrag = 30.0
-- Declare cursor DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' -- Do not scan common table indexes AND TABLE_NAME NOT LIKE 'xdl%' AND TABLE_NAME NOT LIKE 'Prf_%'
-- Loop through all the tables in the database FETCH NEXT FROM tables INTO @tablename
WHILE (@@FETCH_STATUS = 0) BEGIN -- Do the showcontig of all indexes of the table INSERT INTO #fraglist EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS') FETCH NEXT FROM tables INTO @tablename END
-- Close and deallocate the cursor CLOSE tables DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist WHERE LogicalFrag >= @maxfrag AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor OPEN indexes
-- loop through the indexes FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag