SP spikes CPU usage to over 90%.

Mushtaq 2013-04-09 14:54:41

There are few Hash Match operators which are contributing it but i also wondering if i create the missing indexes will it speed up execution.

This plan was collected from Peformance advisor's history. Please let me know if you need further details.

Thank you in advance for your support.

/storage/attachments/372-plan_Anonymized.queryanalysis

Jonathan Kehayias 2013-04-09 15:02:38

Hey Mushtaq,

Unfortunately, the anonymized version of your plan is stripping all the intermediate information that is needed to look at the operations and make suggestions. The one thing I do see is that you have a huge cardinality misestimate that occurs, but since I don't have the statement or other information it's hard to suggest why.

Dan Holmes 2013-04-09 15:11:22

there are a few hash join operators in the 8 or so statements. which statement is this question about?

Mushtaq 2013-04-09 15:36:30

Thank you Jonathan & Dan for your responses

Please find attached original plan.

Dan, i think the second statement 'INSERT INTO #tblReleasedFromEtaBin' with 27,299,287 CPU cycles is most costly CPU operation than other statements. Please correct if i am wrong.

Thanks.

link text

/storage/attachments/373-full_plan.pesession

Jonathan Kehayias 2013-04-09 19:22:34

I'd start out by adding a clustered index on SubID for the #tblReleasedFromEtaBin table if I were looking at tuning this procedure. I'd also try the NOT EXISTS to earlier evaluation with a rewrite like:

INSERT INTO #tblReleasedFromEtaBin (SubID, DateReleased, BoxDate, iYearMonthDay, Unit, ETA) 
SELECT 
    fg.SubID, 
    MIN(fg.ReadTime), 
    MIN(mb.ReadTime), 
    CONVERT(CHAR(4), YEAR(MIN(fg.ReadTime))) + '-' 
        + RIGHT( '0' + CONVERT(VARCHAR(2), MONTH(MIN(fg.ReadTime))), 2) + '-' 
        + RIGHT( '0' + CONVERT(VARCHAR(2), DAY(MIN(fg.ReadTime))), 2),
    MIN(fg.Unit), 
    MIN(bin.ETA)
FROM (
    SELECT fg1.SubID, fg1.BoxID, fg1.ReadTime, fg1.Unit
    FROM dbo.FinishedGoods AS fg1
    WHERE NOT EXISTS (
        SELECT tmp.SubID
        FROM #tblReleasedFromEtaBin tmp
        WHERE tmp.SubID = fg1.SubID)
    ) AS fg
INNER JOIN History.ModuleBox mb WITH (NOLOCK)
ON fg.SubID = mb.SubID
AND fg.BoxID = mb.BoxID
INNER JOIN dbo.Box box WITH (NOLOCK)
ON mb.BoxID = box.BoxID
INNER JOIN dbo.Bin bin WITH (NOLOCK)
ON box.BinID = bin.BinID
WHERE bin.ETA IS NOT NULL
AND bin.WattsSold > 67
GROUP BY fg.SubID
HAVING MIN(fg.ReadTime) > '2011-03-01 00:00:00.000'

I'd be interested to see the plans if you created clustered indexes for your temporary tables based on the JOIN criteria and allowed the optimizer to make better optimizations.

April 9, 2013

No Comments