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

Advertisements
This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

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

  1. Pingback: Talking Database II - The Config Ninja Blog - Site Home - TechNet Blogs

  2. Pingback: Why you should not use the ConfigMgr rebuild index task | Steve Thompson [MVP]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s