Share This Post

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)

clip_image001

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.

clip_image003

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

Filed under: ConfigMgr, SQL Server

Share This Post

Specializing in System Center Products: Configuration Manager 2007, Operations Manager 2007, Desired Configuration Management, SMS 2003, software distribution, ITMU, SUS & WSUS. Database products and solutions: Microsoft SQL Server 2005 & 2008, Business Intelligence Development Studio, Analysis Server, Reporting Services, Integration Services. Microsoft Windows Server 2003 & 2008, SharePoint, PerformancePoint, Active Directory, DNS, Group Policy, Security, VBScript, ASP, Visual Basic, WMI, HTML and XML.

1 Comment

  1. Great write-up! Any tips for manually grooming the indexes?

Leave a Reply