Background (bug previously filed on connect)
I’ve found that the indexes on our SQL Server instances for Configuration manager to be severely fragmented. In investigating, I discovered that even though the Rebuild Indexes site maintenance task was set to run 1x /week, it never ran. Our SQL Server installations are remote on the CAS and our largest primary site, so don’t know if that is a factor. It did not work for us at ConfigMgr 2012 RTM, or at SP1 (where are now). Other MVPs have reported this issue as well. (Update: I have seen this fail to run on SCCM 2007 sites as well. However, it is not consistent in when it fails to run, Microsoft closed this bug as unable to reproduce.)
Check that the Site Maintenance task > Rebuild Index task is enabled (SCCM 2007 shown below)
If this task is enabled, the SMSDBMON.LOG should show signs that this task was actually invoked. Scan the SMSDBMON.LOG or SMSDBMON.LO_ files using your favorite log file reader; search on the key word Indexes. If this task is indeed working, you should see entries in the log file indicating that indexes are actually being rebuilt. On numerous sites where the task was not being invoked, the executing phase was not present.
Queries to check for up-to-date Statistics and Indexes
If you suspect that the site maintenance, rebuild index task is not being run, here are a couple of queries to check.
This query (run on 4/20/2012) shows that the statistics are fairly recent. Change the database context to run against the ConfigMgr database.
CONVERT(varchar(25), STATS_DATE(s.[object_id], s.stats_id), 101) AS StatisticsLastUpdated,
COUNT(*) As Total
FROM sys.stats s
JOIN sys.stats_columns sc ON sc.[object_id] = s.[object_id] AND sc.stats_id = s.stats_id
WHERE OBJECTPROPERTY(s.OBJECT_ID,’IsUserTable’) = 1
AND (s.auto_created = 1 OR s.user_created = 1)
GROUP BY CONVERT(varchar(25), STATS_DATE(s.[object_id], s.stats_id), 101)
ORDER BY 1 DESC;
This query will provide a list of all indexes with a page count > 1500, sorted by the amount of index fragmentation. A large number of indexes with > 40 % fragmentation is a strong indicator that the site index task is not running.
Sample output from our CAS, showing index fragmentation and prior to implementing database optimization (edited to fit).
– Get fragmentation info for all indexes above a certain size in the current database
– Note: This could take some time on a very large database
SELECT DB_NAME(database_id) AS [Database Name], OBJECT_NAME(ps.OBJECT_ID) AS [Object Name],
i.name AS [Index Name], ps.index_id, index_type_desc,
avg_fragmentation_in_percent, fragment_count, page_count
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL, NULL, NULL ,N’LIMITED’) AS ps
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON ps.[object_id] = i.[object_id] AND ps.index_id = i.index_id
WHERE database_id = DB_ID()
AND page_count > 1500
ORDER BY avg_fragmentation_in_percent DESC OPTION (RECOMPILE);
|Database Name||Object Name||Index Name||index_type_desc||avg_fragmentation_in_percent||fragment_count||page_count|