sccm 2012 sql task spiking high cpu

I notice that the sql process is running high on cpu.  I ran a sql query to see what sql task was causing it and I found this. Any idea what it is and how to get it under control. I tried a reboot and it didn't help.

--
-- Name         : spSummaryTaskClientOperation
-- Definition   : SqlObjs
-- Scope        : PRIMARY
-- Object       : P
-- Dependencies : <Detect>
-- Description  : Generate summary data for SMS_EndpointProtectionHealthStatus class
--
CREATE PROCEDURE spSummaryTaskClientOperation @Param nvarchar(255)=NULL, @Watermark BINARY(8)=NULL AS  
BEGIN
 
    SET NOCOUNT ON
    SET XACT_ABORT ON
 
    BEGIN TRAN
 
    -- calculate for SMS_ClientActionStatus
    MERGE INTO ClientActionSummary S
        USING (
            SELECT  s.ID,
                MAX(s.LastMessageTime) AS LastMessageTime,
                COUNT(*) AS TotalClients,
                SUM(CASE WHEN s.ActionResult NOT IN (1, 2, 3, 4) THEN 1 ELSE 0 END) AS UnknownClients,
                SUM(CASE WHEN s.ActionResult = 1 THEN 1 ELSE 0 END) AS NotApplicableClients,
                SUM(CASE WHEN s.ActionResult = 2 THEN 1 ELSE 0 END) AS FailedClients,
                SUM(CASE WHEN s.ActionResult = 3 THEN 1 ELSE 0 END) AS CompletedClients,
                SUM(CASE WHEN s.ActionResult = 4 THEN 1 ELSE 0 END) AS OfflineClients
            FROM vSMS_G_System_ClientActionStatus AS s INNER JOIN
                 v_ClientOperationTargets AS t ON s.OperationID = t.ID AND s.MachineID = t.MachineID AND s.IsVirtual = 0 INNER JOIN
                 vSMS_ClientOperation o ON o.ID = s.OperationID LEFT OUTER JOIN
                 BGB_TaskTemplate AS tt ON tt.TemplateID = o.TemplateID
            WHERE (tt.ValidSeconds IS NULL OR DATEADD(day, 1, DATEADD(second, tt.ValidSeconds, o.RequestedTime)) >= GETUTCDATE()) AND  
                  (o.State != 0 OR DATEADD(day, 1, o.ModifiedOn) >= GETUTCDATE())
            GROUP BY s.ID
        ) AS X ON S.ID=X.ID
    WHEN MATCHED THEN
        UPDATE SET LastMessageTime=X.LastMessageTime,
            TotalClients = X.TotalClients,
            UnknownClients=X.UnknownClients,
            NotApplicableClients=X.NotApplicableClients,
            FailedClients=X.FailedClients,
            CompletedClients=X.CompletedClients,
            OfflineClients = X.OfflineClients
    WHEN NOT MATCHED BY TARGET THEN
        INSERT(ID,
            LastMessageTime,
            TotalClients,
            UnknownClients,
            NotApplicableClients,
            FailedClients,
            CompletedClients,
            OfflineClients)
        VALUES(X.ID,
            X.LastMessageTime,
            X.TotalClients,
            X.UnknownClients,
            X.NotApplicableClients,
            X.FailedClients,
            X.CompletedClients,
            X.OfflineClients);
 
    -- calculate for SMS_ClientOperationStatus
    MERGE INTO ClientOperationSummary S
        USING (
            SELECT  s.ID,
                MAX(s.LastMessageTime) AS LastMessageTime,
                COUNT(*) AS TotalClients,
                SUM(IsUnknown) AS UnknownClients,
                SUM(IsNotApplicable) AS NotApplicableClients,
                SUM(IsFailed) AS FailedClients,
                SUM(IsCompleted) AS CompletedClients,
                SUM(IsOffline) AS OfflineClients
            FROM vSMS_G_System_ClientOperationStatus AS s INNER JOIN
                 v_ClientOperationTargets AS t ON s.ID = t.ID AND s.MachineID = t.MachineID INNER JOIN
                 vSMS_ClientOperation o ON o.ID = s.ID LEFT OUTER JOIN
                 BGB_TaskTemplate AS tt ON tt.TemplateID = o.TemplateID
            WHERE (tt.ValidSeconds IS NULL OR DATEADD(day, 1, DATEADD(second, tt.ValidSeconds, o.RequestedTime)) >= GETUTCDATE()) AND  
                  (o.State != 0 OR DATEADD(day, 1, o.ModifiedOn) >= GETUTCDATE())
            GROUP BY s.ID
        ) AS X ON S.ID=X.ID
    WHEN MATCHED THEN
        UPDATE SET LastMessageTime=X.LastMessageTime,
            LastSummaryTime = GETUTCDATE(),
            TotalClients = X.TotalClients,
            UnknownClients=X.UnknownClients,
            NotApplicableClients=X.NotApplicableClients,
            FailedClients=X.FailedClients,
            CompletedClients=X.CompletedClients,
            OfflineClients = X.OfflineClients
    WHEN NOT MATCHED BY TARGET THEN
        INSERT(ID,
            LastMessageTime,
            LastSummaryTime,
            TotalClients,
            UnknownClients,
            NotApplicableClients,
            FailedClients,
            CompletedClients,
            OfflineClients)
        VALUES(X.ID,
            X.LastMessageTime,
            GETUTCDATE(),
            X.TotalClients,
            X.UnknownClients,
            X.NotApplicableClients,
            X.FailedClients,
            X.CompletedClients,
            X.OfflineClients);
 
    COMMIT TRAN
 
    SELECT 1, @Watermark
 
END
rdefinoAsked:
Who is Participating?
 
Nagendra Pratap SinghDesktop Applications SpecialistCommented:
Since you are good with SQL please check this page.

The symptoms are very much like your. Please let us what happened after this.

https://social.technet.microsoft.com/Forums/en-US/dfb28325-8148-4c53-b54f-8cfe88969513/existing-collections-not-updating
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.