SP spikes CPU usage to over 90%.
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.
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?
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.
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.
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.