Thursday, September 13, 2018

SCCM - Distribution Point Console Performance Issues - Please make an Index



When you have a lot of Distribution Points you could have Console Performance Issues  (SCCM CB 1803, I don't know if solution below have been released since in SCCM CB future release)


For example, when I click on "Administration\Overview\Distribution Points" it takes 6-7 minutes to display 2900 Standard and Pulll Distribution Point
















If we look into SMSProv.log file (in folder "Program Files\Microsoft Configuration Manager\Logs") when you click on "Distribution Points" you should have theses entries

Execute WQL  =Select * From SMS_DistributionPointInfo~
Execute SQL =select  all SMS_DistributionPointInfo.AddressScheduleEnabled,SMS_DistributionPointInfo.BindExcept,SMS_DistributionPointInfo.BindPolicy,SMS_DistributionPointInfo.BitsEnabled,SMS_DistributionPointInfo.CertificateType,SMS_DistributionPointInfo.Communication,SMS_DistributionPointInfo.Description,SMS_DistributionPointInfo.DPFlags,SMS_DistributionPointInfo.Drive,SMS_DistributionPointInfo.GroupCount,SMS_DistributionPointInfo.HasRelationship,SMS_DistributionPointInfo.HealthCheckEnabled,SMS_DistributionPointInfo.HealthCheckPriority,SMS_DistributionPointInfo.HealthCheckSchedule,SMS_DistributionPointInfo.ID,SMS_DistributionPointInfo.IdentityGUID,SMS_DistributionPointInfo.InternetFacing,SMS_DistributionPointInfo.IsActive,SMS_DistributionPointInfo.IsMulticast,SMS_DistributionPointInfo.IsPeerDP,SMS_DistributionPointInfo.IsProtected,SMS_DistributionPointInfo.IsPullDP,SMS_DistributionPointInfo.IsPXE,SMS_DistributionPointInfo.NALPath,SMS_DistributionPointInfo.Name,SMS_DistributionPointInfo.OperatingSystem,SMS_DistributionPointInfo.PreStagingAllowed,SMS_DistributionPointInfo.Priority,SMS_DistributionPointInfo.PXEPassword,SMS_DistributionPointInfo.RateLimitsEnabled,SMS_DistributionPointInfo.Region,SMS_DistributionPointInfo.ResourceType,SMS_DistributionPointInfo.ResponseDelay,SMS_DistributionPointInfo.SccmPXE,SMS_DistributionPointInfo.ServerName,SMS_DistributionPointInfo.ServiceType,SMS_DistributionPointInfo.ShareName,SMS_DistributionPointInfo.SiteCode,SMS_DistributionPointInfo.SiteName,SMS_DistributionPointInfo.SupportUnknownMachines,SMS_DistributionPointInfo.TransferRate,SMS_DistributionPointInfo.UdaSetting,SMS_DistributionPointInfo.Version from v_DistributionPointInfo AS SMS_DistributionPointInfo ~   



When I do same query on SQL Server Management Studio
SELECT * from v_DistributionPointInfo
->same result







So I make a Call To Microsoft (because I assume I will need an Index )

After query with  "Include Actual Execution Plan" we can see problem

here what view do...

And as you can see in a zoom below a lot of SC_SysResUse_Property seems take a lot of time (missing an Index...)


So using an new index (Thanks to Cyril C. from Microsoft), now same query in 5 sec (remember before it was 7-8min)
As usual, it is NOT permit to modify SQL directly to do not lose your Microsoft support. So please contact Microsoft if you need to resolve this problem.



/****** Object:  Index [NonClusteredIndex-20180827-163036]    Script Date: 27/08/2018 16:42:14 ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180827-163036] ON [dbo].[SC_SysResUse_Property]
(
              [SysResUseID] ASC,
              [Value3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

USE [CM_AHD]
GO

SET ANSI_PADDING ON
GO

/****** Object:  Index [NonClusteredIndex-20180827-161513]    Script Date: 27/08/2018 16:42:00 ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20180827-161513] ON [dbo].[SC_SysResUse_Property]
(
              [SysResUseID] ASC
)
INCLUDE (          [Value3],
              [Name],
              [Value1],
              [Value2]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO