How to determine if the ConfigMgr Rebuild Indexes site maintenance task is running

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.)

ConfigMgr Settings

Check that the Site Maintenance task > Rebuild Index task is enabled (SCCM 2007 shown below)

Relevant ConfigMgr Logs

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.

List a count of Statistics by date last updated

This query (run on 4/20/2012) shows that the statistics are fairly recent. Change the database context to run against the ConfigMgr database.

USE SCCM

GO

SELECT

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;

StatisticsLastUpdated	Total
04/20/2013	19
04/19/2013	47
04/18/2013	45
04/17/2013	43
04/16/2013	23
04/15/2013	4
04/14/2013	2311
NULL	388

List the Index fragmentation

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).

USE SCCM

GO

— 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
CM_CAS	ClientOfferStatus	ClientOfferStatus_LastExecutionContext_idx	NONCLUSTERED INDEX	97.47675963	1483	1506
CM_CAS	RecentlyUsedApps_DATA	RecentlyUsedApps_DATA_AK	NONCLUSTERED INDEX	96.93813789	4695	4801
CM_CAS	Update_ComplianceStatus	Update_ComplianceStatus_Status_idx	NONCLUSTERED INDEX	87.37019366	3148	3563
CM_CAS	RecentlyUsedApps_DATA	recentapps_idx1	NONCLUSTERED INDEX	86.30353266	4435	5067
CM_CAS	RecentlyUsedApps_DATA	recentapps_idx2	NONCLUSTERED INDEX	84.42456283	2109	2459
CM_CAS	POWER_MANAGEMENT_SETTINGS_DATA	POWER_MANAGEMENT_SETTINGS_DATA_AK	NONCLUSTERED INDEX	81.32372215	1249	1526
CM_CAS	Update_ComplianceStatus	Update_ComplianceStatus_CI_ID_Status_idx	NONCLUSTERED INDEX	78.53123241	2826	3554
CM_CAS	Update_ComplianceStatus	Update_ComplianceStatus_PK	CLUSTERED INDEX	76.04695987	4427	5707
CM_CAS	POWER_MANAGEMENT_DAY_DATA	POWER_MANAGEMENT_DAY_DATA_PK	CLUSTERED INDEX	70.80573951	2604	3624
CM_CAS	ClientOfferStatus	ClientOfferStatus_PK	CLUSTERED INDEX	70.20491803	1751	2440
CM_CAS	PNP_DEVICE_DRIVER_DATA	PNP_DEVICE_DRIVER_DATA_AK	NONCLUSTERED INDEX	57.51432349	1358	2269
CM_CAS	Services_HIST	Services_HIST_PK	CLUSTERED INDEX	50.30467163	1532	2954
CM_CAS	StatusMessages	StatusMessages_MessageID_idx	NONCLUSTERED INDEX	34.99610288	950	2566