200
^^^^ That is a number that I think almost every ConfigMgr administrator has heard at some point. It is an often quoted value for the ‘suggested max number of collections that have incremental updates enabled.’
This is of course given the qualifier of it depends. It is true that a multitude of factors contribute to how many incrementally updating collections is right for your environment. While I do not have a complex formula for the IOPS requirement for X collections in Y seconds based on Z queries, I DO have some useful information. (I am also going to apologize in advance for my SQL, I do not apologize for my MS Paint skills though)
Why 200?
I do not know why Microsoft initially provided a value of 200 incremental collections. But what I do know is, if your sites ‘Incremental Interval’ is shorter than the cumulative time it takes for all your incremental collections to evaluate you are going to have a bad time. The below SQL query can show you if you suffer from this. The ‘Interval > EvalTime’ column simply returns a True or False based on if the collective evaluation time for all incremental collections is more than your sites ‘Incremental Interval.’
SELECT ste.SiteName
, srv.SiteCode
, cmp.Value3 AS [IncrementalRefreshInterval]
, CONVERT(time(0),DATEADD(millisecond,evl.EvalLength,0)) AS [hh:mm:ss EvalTime]
, CASE WHEN evl.EvalLength/1000.0/60 < cmp.Value3 THEN ‘True’ ELSE ‘False’ END AS [Interval > EvalTime]
FROM dbo.SC_Component_Property cmp
INNER JOIN dbo.ServerData srv
ON cmp.SiteNumber = srv.ID
INNER JOIN dbo.Sites ste
ON srv.SiteCode = ste.SiteCode
INNER JOIN (
SELECT lcl.SiteNumber
, SUM(lcl.IncrementalEvaluationLength) AS [EvalLength]
FROM dbo.Collections_L lcl
INNER JOIN dbo.v_Collection col
ON lcl.CollectionID = col.CollID
WHERE col.RefreshType IN (4,6)
GROUP BY lcl.SiteNumber
) evl
ON cmp.SiteNumber = evl.SiteNumber
WHERE cmp.Name = ‘Incremental Interval’
AND cmp.SiteNumber = dbo.fnGetSiteNumber();
GO
Edit:1/19/19 – SQL Query is now @SqlBenjamin approved. He was kind enough to make the query work in a hierarchy (Makes me feel like I need a lab with a hierarchy now), and make it a bit spiffier!
If you don’t feel like running a SQL query you can also check your interval (and adjust it) by going to the below area in your console.
And if you’d prefer, you can check your total Incremental Evaluation time by using CEViewer, which is part of the Configuration Manager Toolkit. Yes, the 2012 R2 toolkit is the one you want.
Note: I do typically see a small discrepancy between the SQL queries and CEViewer’s eval times. I’ve seen them match up perfectly on some executions, and other times they are not quite 100% accurate with a variation of a handful of milliseconds per collection. I am unable to find any other reference to the length of time an Incremental Refresh takes outside of [dbo].[Collections_L] so that is what I’m using.
So?
So, you can see above that we have a little over 200 collections marked for incremental updates. We’ve also been able to maintain a total evaluation time of 4 minutes and 14 seconds. This is great! On top of my incremental evaluation completing, I’ve got room to spare. Always a good idea to leave some extra time for evaluation in the event of a large number of new devices or other scenarios that can cause a spike in collection evaluation time. Ironically I’ve got around 200 collections within the default interval. Maybe Microsoft does know what they are talking about! Maybe it is a coincidence, maybe it’s Maybelline.
An important note with all of this is, we have options.
If you want to adjust your incremental interval to 10 minutes, and cram in 500 collections, that’s up to you! Or maybe you have a smaller number of clients and you can fit 500 collections into 5 minutes, that’s fine! Maybe you have some really ugly collection queries and you can fit 3 in a 5 minute interval…. well… fix that.
Just be cognizant of your cumulative run time vs your interval.
Cody Mathis
Add comment