Question

Locked

Excessive CPU Utilization For Procedure dbo.usps_ArchivingPerformanceByHour

By OldDogTime ·
Included in the ?Details? section below is the original dbo.usps_ArchivingPerformanceByHours procedure, followed by modifications I applied to eliminate the procedure from eating up 20% of our CPU without accomplishing anything.

This was sent to our VAP (value added provider), who indicated that I would need to open a support ticket in order to have the following responded to. But that the normal recommendation was to place the Vault on a dedicated server. (still not a reason to ignore a performace issue in my book... By hey, I AM a DBA and not a vendor...) Befor I take this further, i wanted to know if anyone else is experiencing the same issue and if so, what are your thoughts...

These were mine:

Here are the alternatives I would like to be considered.

1. If this procedure must be executed throughout the day as it currently runs, have the technical evaluate the changes I made below to allow it to execute more efficiently. In our setup it is constantly looking only for emails archived in the last hour. It just does not make sense to me that the application would want it to continually initiate a full database scan reading millions of records when the expected rows returned should be a very small percentage. The optimizer predicted approx. 4 million rows (it determines an ?on average? as it has incomplete knowledge of what is going on) and refuses to use the index without the hint). So you see that my solution requires both a new index and a hint added to the procedure taking the procedure from a performance hog to a non-measurable event. The downside is that if run within an hour after the next ingestion, use of the index will cause the first run to be longer as it will have an extra index read. But that is one execution compared to the hundreds that seem to be occurring on a daily basis during prime time, consuming almost 20% of our sql server?s cpu and using almost ? of our buffer!!
2. I do not know what this procedure is actually accomplishing for the application with our having chosen to use the file trigger method. Could it be turned off? Or rescheduled to run off hours? As long as it was not affecting prime time execution, my concerns would be alleviated?.

========================================================= Details =================================================================

Procedure: [usps_ArchivingPerformanceByHours]

CPU: 127390
Duration: 600959
Reads: 53,439,050
Rows Returned: 0

SELECT
V.VaultIdentity,
V.VaultID,
count(V.VaultIdentity) TotalItemsAcrhived,
sum(convert(bigint, S.ItemSize)) TotalCompressedSizeArchived ,
sum(SP.OriginalSize) TotalSizeArchived
FROM
Vault V
INNER JOIN
Saveset S
ON
S.VaultIdentity = V.VaultIdentity
INNER JOIN
SavesetProperty SP
ON
SP.SavesetIdentity = S.SavesetIdentity
INNER JOIN
(SELECT id FROM
OPENXML(@idoc, '/vaults/vault', 0)
WITH
(
id VARCHAR(127)
)) XMLT
ON
V.VaultId = XMLT.id
WHERE
S.ArchivedDate >= @cutOffTime
GROUP BY
V.VaultIdentity, V.VaultID

New Index:

USE [EVVSBOCC_1]
GO

/****** Object: Index [IX_ArchivedDate] Script Date: 09/10/2010 08:43:17 ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_ArchivedDate] ON [dbo].[Saveset]
(
[ArchivedDate] ASC,
[IdTransaction] ASC
)
INCLUDE ( [VaultIdentity],
[ItemSize],
[SavesetIdentity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO


Revised Procedure: [usps_ArchivingPerformanceByHours]

CPU: 0
Duration: 1
Reads: 7
Rows Returned: 0

SELECT
V.VaultIdentity,
V.VaultID,
count(V.VaultIdentity) TotalItemsAcrhived,
sum(convert(bigint, S.ItemSize)) TotalCompressedSizeArchived ,
sum(SP.OriginalSize) TotalSizeArchived
FROM
Vault V
INNER JOIN
Saveset S
WITH ( INDEX ( IX_ArchivedDate ) )
ON
S.VaultIdentity = V.VaultIdentity
INNER JOIN
SavesetProperty SP
ON
SP.SavesetIdentity = S.SavesetIdentity
INNER JOIN
(SELECT id FROM
OPENXML(@idoc, '/vaults/vault', 0)
WITH
(
id VARCHAR(127)
)) XMLT
ON
V.VaultId = XMLT.id
WHERE
S.ArchivedDate >= @cutOffTime
GROUP BY
V.VaultIdentity, V.VaultID

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Share your knowledge
Back to Networks Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums