Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
budu
Starting Member
2 Posts |
 Posted - 2009-02-23 : 17:01:12
|
| Hi, today I've came across the strangest bug I ever encountered. And, yes, I know I've said that a hell lot of times, but this time, it's really beyond weird. This afternoon, error reports started coming from our company website. They're all coming from a single page with the same cause, namely "Timeout expired". Nearly every query here are kept in stored procedures, so I get the name of the one used by this page and try it in Query Analyzer. It's really not the best query out there, in fact it's horrible when you look at its execution plan, but it generally return its resultset under 10 seconds. Now it was suddenly taking 30s and sometime way more.I've been optimizing this database for a week, but didn't touch the tables used by this query. The only recent change to one of those tables was adding something like 5000 rows to a table containing ~18000 rows. This shouldn't have such a dramatic effect, especially 2 hours after adding the new records.Then I had a great idea (I really didn't knew what to do at this point, on the verge of panic in fact) of taking the TSQL code from that SP and trying it directly into a Query Analyser window. To my surprise, it worked correctly giving the right resultset in ~3s, retry it, this time ~9s. I'm flaggerbasted by that, I mean, how can this be possible? I look at execution plan of the SP and direct TSQL code, same thing!Then I create a new SP with the same code and a different name, I quickly change our website's DAL code, redeploy it and everything is magically back to normal. Can someone help figure out what happened?We're using SQL Server 2000, Enterprise Edition, but now that I'm checking it's version 8.00.760, thus SP4 has not been applied yet. Maybe this behavior is caused by an old bug. Until today I had assumed SP4 was already applied as it was released 2 year before I got this job, or should I say those jobs, I'm not really a DBA! ;-)Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-02-23 : 17:13:17
|
| Sounds like the execution plan for the procedure was cached from before the new rows were added, and it was not optimized for it. There's also the possibility that the statistics for the table were auto-updated. Either condition could have made the cached plan perform poorly, whereas a new query or procedure would work better since its plan would be generated based on the changes.If you see that happen again, try running UPDATE STATISTICS on the table(s), and also run DBCC FREEPROCCACHE before executing the procedure.Also, if you're running SQL Server 64 bit, there's a bug in the memory management that can cause an excessive amount of RAM to be used for procedure cache, reducing the amount available for data, and causing excessive memory paging. DBCC FREEPROCCACHE can help, at least temporarily, by freeing up that reserved memory. |
 |
|
|
budu
Starting Member
2 Posts |
Posted - 2009-02-24 : 14:00:39
|
| @tkizer & @robvolk: Thank you both for your suggestions. After the maintenance plan ran last night, the original SP has got back to a decent level of performance. I still doesn't get why, when testing the website after adding the batch of records, it wasn't slow and the slowdown was felt only 2 hours after? Anyway, I'll let it has it is right now and try the UPDATE STATISTICS/DBCC FREEPROCACHE tricks if it ever happen again. Thanks again! |
|
|
|
|
|
|
|
|
Sudden loss of performance for a specific SP.