We recently migrated from SCCM 2007 R3 to SCCM 2012. So far we have ca. 2500 clients in SCCM 2012. SCCM is running on a virtual server (Windows 2008 R2 on Hyper-V with 8 GB RAM), and the SQL database is running on a physical server (as separate instance of SQL Server 2008 R2 with latest SP/CU).

The SQL instance has 48 GB RAM assigned (when it is not running, the server has ca. 3 % CPU load and 48 GB free RAM). I can't see any reason for performance issues.

Still, the SQL server can't keep up with the requests from SCCM. When I boot the SCCM server, queues queries pile up. After few minutes there are ca. 500 queries in the queue and SQL stops responding / accepting connections.

I traced SQL connections and can't see anything that would not be legit. Lots of status messages are written, hardware inventory updated etc. - looks normal to me. Still, it seems that the easiest queries take ages (or at least SQL thinks so). 

One weird thing: As a test I enabled the "query governance" option with a setting of 300 (meaning that queries that likely take more then 300 seconds should be refused). With this setting SQL refused all queries - a query that selects ONE row was refused because the calculcated runtime was 8409 seconds - though (after removing the query governance setting) is actually ran only few milliseconds.

Is there anything I could tune? Can it be that something is queued on SCCM side that floods my SQL?

I found that SCCM causes excessive TEMPDB usage, which then causes disk load of ca. 80,000 % (not 80 %). As soon as I kill SMS Executive (it can't be stopped) the disk load drops to zero; few seconds after I start it it goes to 80000 % again.

I found that this query is queued many times, and each takes a long time:

SELECT ServerPath, SiteCode, MasterSiteCode, IISPreferedPort, IISSSLPreferedPort, URL, SigURL, AccessType, ADSiteName, IPSubnets, IPv6Prefixes,          RemoteURL, SigRemoteURL, DPVersion, DPCapabilities, ServerRemoteName, IsPeerDP, IsMulticast,          PkgFlags, IsPXE, WindowsAuthTrustHint, MAX(Locality) as Locality   FROM   (          SELECT '' as ServerPath, '' as SiteCode, '' as MasterSiteCode, '' as IISPreferedPort, '' as IISSSLPreferedPort, 'net:'+ccf.SourceURL as URL, '' as SigURL, '1' as AccessType, '' as ADSiteName, '' as IPSubnets, '' as IPv6Prefixes, 'net:'+ccf.SourceURL as RemoteURL,'' as SigRemoteURL,           '' as DPVersion, '<Capabilities />' as DPCapabilities, '' as ServerRemoteName, '' as IsPeerDP, '' as IsMulticast, '' as PkgFlags, '' as IsPXE,          '' as WindowsAuthTrustHint, 3 as Locality, 0 as DPFlags   from CI_ContentPackages ccp    inner join CI_ContentFiles ccf on ccp.Content_ID = ccf.Content_ID    where ccf.SourceURL IS NOT NULL and ccp.Content_UniqueID = @vchNewContentID and @iOnlineContent = 1    UNION ALL   SELECT CDPM.ServerPath, CDPM.SiteCode,           CASE Sites.SiteType               WHEN 1 THEN Sites.ReportToSite               ELSE Sites.SiteCode           END as MasterSiteCode,        SCPIIS.Value1 as IISPreferedPort, SCPIISSSL.Value1 as IISSSLPreferedPort,         CASE               WHEN CDPM.AccessType=1 AND len(CDPM.URL) > 8 AND DP.DPFlags<>1 AND DP.IsPeerDP<>1               THEN left (CDPM.URL, len(CDPM.URL) - 8) + UC.ContentSubFolder               ELSE CDPM.URL + UC.ContentSubFolder           END as URL,           case when (charindex('SMS_DP_SMSSIG$', CDPM.SigURL)) > 0 then           (left(CDPM.SigURL, 14 + charindex('SMS_DP_SMSSIG$', CDPM.SigURL)) + UC.Content_UniqueID + '.'+ @vchVersion + '.tar')           else CDPM.SigURL END as SigURL,           CDPM.AccessType,           DPI.ADSiteName, DPI.IPSubnets, DPI.IPv6Prefixes,           CASE WHEN CDPM.URLProtocol is not NULL THEN CDPM.URLProtocol +               CASE @iInternet                    WHEN 1 THEN srl.Public

(Likely the query is not complete)

Looks like rebuilding all indexes fixed the issue. I used this script to do it: http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

• Marked as answer by svhelden Wednesday, September 26, 2012 5:58 AM

Do you have the Rebuild Indexes Site Maintenance task disabled?

Do you have the Rebuild Indexes Site Maintenance task disabled?

Ha! All the site maintenance tasks were enabled, except this one. Seems to be off by default, likely to confuse administrators ;)

Thanks!

HI

Rebuilding index on site server resolved this issue could you please let me know mine is set to rebuild once in a week .shall i cahange that to everyday .

Regards

Once a week is sufficient. Too often can also cause issues.