Poor performance after changing SQL Server 2008 R2 database

Question

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.

Suggestions?

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

Answer 1

So the major differences I see on first glance is that the table scan on ARMiscIncome feeds a merge join in the slow version, which requires a sort:

But in the fast version it feeds a hash join (so no sort is required):

This sort is the highest overall cost in the slow plan, so I might suggest either finagling a hash join there (this could be simply due to different row counts in the two systems, even if statistics are out of date), or adding an index, such as this one which satisfies the output:

CREATE INDEX SortHelper ON dbo.ARMiscIncome(GuestID)
  INCLUDE (Amount, ChargeCode, DateEntered, Description, TranDate);

(Or changing the clustered index on that table.)

Usual caveats apply, e.g. adding/changing an index may help this specific query, but may not help others, and/or may cause undue stress on DML operations. But certainly worth thoroughly testing IMHO.

As an aside, I was able to find the most expensive operator by comparing the plans in SQL Sentry Plan Explorer. This is a free tool my company puts out to make exactly this type of investigation a lot more painless than it is in Management Studio.

Answer 2

In your slow plan you've got plenty of heap RID lookups which miss in the fast plan. It seems like as if your clustered indexes went away or you are now missing some covering indexes you had before, especially on ARCharge and ARPayAdj.