ConfigMgr 2012 R2 – Collection Evaluation and SQL TempDb

 

Today we are going to see about the relation between Collection Evaluation and TempDb in SQL. Before we proceed I would like you to have a look at my previous blog to understand the various types of Collection Evaluations in ConfigMgr 2012.

Hope you are done reading my previous post. Let’s move on.

 

When a collection in ConfigMgr runs an evaluation, it uses the TempDb to create some temporary tables to process to insert the data based on what query we are using in Collection Rules. When I say Query, there are two types. WQL (WMI Query Language) and SQL (Structured Query Language).

In ConfigMgr we use the above two types. WQL is used by SMS Provider and SQL for Microsoft SQL Server.

Here is an example of a WQL and its equivalent SQL query for a Test collection created in ConfigMgr 2012 R2 to return all active clients.

 

WQL: select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.Active = "1"

SQL:   select all SMS_R_SYSTEM.ItemKey,SMS_R_SYSTEM.DiscArchKey,SMS_R_SYSTEM.Name0,SMS_R_SYSTEM.SMS_Unique_Identifier0,SMS_R_SYSTEM.Resource_Domain_OR_Workgr0,SMS_R_SYSTEM.Client0 from vSMS_R_System AS SMS_R_System where SMS_R_System.Active0 = N'1'

 

Above two statements looks similar. But they are not. You cannot run the WQL query in from SQL Management Studio. But you can execute the SQL statement.

You can see the WQL and SQL equivalent queries for any collection in SQL by running the below query. (Note: Don’t modify anything directly to table in SQL. This will impact the AdminUI functionality) Replace ‘P0000012’ with your collection ID.

 

select * from Collection_Rules_SQL Where CollectionID in(select CollectionID from Collections where SiteID = 'P0000012')

   

When will the TempDb size grow and what is the impact with Collection Evaluation?

There are certain components in ConfigMgr uses TempDb. The important one is Collection Evaluator. Collection Evaluator is Single Threaded and run one collection evaluation at a time. The reason for this is that the next collection in the Evaluation Graph may be directly or indirectly depend on the collection getting evaluated.

By Default, the TempDb size is configured as below. But SQL Administrators, may have changed based on their required settings.

 

If we are using an Expensive query in a Collection with lot of joins and complex where clauses, it may start filling up the TempDb.

We are most likely to use “LIKE” and “NOT LIKE” statements in Collections to get our desired objects in the collection. According to the MSDN article “NOT LIKE” statements are Expensive and avoid this to improve SQL performance.

You can run the below Statement in SQL to identify what are all the collections in your environment really has “LIKE” and “NOT LIKE” in the queries.

select SiteID, CollectionName from Collections where CollectionID in(select CollectionID from Collection_Rules_SQL where SQL like '%Like%' or SQL like '%NOT LIKE%')

 You will not see big Impact when you manage small number of systems with less number of collections. That’s why the collection query may work in your Test environment and may not give the expected result in Production environment.

When the Site is growing bigger and bigger, we will get lot of Data from clients and that puts lot of pressure in SQL.

 

Senthilkumar Pandurangan

Support Escalation Engineer | Microsoft System Center Configuration Manager.

Disclaimer: This posting is provided "AS IS" with no warranties and confers no rights

Comments

• Anonymous
  January 01, 2003
  Neatly explained..... Thanks SKP
• Anonymous
  February 13, 2015
  Thanks SP
• Anonymous
  February 13, 2015
  Thanks
  appreciated
• Anonymous
  February 13, 2015
  Actually, by default, TempDB is set to 8MB. Recommendation is TempDB should be approximately 25-30% the size of ConfigMgr 2012. http:stevethompsonmvp.wordpress.com