| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2008-07-21 : 17:52:53
|
| We have an ASP.NET web application that queries our SQL Server 2000 database, and one action in the web app is throwing database time-out errors. There are several database actions that take place from the one web app action, but there is one primary stored procedure which consumes the most time. It normally runs to completion successfully in 4-6 seconds. All seems to function fine for a while, and then we start getting timeouts. This happened last Friday and again today (Monday). There does not appear to be a significant change in work load on the server at these times, and in fact, we have two other instances of this web app and database (identical code) running on the same servers which continue to operate fine while the one instance fails, so this suggests to me that it must be database or web site specific rather than server or network specific.The "fix" that we stumbled upon is a bit strange to me. We have done a significant overhaul of this one key procedure for our next version. If we deploy it (drop the old / create the new) and then re-deploy the original (another drop / create script) then the time-outs disappear. This led me to consider something with the procedure cache, but when the problem recurred today, we issued a DBCC FREEPROCCACHE statement to no avail. In fact, we also tried just using the original script to drop the procedure and recreate it identically (without using the new code) and that did not seem to make any difference. Perhaps this pseudo-upgrade approach is a red-herring, but it has "worked" both times now. But, as I mentioned, the problem came back, and I fear it will return again soon.So... Any ideas of possible causes or better fixes?SQL Server details: - Windows 2003, Service Pack 1 - SQL Server 2000, Service Pack 3a - Quad 3.3 GHz Pentium 4 (Hyper-Threaded) - 8 GB RAM---------------------------EmeraldCityDomains.com |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-21 : 21:53:06
|
| What does the sp do? Did you see blocking? Are statistics involved up to date? Do you use sql2k enterprise edition? Run three sql instances on the server or three similar dbs in single instance? How did you set memory in sql? Possible to apply sp4a? |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-07-22 : 00:45:32
|
quote:
Originally posted by rmiao
What does the sp do?
Joins about 8 tables together with a couple of correlated subqueries to retrieve a couple-dozen fields. I know that's not the most efficient, and that's why it has been overhauled, but it has been working within tolerance for some time now. SELECT only, No updates.quote:
Did you see blocking?
Didn't look. Maybe I should have, but it's only doing a select so i didn't think of it. Issue stayed alive for several hours and then immediately disappeared with procedure replacement. Doesn't sound like blocking to me, but will keep in mind for next occurrence.quote:
Are statistics involved up to date?
Ran sp_updatestats the first time it happened with no noticeable effect.quote:
Do you use sql2k enterprise edition?
Yes.quote:
Run three sql instances on the server or three similar dbs in single instance?
The server is actually running four instances of SQL Server, but three of them are fairly light-weight. In the fourth instance, there are 3 databases of identical code and schema that back-end 3 web sites of identical code. Think of it as different major clients get their own copy of the system and their own database. Only one of the three has this issue, and it is not the busiest of the three.quote:
How did you set memory in sql?
Can you be more specific with this question?quote:
Possible to apply sp4a?
Not a chance! These are scheduled for migration to SQL 2005 over the next six months, but that doesn't help me now.---------------------------EmeraldCityDomains.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-22 : 00:50:56
|
| Have you tried sp_recompile or adding the WITH RECOMPILE option to the stored procedure? What does Performance Monitor look like when it slows down?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-07-22 : 00:58:34
|
| Hi Tara!No, I hadn't tried either of those specifically. I'll make a note of that, too. But wouldn't dropping and recreating the procedure have effectively done the same thing? What I thought was really strange was that dropping and re-creating did not appear to do anything until the CREATE was a significantly different procedure.BTW, I'm sure that I am WAY late to the party on this, but...20,000 posts?!?!?! WOW! You're awesome!---------------------------EmeraldCityDomains.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-07-22 : 01:03:49
|
| Yes I would think it did the same thing, but just in case, I wanted to throw out ideas to try. We've got a similar performance issue right now except that DBCC FREEPROCCACHE is resolving it most of the time. On other occassions, we have to continuously kill the Reporting Services connections. A more long-term solution for us to move reports to their own reporting database, but that's still in the testing phases.Thanks, it's mind-boggling that I've got so many posts here. Glad to see you back at SQLTeam, stick around!Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
pootle_flump
1064 Posts |
Posted - 2008-07-22 : 06:28:08
|
Since you get intermittent success with dropping and recreating then it could be parameter sniffing.quote:
Originally posted by AjarnMark
But wouldn't dropping and recreating the procedure have effectively done the same thing? [ADDED BY ME - as running WITH RECOMPILE]
WITH RECOMPILE will ensure a new plan every time it runs. Dropping and recreating will ensure a new plan the first time it is run again. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-07-22 : 15:02:53
|
| Pootle_flump, I'm not quite sure what you mean by "parameter sniffing". If you're referring to the fact that a procedure can be written such that different parameters can cause wide variations in the execution plan, then in this case, that does not happen. The parameters for this procedure are primary key values used to narrow the scope of results returned, but the core of the query remains the same.Also, in our situation, I won't use the WITH RECOMPILE option because this is a frequently used procedure and the likely overall performance hit makes shudder.This problem has not yet appeared today, so perhaps whatever flukey conditions caused it have now passed, but I sure wish I knew what the cause was. I HATE mysteries like this.---------------------------EmeraldCityDomains.com |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-07-22 : 16:16:13
|
| what other sprocs are accessing (select/insert/update) data from the tables used in this sproc?if there are any there could be a blocking issue like rmiao suggested.are your indexes fragmented?there was once an issue i had (i hope i remember it correctly) where the clustered index was fragmented and the select started reading data on pages 1-5. the order of those reads went something like page 1, 5, 4, 2, 3. when the select read page 5 a different update locked page 2 and wanted to lock page 4 when select was reading it. select waited for update to release page 2 and update waited for select to read page 4.funny enough there was no deadlock victim chosen.reindex fixed this just fine._______________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.0 out! |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-07-22 : 22:35:23
|
| quote:-------------------------------------------------------------------------------- How did you set memory in sql?--------------------------------------------------------------------------------Can you be more specific with this question?Since you run 4 instances on the server, there maybe memory pressure. Keep in mind that sql can only use low 2gb memory for everything other than data buffer. |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2008-07-23 : 01:11:41
|
| Mladen, thanks for the tip on index fragmentation. We are in the process of rebuilding all of our database maintenance scripts (previous DBA used the built-in Maintenance Plans, and I don't care for those). I'll make sure that we give due consideration to maintenance tasks for the indexes.rmiao, thanks for the memory tip. This is an area I need to learn better.---------------------------EmeraldCityDomains.com |
|
|
|
|
SQL Timeouts and Procedure Cache?