SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

cwhite
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:

Yesterday
CPU: 386976
Reads: 200066610
Duration: 427182

Today
CPU: 4087
Reads: 109362
Duration: 4439


lionofdezert
Aged Yak Warrior

Pakistan
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.

--------------------------
http://connectsql.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.03 seconds. Powered By: Snitz Forums 2000