I have a large stored procedure running on SQLServer2008 to produce a report on the previous day's data. It rehashes data from 12 tables, including a lot of ORDER by date and CASTS. Among others, it SELECTs a few columns from table S, using a LEFT OUTER JOIN S.

The time for execution changed from 3 hours to 12 hours when I (accidentally) dropped an index on an apparently unrelated table SA.

I checked the dependencies and SSMS shows something like this:

  • spReport

    • TABLE A
      • TABLE S
        • spSA1
        • spSA2

    SSMS tells me that there is dependency on table S, that, in turn, depends on two stored procedures, that (I know) happen to use table SA. The dependency graph stops at the stored procedures, and does not show table SA.

Table S has a TRIGGER AFTER UPDATE that calls these stored procedures on table SA.

But the top level stored procedure only SELECTS columns from table S, it does not UPDATE anything. The trigger should never run during its execution.

Is the dependecy graph correct or is it just ignoring the AFTER UPDATE?

Should I be looking elsewhere to understand the link between performance drop and index on SA?

Could the TRIGGERS without the index on SA be running slowly in the background, locking up tables and disrupting everything else?

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.