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

  • Can you post the actual (not estimated) execution plan somewhere? (As well as the plan you get from the old copy, so we can compare?) And I mean the actual .sqlplan file, not a screen shot. Plan complexity is not that important, it's still going to point to a culprit. – Aaron Bertrand Jul 16 '12 at 12:37

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:

enter image description here

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

enter image description here

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.

  • finagle verb - to improve the performance of a SQL query by tweaking an incorrectly chosen JOIN operator. Eg: "My query suddenly ran slow, so I finagled the merge join into a hash join" – ligos Jul 16 '12 at 23:00
  • 1
    I agree with the Hash hint Aaron. This query is so complicated that it could be risky to start messing with adding indexes. In general, looking at the estimated verses actual numbers on both plans, it seems that there could be some missing stats... Stats are very cheap and can really help the optimizer generate better plans. – brian Jul 17 '12 at 1:05

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.