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
