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?
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