Searching for devices takes long time to return results in SCCM Console

Recently experienced slow performance issue while searching for a particular device in SCCM console under Devices node \Assets and Compliance\Overview\Devices.
Note: The slow search is only on under Device node, every where else (in) the console is fine.

No errors in SmsAdminUI.log and no errors in the database error logs as well.
Reviewing smsprov.log on the site server showed the actually query (below is the query) the system is trying to execute and it is taking too long to complete the task

Select ALL SMS_CombinedDeviceResources.AADDeviceID,SMS_CombinedDeviceResources.AADTenantID,SMS_CombinedDeviceResources.ActivationLockBypassState,SMS_CombinedDeviceResources.ActivationLockBypassStateTimeStamp,SMS_CombinedDeviceResources.ActivationLockState,SMS_CombinedDeviceResources.ADLastLogonTime,SMS_CombinedDeviceResources.ADSiteName,SMS_CombinedDeviceResources.AMTFullVersion,SMS_CombinedDeviceResources.AMTStatus,SMS_CombinedDeviceResources.ATP_LastConnected,SMS_CombinedDeviceResources.ATP_OnboardingState,SMS_CombinedDeviceResources.ATP_OrgId,SMS_CombinedDeviceResources.ATP_SenseIsRunning,SMS_CombinedDeviceResources.CA_ComplianceEvalTime,SMS_CombinedDeviceResources.CA_ComplianceSetTime,SMS_CombinedDeviceResources.CA_ErrorDetails,SMS_CombinedDeviceResources.CA_ErrorLocation,SMS_CombinedDeviceResources.CA_IsCompliant,SMS_CombinedDeviceResources.ClientActiveStatus,SMS_CombinedDeviceResources.ClientCertType,SMS_CombinedDeviceResources.ClientCheckPass,SMS_CombinedDeviceResources.ClientEdition,SMS_CombinedDeviceResources.ClientRemediationSuccess,SMS_CombinedDeviceResources.ClientState,SMS_CombinedDeviceResources.ClientType,SMS_CombinedDeviceResources.ClientVersion,SMS_CombinedDeviceResources.CNAccessMP,SMS_CombinedDeviceResources.CNIsOnInternet,SMS_CombinedDeviceResources.CNIsOnline,SMS_CombinedDeviceResources.CNLastOfflineTime,SMS_CombinedDeviceResources.CNLastOnlineTime,SMS_CombinedDeviceResources.CoManaged,SMS_CombinedDeviceResources.CurrentLogonUser,SMS_CombinedDeviceResources.DeviceAccessState,SMS_CombinedDeviceResources.DeviceCategory,SMS_CombinedDeviceResources.DeviceOS,SMS_CombinedDeviceResources.DeviceOSBuild,SMS_CombinedDeviceResources.DeviceOwner,SMS_CombinedDeviceResources.DeviceThreatLevel,SMS_CombinedDeviceResources.DeviceType,SMS_CombinedDeviceResources.Domain,SMS_CombinedDeviceResources.EAS_DeviceID,SMS_CombinedDeviceResources.EP_AntispywareEnabled,SMS_CombinedDeviceResources.EP_AntispywareSignatureUpdateDateTime,SMS_CombinedDeviceResources.EP_AntispywareSignatureVersion,SMS_CombinedDeviceResources.EP_AntivirusEnabled,SMS_CombinedDeviceResources.EP_AntivirusSignatureUpdateDateTime,SMS_CombinedDeviceResources.EP_AntivirusSignatureVersion,SMS_CombinedDeviceResources.EP_ClientVersion,SMS_CombinedDeviceResources.EP_DeploymentDescription,SMS_CombinedDeviceResources.EP_DeploymentErrorCode,SMS_CombinedDeviceResources.EP_DeploymentState,SMS_CombinedDeviceResources.EP_Enabled,SMS_CombinedDeviceResources.EP_EngineVersion,SMS_CombinedDeviceResources.EP_InfectionStatus,SMS_CombinedDeviceResources.EP_LastFullScanDateTimeEnd,SMS_CombinedDeviceResources.EP_LastFullScanDateTimeStart,SMS_CombinedDeviceResources.EP_LastInfectionTime,SMS_CombinedDeviceResources.EP_LastQuickScanDateTimeEnd,SMS_CombinedDeviceResources.EP_LastQuickScanDateTimeStart,SMS_CombinedDeviceResources.EP_LastThreatName,SMS_CombinedDeviceResources.EP_PendingFullScan,SMS_CombinedDeviceResources.EP_PendingManualSteps,SMS_CombinedDeviceResources.EP_PendingOfflineScan,SMS_CombinedDeviceResources.EP_PendingReboot,SMS_CombinedDeviceResources.EP_PolicyApplicationDescription,SMS_CombinedDeviceResources.EP_PolicyApplicationErrorCode,SMS_CombinedDeviceResources.EP_PolicyApplicationState,SMS_CombinedDeviceResources.EP_ProductStatus,SMS_CombinedDeviceResources.ExchangeOrganization,SMS_CombinedDeviceResources.ExchangeServer,SMS_CombinedDeviceResources.IMEI,SMS_CombinedDeviceResources.IsActive,SMS_CombinedDeviceResources.IsAlwaysInternet,SMS_CombinedDeviceResources.IsAOACCapable,SMS_CombinedDeviceResources.IsApproved,SMS_CombinedDeviceResources.IsBlocked,SMS_CombinedDeviceResources.IsClient,SMS_CombinedDeviceResources.IsInternetEnabled,SMS_CombinedDeviceResources.IsMDMActive,SMS_CombinedDeviceResources.IsObsolete,SMS_CombinedDeviceResources.IsSupervised,SMS_CombinedDeviceResources.IsVirtualMachine,SMS_CombinedDeviceResources.LastActiveTime,SMS_CombinedDeviceResources.LastClientCheckTime,SMS_CombinedDeviceResources.LastDDR,SMS_CombinedDeviceResources.LastHardwareScan,SMS_CombinedDeviceResources.CP_LastInstallationError,SMS_CombinedDeviceResources.LastLogonUser,SMS_CombinedDeviceResources.LastMPServerName,SMS_CombinedDeviceResources.LastPolicyRequest,SMS_CombinedDeviceResources.LastSoftwareScan,SMS_CombinedDeviceResources.LastStatusMessage,SMS_CombinedDeviceResources.LastSuccessSyncTimeUTC,SMS_CombinedDeviceResources.LastSyncNowRequest,SMS_CombinedDeviceResources.CP_LatestProcessingAttempt,SMS_CombinedDeviceResources.ManagementAuthority,SMS_CombinedDeviceResources.Name,SMS_CombinedDeviceResources.PasscodeResetState,SMS_CombinedDeviceResources.PasscodeResetStateTimeStamp,SMS_CombinedDeviceResources.PhoneNumber,SMS_CombinedDeviceResources.PolicyApplicationStatus,SMS_CombinedDeviceResources.PrimaryUser,SMS_CombinedDeviceResources.RemoteLockState,SMS_CombinedDeviceResources.RemoteLockStateTimeStamp,SMS_CombinedDeviceResources.MachineID,SMS_CombinedDeviceResources.ArchitectureKey,SMS_CombinedDeviceResources.RetireStatus,SMS_CombinedDeviceResources.SerialNumber,SMS_CombinedDeviceResources.SiteCode,SMS_CombinedDeviceResources.SMSID,SMS_CombinedDeviceResources.CP_Status,SMS_CombinedDeviceResources.SuppressAutoProvision,SMS_CombinedDeviceResources.SyncNowStatus,SMS_CombinedDeviceResources.Unknown,SMS_CombinedDeviceResources.UserDomainName,SMS_CombinedDeviceResources.UserName,SMS_CombinedDeviceResources.WipeStatus from vSMS_CombinedDeviceResources AS SMS_CombinedDeviceResources  where (((SMS_CombinedDeviceResources.ClientType is null  AND SMS_CombinedDeviceResources.EAS_DeviceID is null ) OR SMS_CombinedDeviceResources.ClientType <> 3) AND (((SMS_CombinedDeviceResources.PrimaryUser like N'%Windows%' OR SMS_CombinedDeviceResources.CurrentLogonUser like N'%Windows%') OR SMS_CombinedDeviceResources.LastLogonUser like N'%Windows%') OR SMS_CombinedDeviceResources.Name like N'%Windows%'))

When I executed the same query in SQL management Studio, the execution completed within 3 seconds, where as the console is taking 8-10 minutes.

Next, fired up SQL profiler and captured SQL server profiler when searching device in SCCM console. The SQL server profiler indeed showed that the above query executed very slow.

So based on these tests, it seems to be some differences in the SQL execution of the same command when used SQL Management Studio and SCCM Console.

To test further, decided to cleanup the SQL execution plan cache by running below commands;

--clean buffer cache
DBCC DROPCLEANBUFFERS

--clean procedure cache
DBCC FREEPROCCACHE

--clean all caches
DBCC FREESYSTEMCACHE ('ALL')

Please Note:
- After running above commands, some of the queries may run slow for first time
- Always check with your DBA for any applied customization

After running above commands, re-tested the search in SCCM console under device node. The slow search issue disappeared and the search results displayed in 3 seconds.