Collections are a critical part of SCCM 2012. They factor into almost everything from deployments to client settings, to… everything! It just so happens that collections are a unique breed of things that are easy to understand but difficult to master.
In part 1 of this series, Troubleshooting Slow Collection Evaluation in SCCM 2012 (AKA Laggy Collections), we discussed 3 issues you may see that impact the performance of your collections and how to resolve them. If you missed part 1, you can read it here.
Then, in part 2 of this series, Troubleshooting Slow Collection Evaluation in SCCM 2012 (AKA Rooting Out Issues With Collections), we looked under the hood with SQL/PowerShell code on how to identify the issues like missing direct memberships, how to identify which limiting collection is preventing a client from showing in your collection, and how to find a “reverse limiting collection update train” (if you are wondering what in the world that is, read part two here).
For our third session on collection update issues, we are going to look at a common issue – collection update times.
Identifying Update Types for Collections
In SCCM 2012 all collections have one of the following types of update schedules: None, Incremental, Scheduled, or Incremental & Scheduled.
Incremental collections update on a interval, that is configured in the Administration workspace in the Admin console. I’ve seen values for this as low as 5 minutes to 2 hours. Whatever the interval is, EVERY incremental collections does an update at once every x (whatever your value is) minutes. In a site with low resources, or a large number of incremental collections, this can cause havoc and result in collections taking a long time to update. Microsoft recommends no more than 200 incremental collections as a general rule.
To keep yourself from looking at hundreds of collections you can run the following query against your SCCM Site Database:
Select (Case when RefreshType = 1 then 'Manual'
when RefreshType = 2 then 'Scheduled'
when RefreshType = 4 then 'Incremental'
when RefreshType = 6 then 'Scheduled and Incremental'
else 'Unknown' end) as RefreshType, count(SiteID) as Collections
from v_Collections
group by RefreshType
You should get something similar to the following:
RefreshType | Collections
------------------------------------------------------------------
Manual | 17
Scheduled | 371
Incremental | 9
Scheduled and Incremental | 224
If you have more than 200 Incremental or Scheduled and Incremental added together, you need to work to reduce your number of incremental collections.
To find out which collections are which, run this:
Select SiteID, CollectionName,
(Case when RefreshType = 1 then 'Manual'
when RefreshType = 2 then 'Scheduled'
when RefreshType = 4 then 'Incremental'
when RefreshType = 6 then 'Scheduled and Incremental'
else 'Unknown' end) as RefreshType
from v_Collections
This will list out all your collections, with the refresh type.
Identifying Collection Schedule Clustering
Another issue you may see is where many collections are updating at the same time. In this case, it is useful to be able to determine the peak times for collection updates to see how bad it truly is.
This is when you wish you had a chart just like this one:
This chart shows the estimated count of collection updates over a week by hour. Both Scheduled and Incremental are broken out separately.
I created it using a PowerShell script that I will share with you at the end of this post. The script reads in all collections for a site, then predicts all the times the collections will update based on their collection schedule type and their last update time. The estimate is done for 7 days and the collections are grouped by hour.
The script dumps the data into two csv files, one for scheduled collections, and the other for incremental collections. I took the two CSV files and used Excel magic to create a nice clear graph so I could visualize my data. As you can see, things are pretty even, but there are a few spikes.
If you are wondering how I did this, or want to try it for yourself, grab the script and give it a try below. Let me know if you find any issues.
Function Get-CMHourlyCollectionCounts {
param(
[string]$CMDatabaseServer,
[string]$CMDatabaseName,
[string]$CMSiteCode
) #parameters
#region Set up environment
$StartDate = Get-Date
$NumDays = 8
$EndDate= $StartDate.AddDays($NumDays)
$GridInterval = 1
#Make Connection to SCCM 2012
Write-Verbose "Making connection to SCCM 2012"
#Modules
Import-Module "C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1" -Force
#Enter SCCM PSProvider
Set-Location "$($CMSiteCode):\"
#endregion
#region Build Schedule Results Grid
$Results = @{"$($StartDate.Date.ToString("MM-dd-yyyy")) $($StartDate.TimeofDay.ToString("hh")):00"=0}
$TestDate = $StartDate
Do {
$TestDate = $TestDate.AddHours($GridInterval)
$Results.Add("$($TestDate.Date.ToString("MM-dd-yyyy")) $($TestDate.TimeOfDay.ToSTring("hh")):00",0)
# Write-Host "$($TestDate.Date.ToString("MM-dd-yyyy")) $($TestDate.TimeOfDay.ToSTring("hh")):00"
$Output = $Output + $Item
} while ($TestDate -le $EndDate)
#endregion
#region Build Incremental Results Grid
$IncResults = @{"$($StartDate.Date.ToString("MM-dd-yyyy")) $($StartDate.TimeofDay.ToString("hh")):00"=0}
$TestDate = $StartDate
Do {
$TestDate = $TestDate.AddHours($GridInterval)
$IncResults.Add("$($TestDate.Date.ToString("MM-dd-yyyy")) $($TestDate.TimeOfDay.ToSTring("hh")):00",0)
# Write-Host "$($TestDate.Date.ToString("MM-dd-yyyy")) $($TestDate.TimeOfDay.ToSTring("hh")):00"
$Output = $Output + $Item
} while ($TestDate -le $EndDate)
#endregion
#region Process All Collections
$Collections = Invoke-Sqlcmd -ServerInstance $CMDatabaseServer -Database $CMDatabaseName -Query "Select SiteID, CollectionName, Schedule, EvaluationStartTime, RefreshType from v_Collections"
$IncrInt = Invoke-Sqlcmd -ServerInstance $CMDatabaseServer -Database $CMDatabaseName -Query "Select Name, Value3 as Value from vSMS_SC_Component_Properties where Name = 'Incremental Interval'"
Foreach ($Collection in $Collections) {
Write-Host "Processing: $($Collection.SiteID) - $($Collection.CollectionName)"
#Process Scheduled Collections
If ($Collection.RefreshType -eq 2 -or $Collection.RefreshType -eq 6) {
$Sched = Convert-CMSchedule -ScheduleString $Collection.Schedule
#Process Monthly by Date
if ($Sched.MonthDay -gt 0 -and $Sched.ForNumberOfMonths -gt 0) {
##Monthly by Date Schedule
$LastEval = $Collection.EvaluationStartTime
If ($LastEval.Day -eq $Sched.MonthDay -and $LastEval -ge $StartDate -and $LastEval -le $EndDate) {
If ($Results.ContainsKey("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00")) {
$Count = $Results.Get_Item("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00")
$Results.Set_Item("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00",($Count + 1))
}
} else {
$NextMonth = Get-Date -Date "$($LastEval.Month + 1)/1/$($LastEval.Year) $($Sched.StartTime.TimeOfDay.ToString())"
if ($NextMonth -ge $StartDate -and $NextMonth -le $EndDate) {
If ($Results.ContainsKey("$($NextMonth.Date.ToString("MM-dd-yyyy")) $($NextMonth.TimeOfDay.ToSTring("hh")):00")) {
$Count = $Results.Get_Item("$($NextMonth.Date.ToString("MM-dd-yyyy")) $($NextMonth.TimeOfDay.ToSTring("hh")):00")
$Results.Set_Item("$($NextMonth.Date.ToString("MM-dd-yyyy")) $($NextMonth.TimeOfDay.ToSTring("hh")):00",($Count + 1))
}
}
}
}
$LastEval = $Collection.EvaluationStartTime
If ($Sched.DaySpan -gt 0 -or $Sched.HourSpan -gt 0 -or $Sched.MinuteSpan -gt 0) {
Do {
$LastEval = $LastEval.AddDays($Sched.DaySpan)
$LastEval = $LastEval.AddHours($Sched.HourSpan)
$LastEval = $LastEval.AddMinutes($Sched.MinuteSpan)
If ($Sched.ForNumberOfWeeks -gt 0) {
$Days = (($Sched.Day + 1) * $Sched.ForNumberOfWeeks)
$LastEval = $LastEval.AddDays($Days)
}
If ($Sched.ForNumberOfMonths -gt 0) {
If ($LastEval.Day -ne $Sched.MonthDay) {
$LastEval = $LastEval.AddDays(1)
} else {
Continue
}
}
If ($LastEval -ge $StartDate -and $LastEval -le $EndDate) {
If ($Results.ContainsKey("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00")) {
$Count = $Results.Get_Item("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00")
$Results.Set_Item("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00",($Count + 1))
}
}
} while ($LastEval -le $EndDate)
} #End Processing Interval Schedules
} #End Processing Schedules
#Process Incremental Schedules
If ($Collection.RefreshType -eq 4 -or $Collection.RefreshType -eq 6) {
$LastEval = $Collection.EvaluationStartTime
Do {
$LastEval = $LastEval.AddMinutes($IncrInt.Value)
If ($LastEval -ge $StartDate -and $LastEval -le $EndDate) {
If ($IncResults.ContainsKey("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00")) {
$Count = $IncResults.Get_Item("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00")
$IncResults.Set_Item("$($LastEval.Date.ToString("MM-dd-yyyy")) $($LastEval.TimeOfDay.ToSTring("hh")):00",($Count + 1))
}
}
} while ($LastEval -le $EndDate)
} #End Processing Incremental Schedules
}
$Output = @()
Foreach ($Result in $Results) {
$FailedPkg = New-Object -TypeName PSObject
Add-Member -InputObject $FailedPkg -MemberType NoteProperty -Name PackageID -Value $Failure.PackageID
}
$Results.GetEnumerator() | Sort-Object -Property Name | Select-Object Name, Value | Export-CSV -Path c:\Windows\Temp\SchedResults.csv -NoTypeInformation
$IncResults.GetEnumerator() | Sort-Object -Property Name | Select-Object Name, Value | Export-CSV -Path c:\Windows\Temp\IncSchedResults.csv -NoTypeInformation
}
That concludes this 3-part series on troubleshooting slow collection evaluation in SCCM 2012! Still not clear on how to resolve these issues? I’m happy to talk with you directly. Contact Model to set up some time to chat and don’t hesitate to check out our services for a better idea of how we can help you!
