Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Wacky Performance General Question
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

1 Posts

Posted - 12/12/2013 :  00:26:14  Show Profile  Reply with Quote
I work with a 260GB vendor supplied database with all of the indexes created.

A couple of weeks ago a particular process started taking 5-7 minutes to run that used to run in seconds. It did this for a couple of days and then went back to normal. It's happened a few times since.

The stored procedure is 500+ lines and accesses over 25 tables with probably 100-200 indexes.

The problem occurred yesterday and I ran a trace on it while it was misbehaving. Things were ok today and I ran another trace.

What would cause the same process to act so differently?
From Profiler:

CPU: 386976
Reads: 200066610
Duration: 427182

CPU: 4087
Reads: 109362
Duration: 4439

Aged Yak Warrior

885 Posts

Posted - 12/12/2013 :  01:57:45  Show Profile  Visit lionofdezert's Homepage  Send lionofdezert a Yahoo! Message  Reply with Quote
Different execution plans for same query can act like this. Check if base indexes are being rebuild/reorganized regularly and stored procedure is not a victim of parameter sniffing.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000