Use this task to rebuild the Configuration Manager database indexes. An index is a database structure that's created on a database table to speed up data retrieval. For example, searching an indexed column is often much faster than searching a column that isn't indexed.
Why you should not use the ConfigMgr rebuild index task
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.
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.
To run the task you must first of all select the type of optimisation you want to run. Ola has these listed out on his site ( -server-index-and-statistics-maintenance.html), the one I use on my own environment is the following;
What does this mean for your database, this potentially mean that you have a series of indexes that are higher than the recommended percent. On my opinion the indexes should not be higher than 30%, so in this example the overall percentage of your database is on the 99%.
A key performance consideration with SQL is to keep the database indexes up to date and the database fragmentation levels down. Microsoft has included built-in maintenance tasks that can take care of some of this. For example, the rebuild indexes task as pictured below.
However, there is a better way! Microsoft MVP Ola Hallengren has published a script that undertakes all of the key performance maintenance tasks. By default this script will set up SQL jobs that you can schedule to run tasks such as integrity checking, re-indexing and backing up all databases within the SQL instance.
To view the maintenance tasks from database, run the SQL query Select * from SQLTaskStatus against the Configuration Manager database. The output should return all maintenance tasks available on the site.
I eventually got my issue worked out...although it may not have been the best course of action to take nor am I completely 100% sure what I did was the "fix". What I ended up doing was running the re-index and re-organize tasks directly from the SQL Management studio on the SCCM databases. And I actually re-ran these same tasks multiple times in a row a handful of times. The next day I had then noticed that the results from the All Software Updates and specific Update Groups were returning their results almost immediately. From there I went ahead and enabled the SCCM re-index task to run quite a bit more frequently that what I had it set to previously.
Thanks for the response Jason. I had the re-index maintenance task enabled, but only for one day a week. I've since bumped that up to a few times, with one of them being tonight, so I'll see what happens tomorrow after it runs.
Task Rebuild Indexes is due now. SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Executing Rebuild Indexes SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)STATMSG: ID=2408 SEV=I LEV=M SOURCE="SMS Server" COMP="SMS_DATABASE_NOTIFICATION_MONITOR" SYS=(Server Name Inserted Here) SITE=123 PID=10744 TID=7468 GMTDATE=Fri Nov 30 06:00:05.133 2012 ISTR0="Rebuild Indexes" ISTR1="" ISTR2="" ISTR3="" ISTR4="" ISTR5="" ISTR6="" ISTR7="" ISTR8="" ISTR9="" NUMATTRS=0 SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)CTriggerManager::ExecuteTask - beginning Rebuild Indexes process. SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)SQL MESSAGE: - String data, right truncation SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Searching for indexes to rebuild. SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)RCV: UPDATE on SiteControl for SiteControl_AddUpd_HMAN [ITC ][72057594039087739] SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 10752 (0x2A00)RCV: UPDATE on SiteControl for SiteControl_AddUpd_SiteCtrl [ITC ][72057594039087740] SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 10752 (0x2A00)SND: Dropped D:\Program Files\Microsoft Configuration Manager\inboxes\hman.box\ITC.SCU [72057594039087739] SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 10752 (0x2A00)SND: Dropped D:\Program Files\Microsoft Configuration Manager\inboxes\sitectrl.box\ITC.CT0 [72057594039087740] SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 10752 (0x2A00)Found 410 indexes. SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.CI_AssignmentStatus.CI_AssignmentStatus_ItemKey_idx, fragmentation=99% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.CI_Contents.CI_Contents_AK, fragmentation=99% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.IDE_Controller_DATA.IDE_Controller_DATA_AK, fragmentation=99% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.ResPolicyCollMap.ResPolicyCollMap_CollectionID_idx, fragmentation=99% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.Update_ComplianceStatus.Update_ComplianceStatus_Machine_idx, fragmentation=98% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.Update_ComplianceStatus.Update_ComplianceStatus_Status_idx, fragmentation=98% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)RCV: UPDATE on SiteControlNotification for SiteCtrlNot_Add_DDM [ITC ][72057594039087741] SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 10752 (0x2A00)Site Control Notification. SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 10752 (0x2A00)SND: Dropped D:\Program Files\Microsoft Configuration Manager\inboxes\ddmnotif.box\ITC.SCA [72057594039087741] SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 10752 (0x2A00)Reindexing index dbo.ResPolicyMap.ResPolicyMap_PADBID_MachineID_idx, fragmentation=97% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.Update_ComplianceSummary.Update_ComplianceSummary_LastSummaryTime_idx, fragmentation=97% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.ClientKeyData.ClientKeyData_Thumbprint_AgentType_ParentID_idx, fragmentation=96% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.Update_ComplianceStatus.Update_ComplianceStatus_CI_ID_Status_idx, fragmentation=96% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.ResPolicyChange.ResPolicyChange_PK, fragmentation=95% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.CI_ConfigurationItems.CI_ConfigurationItems_CI_UniqueID_idx, fragmentation=94% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.Flat_Group_System_Relationship.Flat_Group_System_Relationship_System_ObjectGUID_idx, fragmentation=94% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.ResPolicyCollMap.ResPolicyCollMap_PADBID_idx, fragmentation=94% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.System_DISC.System_DISC_Netbios_Name0_idx, fragmentation=94% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.User_DISC.User_DISC_Object_GUID0_idx, fragmentation=94% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.CollectionMembers.CollectionMembers_IsClient_idx, fragmentation=93% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.CollectionMembers.CollectionMembers_SiteID_IsClient_idx, fragmentation=93% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.CollectionMembers.CollectionMembers_SiteID_MachineID_idx, fragmentation=93% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.SoftwareInventoryStatus.SoftwareInventoryStatus_PK, fragmentation=93% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)Reindexing index dbo.System_AUX_Info.System_AUX_Info_Netbios_Name0_idx, fragmentation=93% SMS_DATABASE_NOTIFICATION_MONITOR 1/1/1601 12:00:00 AM 7468 (0x1D2C)
Expand the Management node and then right-click on Maintenance Plans. Select Maintenance Plan Wizard. Give the plan a name and schedule if desired, select what task (in this case Rebuild Index), and then pick your SCCM database to run it on. You can select the checkbox to Keep index online while reindexing if you're running SQL Enterprise and not Standard. Once that's created just execute it whenever you'd like or let it go according to the schedule you created, if you created one.
We had our DBA update the stats on the tables after site recovery, when experiencing this issue "execsp_updatestats"which restored the performance. The CM re-index maintenance task was enabled and running successfully, but made no difference to the performance when it was in this state. Definitely worth trying this first if experiencing the same issue after a site restore. 2ff7e9595c
Comments