Need some tips on what I can do to track down this problem. My client has a modest sized SQL Server 2008 R2 database (approx 6 gigs in size).
Last week I modified the database to add new a single new field to about 6 (of the hundred) tables, and made corresponding changes to the views and stored procedures that used those tables - all in in, very minor changes.
Problem is, for one stored procedure, that I did not modify, the performance has become truly horrid. What used to run in 8 seconds, now takes at least 10 minutes and since this report is run throughout the day, it tends to kill everyone else's performance when someone runs it.
I have used Red Gate's SQL Compare to try and figure out what has changed, besides the new fields I added, but can't quite find anything that would account for the difference - so the question is, how do I go about debugging this? I have a 6 month old copy of the database on the same machine that I can still run this SP in 8 seconds; the SP is the same, the indices seem all to be the same, I have updated statistics, recompiled the views and recompiled all the stored procedures, and still can't seem to get the performance back.
SO the question for you more experienced DBA's, how do I go about solving this? I've looked at the execution plans, but quite frankly the SP is so complex, with so many steps that its difficult to read - and I don't know exactly what I am looking for.
EDIT: and just to clarify, I didn't upgrade the hardware OR the version of SQL Server, just some underlying table changes; and the 6 month old copy of the database, that still runs really fast, is only about 10 percent smaller than the current version, so while there has been growth, nothing that should account for a 60X slowdown in performance.
Link to slow plan: https://www.dropbox.com/s/arg2s5psav3slj3/SlowPlan.sqlplan
Link to fast plan: https://www.dropbox.com/s/2frmzqqajtpy9q2/GoodPlan.sqlplan