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