Stored Procedure takes 15 minutes to run instead of its usual 1 minute

You also notice that it is doing a lot more reads than it should, and by a lot, I mean hundreds of millions versus tens of thousands. Now stop right there…

Without knowing anything else, what’s the first thing that comes to mind? Don’t worry. I’ll wait.

Purple Clock

Statistics! Yes, stale statistics. “But, Stacy,” you protest, “we have a job that runs every night that checks the fragmentation of the indexes and rebuilds the fragmented ones. This cannot be. You must be terribly mistaken.”

Sad girl

Check them anyway. In this case, it was, indeed, the statistics. Update stats with full scan resolved the issue right away. Remember, when things were running fine a couple of days ago, and now, for seemingly no reason, things are running slow, check your stats.

happy dance

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s