Hello All
Just want to ask if anyone has seen this funky issue:
We went from SCCM 2012 R2 -> 1511 -> 1606 Rollup 1 - (went to 1606 first then 1
week later we applied Rollup 1) recently and after it was complete - We noticed
the packages were taking much longer than expected to enumerate.
Here is our Environment:
~150,000 Clients
CAS with 3 Primaries (yes - we're that company)
~3,800 packages
We have an open PSS call on this:
A few other team members found this:
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Here is the view from 2012:
vSMS_Package_List view from SCCM 2012
ALTER VIEW [dbo].[vSMS_Package_List] AS
select pkg.*, ISNULL(pgn.num, 0) as NumOfPrograms,
IsVersionCompatible=case when isnull(pkg.MinRequiredVersion,0) <
dbo.fnCurrentSiteVersion_INT() then 1 else 0 end,
case when exists (select FullPackageID from ClientDeploymentSettings where
FullPackageID = pkg.PkgID) then 1 else 0 end as IsPredefinedPackage
from SMSPackages_All pkg left join
(
select pg.PkgID, COUNT(*) as num from PkgPrograms pg
where pg.Action != 3 and (pg.ProgramFlags &
0x00000010 = 0)
group by pg.PkgID
) pgn on pkg.PkgID = pgn.PkgID
inner join SMSPackages_G g on g.PkgID=pkg.PkgID
where pkg.Action != 3 and pkg.PackageType = 0 and ((pkg.PkgFlags & 0x80000000)
= 0) -- AP_HIDDEN_FROM_UI
and pkg.PkgID not in (select UpgradePackageID from ClientDeploymentSettings)
AND here is the view from 1606, which is identical except the last line that is
highlighted in yellow.
vSMS_Package_List view from SCCM 1606
ALTER VIEW [dbo].[vSMS_Package_List] AS
select pkg.*, ISNULL(pgn.num, 0) as NumOfPrograms,
IsVersionCompatible=case when isnull(pkg.MinRequiredVersion,0) <
dbo.fnCurrentSiteVersion_INT() then 1 else 0 end,
case when exists (select FullPackageID from ClientDeploymentSettings where
FullPackageID = pkg.PkgID) then 1 else 0 end as IsPredefinedPackage
from SMSPackages_All pkg left join
(
select pg.PkgID, COUNT(*) as num from PkgPrograms pg
where pg.Action != 3 and (pg.ProgramFlags &
0x00000010 = 0)
group by pg.PkgID
) pgn on pkg.PkgID = pgn.PkgID
inner join SMSPackages_G g on g.PkgID=pkg.PkgID
where pkg.Action != 3 and pkg.PackageType = 0 and ((pkg.PkgFlags & 0x80000000)
= 0) -- AP_HIDDEN_FROM_UI
and (dbo.fn_IsClientUpgradePackage(pkg.PkgID) = 0)
The only difference is the replacement of pkg.PkgID not in () with a function
called fn_IsClientUpgradePackage().
That function has the following query
Function IsClientUpgradePackage
ALTER FUNCTION [dbo].[fn_IsClientUpgradePackage] (
@PkgID NVARCHAR(8)
)
RETURNS BIT
AS
BEGIN
DECLARE @RetVal AS BIT
IF (@PkgID IN (SELECT UpgradePackageID FROM ClientDeploymentSettings UNION
SELECT PilotingPackageID FROM ClientDeploymentSettings))
SET @RetVal = 1
ELSE
SET @RetVal = 0
RETURN @RetVal
END
The query in the function is very straightforward. So we created a new view
called vSMS_Package_List_Test and replaced the functional call in 1606 with the
highlighted section above.
ALTER VIEW [dbo].[vSMS_Package_List_TEST] AS
select pkg.*, ISNULL(pgn.num, 0) as NumOfPrograms,
IsVersionCompatible=case when isnull(pkg.MinRequiredVersion,0) <
dbo.fnCurrentSiteVersion_INT() then 1 else 0 end,
case when exists (select FullPackageID from ClientDeploymentSettings where
FullPackageID = pkg.PkgID) then 1 else 0 end as IsPredefinedPackage
from SMSPackages_All pkg left join
(
select pg.PkgID, COUNT(*) as num from PkgPrograms pg
where pg.Action != 3 and (pg.ProgramFlags &
0x00000010 = 0)
group by pg.PkgID
) pgn on pkg.PkgID = pgn.PkgID
inner join SMSPackages_G g on g.PkgID=pkg.PkgID
where pkg.Action != 3 and pkg.PackageType = 0 and ((pkg.PkgFlags & 0x80000000)
= 0) -- AP_HIDDEN_FROM_UI
and pkg.PkgID not in (SELECT UpgradePackageID FROM ClientDeploymentSettings
UNION SELECT PilotingPackageID FROM ClientDeploymentSettings)
Then we modified a query found in the provider to use the new View
SELECT ALL PkgID, Action, DefaultImage, Description, DisconnectDelay,
UseForcedDisconnect, ForcedRetryDelay, Icon, IgnoreSchedule,
IsPredefinedPackage, IsVersionCompatible, ISVString,
Language, LastRefresh, Manufacturer, MIFFilename, MIFName, MIFPublisher,
MIFVersion, Name,
NumOfPrograms, PkgID AS Expr1, SourceSize, PackageType,
PkgFlags, StorePkgFlag, Source, PreferredAddress, Priority, SedoObjectVersion,
ShareName, ShareType,
SourceDate, SourceSite, SourceVersion, StoredPkgPath,
StoredPkgVersion, TransformAnalysisDate, TransformReadiness, Version
FROM dbo.vSMS_Package_List_test AS SMS_Package
WHERE (PkgID NOT IN
(SELECT InstanceKey
FROM dbo.vFolderMembers AS
Folder##Alias##810314
WHERE (ObjectTypeName = N'SMS_Package'))) AND
(Action <> 3) AND (PackageType = 0)
ORDER BY Manufacturer, Name, Version, Language
Testing shows this now returns in 1 second as opposed to more than 90 sec.
depending on how busy SQLServer was before this modification.
Even though the queries look like they should behave the same way, the query
execution plans look substantially different when including the function calls.
=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
We're reporting these findings to the PSS call now (waiting for their
recommendation/workaround) but wanted to see if anyone else has seen this in
their environment?
Thanks
Best regards / Cordialement
Henry E. WILSON
Technical Expert Engineer
Information Technology & Solutions
Infrastructure Management
Automation Services
55 Corporate Drive
Bridgewater, NJ 08807
TEL.: +1 908 981 3288
CELL.: +1 610 724 1169
FAX: +1 908 635 5858
[cid:[email protected]]
System Center Technical
Blog<http://wssamer.sanofi.com/ws/AutomationCenterProvTools/SCTechBlog/Lists/Posts/AllPosts.aspx>
(for internal use only)
SCCM 2012
Dashboard<http://xspw10w201w/Reports/Pages/Report.aspx?ItemPath=%2fUnRestricted+Reports%2fInfrastructure+Health+Status%2fConfigMgr+2012+Dashboard>
(for internal use only)
Provisioning Tools & Lifecycle Sharepoint
Site<http://wssamer.sanofi.com/ws/AutomationCenterProvTools/Documents/Forms/AllItems.aspx>
(for internal use only)
