Question
My Altiris Notification Server database is under performing (it runs slower). How can I defrag my database indexes to improve performance?
Answer
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_%'
-- Create the table
CREATE TABLE #fraglist ( ObjectName varchar (255), ObjectId int, IndexName varchar (255), IndexId int, Lvl int, CountPages int, CountRows int, MinRecSize int, MaxRecSize int, AvgRecSize int, ForRecCount int, Extents int, ExtentSwitches int, AvgFreeBytes int, AvgPageDensity int, ScanDensity numeric(8,3), BestCount int, ActualCount int, LogicalFrag numeric(8,3), ExtentFrag numeric(8,3) )
-- Open the cursor
OPEN tables -- 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 WHILE (@@FETCH_STATUS = 0) BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ', ' + RTRIM(@indexid) + ') - fragmentation currently ' + RTRIM(CONVERT(varchar(15),@frag)) + '%' SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')' EXEC (@execstr) FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
END -- Close and deallocate the cursor
CLOSE indexes DEALLOCATE indexes -- Delete the temporary table
DROP TABLE #fraglist GO |
If you are using SQL 2005, please refer to KB for an updated method of accomplishing this:
How can I reorganize or rebuild my Altiris database indexes on a SQL 2005 server for improved performance?
http://www.symantec.com/business/support/index?page=content&id=HOWTO4644