System Center Blog

6 MIN READ

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

Former Employee

Feb 16, 2019

First published on TECHNET on Nov 16, 2015

~ Scott Walker | Senior Support Escalation Engineer

This 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 I nternalJobHistory 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 :