July 19, 2011 at 11:09 am
the store proc which is in production suddenly got a performance problem, the store proc runs smoothly for few weeks but yesterday i got a performance problem, it taking few seconds more while i try to execute. So will any one give me a suggestion what would be the problem. thank you
July 19, 2011 at 11:14 am
When's the last time you updated the stats on that table?
What's the current, bad execution plan of the sp?
What's the good execution plan of the query?
July 19, 2011 at 2:18 pm
What's the load on the server? On the network?
Is the query returning a lot more data than it was before?
Is there any blocking going on?
July 19, 2011 at 4:03 pm
This tool can help you get started with figuring out the basics:
Performance Troubleshooting Tool
Jason
Webmaster at SQL Optimizations School
July 19, 2011 at 4:39 pm
Hi
I suggest to get an idea of any query is to hit ctrl+ l and get the query plan and see where your cost is.
Yes to the other replies, it could be stats and it could be recent other changes on the server.
For stats the default settting should update where and when stats are required and even create it when needed.
So checkout the query plan and see where the time is going.
Cheers
Jannie
July 24, 2011 at 12:32 am
Depending the amount of data changes , and on your STATISTCS management, some data distribution may have changed. As a first step , Force a UPDATE STATISTICS and execute the sp again
Jack Vamvas
sqlserver-dba.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply