rdefino
asked on
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 : spSummaryTaskClientOperati on
-- Definition : SqlObjs
-- Scope : PRIMARY
-- Object : P
-- Dependencies : <Detect>
-- Description : Generate summary data for SMS_EndpointProtectionHeal thStatus class
--
CREATE PROCEDURE spSummaryTaskClientOperati on @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_ClientAction Status 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.LastMess ageTime,
TotalClients = X.TotalClients,
UnknownClients=X.UnknownCl ients,
NotApplicableClients=X.Not Applicable Clients,
FailedClients=X.FailedClie nts,
CompletedClients=X.Complet edClients,
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_ClientOperat ionStatus 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.LastMess ageTime,
LastSummaryTime = GETUTCDATE(),
TotalClients = X.TotalClients,
UnknownClients=X.UnknownCl ients,
NotApplicableClients=X.Not Applicable Clients,
FailedClients=X.FailedClie nts,
CompletedClients=X.Complet edClients,
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
--
-- Name : spSummaryTaskClientOperati
-- Definition : SqlObjs
-- Scope : PRIMARY
-- Object : P
-- Dependencies : <Detect>
-- Description : Generate summary data for SMS_EndpointProtectionHeal
--
CREATE PROCEDURE spSummaryTaskClientOperati
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_ClientAction
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.LastMess
TotalClients = X.TotalClients,
UnknownClients=X.UnknownCl
NotApplicableClients=X.Not
FailedClients=X.FailedClie
CompletedClients=X.Complet
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_ClientOperat
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.LastMess
LastSummaryTime = GETUTCDATE(),
TotalClients = X.TotalClients,
UnknownClients=X.UnknownCl
NotApplicableClients=X.Not
FailedClients=X.FailedClie
CompletedClients=X.Complet
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.