1

Hopefully this isn't a repeat question. I haven't had much luck finding an existing answer.

I have an index on a somewhat large table (About 60 million rows, 25gbs with indexes) that has been in existence, unchanged for about 2-3 years and a stored procedure that is also untouched for about 2-3 years that relies on this index. The SQL server that both reside on has its statistics updated at 5AM each morning. This server is a subscriber to another DB that also has the same SP, table, and index. The table is replicated to the server in question.

Literally overnight this stored procedure went from running for under a second to running up to 45 minutes causing CPU to spike to 100%. On the publisher server, this same stored procedure uses the identical table + index still under a second when executed.

While I can't get an actual execution plan, I can see the estimated and the index seek estimated a CPU cost of like 200,000 instead of the usual 7-9.

I tried updating statistics on the table (despite the morning job) with fullscan. I've tried dropping and recreating the index.

Ultimately, dropping this index caused the stored procedure to use a new plan which is much faster (done in seconds), but the actual execution plan of course is asking for the index that I dropped.

Any ideas, in general, what would make a plan and/or index spike CPU like this so suddenly?

Thanks

EDIT:

Link to High CPU Plan

Link to Low CPU Plan

ALTER Procedure Schema1.Object1

@ID char(3) = ?

AS SET NOCOUNT ON DECLARE Variable1 integer

SELECT Variable1 = Column1 FROM Object2 WHERE Column2 = Variable2

IF Variable1 IS NOT NULL AND Variable1 > ? BEGIN

SELECT DISTINCT Object3.Column3 AS Column4

, Object3.Column5 , Object3.Column6 ,(Object3.Column5 + ? + Object3.Column6) AS Column7 FROM Object4 JOIN Object3 ON Object3.Column3 = Object4.Column3 WHERE Object4.Column1 = Variable1 AND Object4.Column8 = ?

UNION SELECT DISTINCT Object3.Column3 AS Column4 , Object3.Column5 , Object3.Column6 ,(Object3.Column5 + ? + Object3.Column6) AS Column7 FROM Object5 Object6 INNER JOIN Object7 Object8 ON Object8.Column9 = Object6.Column9 INNER JOIN Object3 ON Object3.Column3 = Object6.Column10 WHERE Object6.Column11 = ? AND Object6.Column8 = ? AND Object8.Column8 = ? AND Object8.Column1 = Variable1

UNION select ?, ?, ?, ? ORDER BY Object3.Column5, Object3.Column6

END
  • Can you post the SP and the (estimated or actual) execution plan? – ypercubeᵀᴹ Jan 26 '17 at 23:07
  • 2
    Usually it's parameter sniffing, but it's sort of hard to tell exactly without being able to see more. You should search around for those terms to see if the symptoms match your situation, assuming you can't provide more info. – Erik Darling Jan 26 '17 at 23:18
  • Have you tried comparing the plan on the publisher with the plan on the subscriber. It would be easier to troubleshoot when u see the difference. – jesijesi Jan 26 '17 at 23:57
  • Maybe worth a read... – Aaron Bertrand Jan 27 '17 at 1:45
  • 1
    Before I do anything, like stats update, re-index, I would suggest you do a sp_recompile '<your SP in question>'. This will force the SP to do an recompilation. From your description, it is surely an Execution Plan (EP) sub-optimized, it can be parameter sniffing or just a bad EP reuse. Recompiling SP will most likely (60% chance IMHO) fix the issue. – jyao Jan 27 '17 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.