Hi,
Got a weird one, cpu is getting thrashed on the solarwinds database server. User experience is OK, but the below query is executing a million times an hour. Literally. Its waiting on the CPU and is the biggest wait time.
Anyone know what this is, is it normal to have it executing that many times, and is it the cause of my crazy cpu?
WITH cte as
(
SELECT cm.ElementId,
cm.MetricId,
cm.Time,
cm.Value,
cm.Weight,
rn = ROW_NUMBER() OVER (PARTITION BY cm.ElementId, cm.MetricId ORDER BY Time DESC)
FROM Cortex_Metrics_0_Latest cm
WITH
(
NOLOCK
)
INNER JOIN Cortex_Documents cd
WITH
(
NOLOCK
)
ON cm.ElementId = cd.ElementId
WHERE 1=1
AND cm.[ElementId] IN
(
SELECT id
FROM @ElementIds
)
AND [OwnerPartitionId] NOT IN (@ExcludedPartitionIds1)
AND cm.[Time] > DATEADD(MINUTE, -5, GETUTCDATE())
)
SELECT ElementId,
MetricId,
Time,
Value,
Weight
FROM cte
WHERE rn = 1
*/
(@ElementIds [cortex_BigIntIdTableType] READONLY,@ExcludedPartitionIds1 int);
/* BEGIN ACTIVE SECTION (comment inserted by DPA) */
WITH cte as
(
SELECT cm.ElementId,
cm.MetricId,
cm.Time,
cm.Value,
cm.Weight,
rn = ROW_NUMBER() OVER (PARTITION BY cm.ElementId, cm.MetricId ORDER BY Time DESC)
FROM Cortex_Metrics_0_Latest cm
WITH
(
NOLOCK
)
INNER JOIN Cortex_Documents cd
WITH
(
NOLOCK
)
ON cm.ElementId = cd.ElementId
WHERE 1=1
AND cm.[ElementId] IN
(
SELECT id
FROM @ElementIds
)
AND [OwnerPartitionId] NOT IN (@ExcludedPartitionIds1)
AND cm.[Time] > DATEADD(MINUTE, -5, GETUTCDATE())
)
SELECT ElementId,
MetricId,
Time,
Value,
Weight
FROM cte
WHERE rn = 1
