An in-depth look at grooming in System Center Service Manager: Part 1

~ Scott Walker | Senior Support Escalation Engineer

HOWThis post is the first in a three part series on grooming in System Center 2012 Service Manager (SCSM 2012 and SCSM 2012 R2). As we all know, grooming is the process of tidying up, and this is just what grooming does in Service Manager.

Note: Part 2 can be found here. Part 3 is here.

The CMDB in Service Manager is a place where a lot of work goes on behind the scenes to provide the various classes, relationships, and methods needed to automate your IT Service Management best practices.  As you might imagine, all this behind the scenes work requires creating temp tables, entering data that is used just for tracking, keeping tabs on points in time, as well as many other things.  All of this behind-the-scenes work needs to be tidied up from time to time in order to optimize performance and growth of the CMDB, and this is where grooming comes in. Service Manager employs several grooming jobs to get the job done and we’ll discuss these throughout the series.

Now you might be asking yourself “Why do I even need to know about this grooming stuff?” As a Service Manager admin, chances are you’ll encounter the grooming processes sooner or later, and just because all of this is happening in the background doesn't mean you shouldn't care about it.  For example, if grooming jobs start to fail, ultimately you may see performance issues in the Service Manager environment. Performance issues that can cause slowness doing mundane tasks in the console, delays when running workflows, and a possible list of other performance (slowness) related problems.  When grooming jobs consistently fail, the Service Manager database can become bloated in several key tables which eventually results in these performance troubles.  And once many of these key tables become very large, it's not always easy to clear up. In some cases, if things get bad enough then getting things cleaned up may, at the very least, require a support case for assistance.  As a Service Manager admin, if you're having a grooming related issue you want to know about it as soon as possible so you can address it before you start experiencing performance problems.

Getting Started

A good place to start our investigation into Service Manager grooming is in the InternalJobHistory table.  From that table, I've compiled a list of Stored Procedures that are run regularly to perform various grooming tasks.  We'll take a look at how these are used coming up.

This query returns a list of grooming commands that are performed regularly.  For now, I've removed the parameters passed and the duplicates to show just the stored procedures:

 

command 
Exec dbo.p_DataPurging 
Exec dbo.p_GroomStagedChangeLogs 
Exec dbo.p_GroomSubscriptionSpecificECLRows  
Exec dbo.p_GroomTypeSpecificLogTables  
Exec dbo.p_GroomPartitionedObjects and dbo.p_Grooming 
Exec dbo.p_GroomChangeLogs 
Exec dbo.p_GroomManagedEntity 

 

For a more detailed look at how these are executed, let's take a look at one of the Managed Type tables in the CMDB.

 

SELECT 

StoredProcedure 

,Criteria 

,RetentionPeriodInMinutes 

,BatchSize 

FROM dbo.MT_GroomingConfiguration 

 

 

StoredProcedure 
Criteria 
RetentionPeriodInMinutes 
BatchSize 
p_GroomManagedEntity 
SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_System$WorkItem$ReleaseRecord R ON R.BaseManagedEntityId = BME.BaseManagedEntityId WHERE BME.[IsDeleted] = 0 AND BME.[LastModified] < @Retention AND BME.BaseManagedTypeId = @TargetTypeId AND R.Status_F7BFD782_80B2_10C2_04B3_7F4C042DB5D2 = '221155FC-AD9F-1E40-C50E-9028EE303137' 
525600 
1000 
p_GroomChangeLogs 
 
525600 
1000 
p_GroomSubscriptionLogs 
 
0 
1000 
p_GroomManagedEntity 
SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_System$WorkItem$Problem P ON P.BaseManagedEntityId = BME.BaseManagedEntityId WHERE BME.[IsDeleted] = 0 AND BME.[LastModified] < @Retention AND BME.BaseManagedTypeId = @TargetTypeId AND P.Status_3C8876F4_FCBF_148C_FBAF_4CF4F02C6187 = '25EAC210-E091-8AE8-A713-FEA2472F32FF' 
525600 
100 
p_GroomManagedEntity 
SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_System$WorkItem$Incident I ON I.BaseManagedEntityId = BME.BaseManagedEntityId WHERE BME.[IsDeleted] = 0 AND BME.[LastModified] < @Retention AND BME.BaseManagedTypeId = @TargetTypeId AND I.Status_785407A9_729D_3A74_A383_575DB0CD50ED = 'BD0AE7C4-3315-2EB3-7933-82DFC482DBAF' 
129600 
100 
p_GroomStagedChangeLogs 
 
0 
1000 
p_GroomManagedEntity 
SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_System$WorkItem$ServiceRequest SR ON SR.BaseManagedEntityId = BME.BaseManagedEntityId WHERE BME.[IsDeleted] = 0 AND BME.[LastModified] < @Retention AND BME.BaseManagedTypeId = @TargetTypeId AND SR.Status_6DBB4A46_48F2_4D89_CBF6_215182E99E0F = 'C7B65747-F99E-C108-1E17-3C1062138FC4' 
525600 
1000 
p_GroomManagedEntity 
SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_System$WorkItem$ChangeRequest C ON C.BaseManagedEntityId = BME.BaseManagedEntityId WHERE BME.[IsDeleted] = 0 AND BME.[LastModified] < @Retention AND BME.BaseManagedTypeId = @TargetTypeId AND C.Status_72C1BC70_443C_C96F_A624_A94F1C857138 = 'F228D50B-2B5A-010F-B1A4-5C7D95703A9B' 
525600 
100 

 

Now, some of the entries in InternalJobHistory will make more sense, so let's take a look at the full commands:

SELECT DISTINCT command  

FROM dbo.InternalJobHistory

 

command 
Exec dbo.p_GroomManagedEntity E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C, 525600, SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_Sy, 100 
Exec dbo.p_DataPurging 
Exec dbo.p_GroomStagedChangeLogs 55270A70-AC47-C853-C617-236B0CFF9B4C, 0, , 1000 
Exec dbo.p_GroomManagedEntity D02DC3B6-D709-46F8-CB72-452FA5E082B8, 525600, SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_Sy, 1000 
Exec dbo.p_GroomSubscriptionSpecificRECLRows 55270A70-AC47-C853-C617-236B0CFF9B4C, 0, , 1000 
Exec dbo.p_GroomManagedEntity 04B69835-6343-4DE2-4B19-6BE08C612989, 525600, SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_Sy, 1000 
Exec dbo.p_GroomSubscriptionSpecificECLRows 55270A70-AC47-C853-C617-236B0CFF9B4C, 0, , 1000 
Exec dbo.p_GroomTypeSpecificLogTables  
Exec dbo.p_GroomPartitionedObjects and dbo.p_Grooming 
Exec dbo.p_GroomManagedEntity A604B942-4C7B-2FB2-28DC-61DC6F465C68, 129600, SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_Sy, 100 
Exec dbo.p_GroomChangeLogs 55270A70-AC47-C853-C617-236B0CFF9B4C, 525600, , 1000 
Exec dbo.p_GroomManagedEntity 422AFC88-5EFF-F4C5-F8F6-E01038CDE67F, 525600, SELECT TOP(@NumOfEntities) BME.[BaseManagedEntityId] FROM dbo.[BaseManagedEntity] BME JOIN dbo.MT_Sy, 100 

Now we can see that many of the Stored Procedures are invoked with a list of parameters.  The first parameter is often a GUID.  This GUID is a Service Manager Managed Type, so to find out what is being groomed let's take a look at the stored procedure p_GroomManagedEntity. The first one in the list is passing the first parameter E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C but in this list we can see several entries for p_GroomManagedEntity so let's just see what all of them are:

SELECT TypeName  

FROM ManagedType 

WHERE ManagedTypeID IN  

('E6C9CF6E-D7FE-1B5D-216C-C3F5D2C7670C' 

,'D02DC3B6-D709-46F8-CB72-452FA5E082B8' 

,'04B69835-6343-4DE2-4B19-6BE08C612989' 

,'A604B942-4C7B-2FB2-28DC-61DC6F465C68' 

,'422AFC88-5EFF-F4C5-F8F6-E01038CDE67F') 

These are the various Managed Types being groomed by p_GroomManagedEntity:

 

TypeName 
System.WorkItem.ReleaseRecord 
System.WorkItem.Incident 
System.WorkItem.ServiceRequest 
System.WorkItem.ChangeRequest 
System.WorkItem.Problem 

For the rest of the parameters being passed, refer back to the results from the MT_GroomingConfiguration table.  The second parameter being passed is RetentionPeriodInMinutes, the third parameter is the Criteria, followed by the BatchSize. These procedures are often run against a large set of data, so to keep table locking to a minimum, the grooming is done in batches.

Question: Where does the GroomingConfiguration table get its information?  

Answer: From the Service Manager Grooming Configuration Management Pack (ServiceManager.Grooming.Configuration.mp).

We can break down the other Stored Procedures in a similar manner and see, for instance, that p_GroomStagedChangedLogs and GroomChangeLogs both tidy up System.Entity Managed Types, and so on.

This should give us a good basis for understanding how some of these grooming tasks are run. In the next post we’ll take a look at InternalJobHistory a bit more in-depth.

Scott Walker | Senior Support Escalation Engineer | Microsoft GBS Management and Security Division

Get the latest System Center news on Facebook and Twitter:

clip_image001 clip_image002

System Center All Up: http://blogs.technet.com/b/systemcenter/

Configuration Manager Support Team blog: http://blogs.technet.com/configurationmgr/ 
Data Protection Manager Team blog: http://blogs.technet.com/dpm/ 
Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/ 
Operations Manager Team blog: http://blogs.technet.com/momteam/ 
Service Manager Team blog: http://blogs.technet.com/b/servicemanager 
Virtual Machine Manager Team blog: http://blogs.technet.com/scvmm

Microsoft Intune: http://blogs.technet.com/b/microsoftintune/
WSUS Support Team blog: http://blogs.technet.com/sus/
The RMS blog: http://blogs.technet.com/b/rms/
App-V Team blog: http://blogs.technet.com/appv/
MED-V Team blog: http://blogs.technet.com/medv/
Server App-V Team blog: http://blogs.technet.com/b/serverappv
The Surface Team blog: http://blogs.technet.com/b/surface/
The Application Proxy blog: http://blogs.technet.com/b/applicationproxyblog/

The Forefront Endpoint Protection blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/

SCSM 2012 R2