As a side note: We've been chasing excessive collection evaluations for some time (we have over 5,000 collections now)
There are also 2 "Exclude Collections" in the previously created collections and this collection is scheduled to evaluate every day. Our environment: All Virtual SCCM 1606 1 CAS 3 Primaries Over 140,000 Active Workstations - on the way to ~160,000 SQL on separate box Average Config on SQL systems: SQL on separate box (same segment as primary) 8 CPU's to 14 CPU's 64GB to 98GB (higher config's on more stressed systems) Both General Guidelines as well as custom tweaks would be helpful Thanks From: Wilson, Henry /US Sent: Tuesday, April 04, 2017 6:16 AM To: '[email protected]' <[email protected]> Subject: SQL Query question Hello SQL Guru's We're having a discussion with some Admin's in Europe and I would like an opinion on 2 Collection queries looking for the same result: Which one is more efficient? Collection Query #1: select SMS_R_SYSTEM.ResourceID, SMS_R_SYSTEM.ResourceType, SMS_R_SYSTEM.Name, SMS_R_SYSTEM.SMSUniqueIdentifier, SMS_R_SYSTEM.ResourceDomainORWorkgroup, SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in ( select SMS_R_System.ResourceId from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName in ("Microsoft .NET Framework 4.5.2","Microsoft .NET Framework 4.6.1","Microsoft .NET Framework 4.6","Microsoft .NET Framework 4.6.2") ) ================================================================= Collection Query #2: SELECT sms_r_system.resourceid, sms_r_system.resourcetype, sms_r_system.NAME, sms_r_system.smsuniqueidentifier, sms_r_system.resourcedomainorworkgroup, sms_r_system.client FROM sms_r_system WHERE sms_r_system.resourceid NOT IN (SELECT sms_r_system.resourceid FROM sms_r_system INNER JOIN sms_g_system_installed_software ON sms_g_system_installed_software.resourceid = sms_r_system.resourceid WHERE sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.5.2" or sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.6.1" or sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.6" or sms_g_system_installed_software.arpdisplayname = "microsoft .net framework 4.6.2" ====================================================================== A DCM has also been mentioned to achieve the same result. Comments or recommendations? 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)
