Index loss, but only on IIS calls

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Index loss, but only on IIS calls

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-14 : 19:17:01

This is a problem I've had for over a year. I usually resolve it by marking all stored procs for recompilation.

The following query call is generated by a web page to return summary values for a page footer:

declare @P1 varchar(100)
set @P1=NULL
declare @P2 int
set @P2=NULL
declare @P3 int
set @P3=NULL
declare @P4 int
set @P4=NULL
declare @P5 varchar(100)
set @P5=NULL
declare @P6 int
set @P6=NULL
declare @P7 int
set @P7=NULL
declare @P8 int
set @P8=NULL
declare @P9 int
set @P9=NULL
declare @P10 int
set @P10=NULL
declare @P11 int
set @P11=NULL
declare @P12 int
set @P12=NULL
declare @P13 varchar(1000)
set @P13=NULL
exec dbo.AD_CourseStats 154409, 6732, @P1 output, @P2 output, @P3 output, @P4 output, 
@P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, @P11 output, 
@P12 output, @P13 output
select @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13

Normally, this query runs in under a second. The execution plan is great. Today, the bug is back and before I recompile the stored proc, I ran SQL profiler to get further information.

Here's the query result from profiler when called from the web page via IIS:
CPU 40000
READS 684306
WRITES 0
DURATION 40000

The large number of reads and the resulting timeout indicates the stored procedure is not using an index to do the calculation. What is odd, is that the proc works fine when called from Query Analyzer. Read on...

If I cut and paste the query into query analyzer, logged in as the same user the web uses, the query runs fine... here's the profiler data when the query is called from QA:
CPU 234
READS 884
WRITES 0
DURATION 483

This is very repeatable, and is a major problem for the web / database performance. Fortunately, it is easily resolved by marking all stored procedures for recompilation.

By the time you read this post, I'll have marked all stored procedures for recompilation, so I'll have to wait for the next occurance before performing any further tests.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2005-08-14 : 20:53:00

Try sp_recompile on the sp.
If that doesn't help dbcc freeproccache.

You might want to update statistics on the table and recompile the SP each night.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SamC
White Water Yakist

3467 Posts

Posted - 2005-08-14 : 23:00:34

quote:
Originally posted by nr

Try sp_recompile on the sp.
You might want to update statistics on the table and recompile the SP each night.

I have a job that does exactly that. It doesn't seem to stop this bug from coming in at odd times, and it doesn't explain why the proc would fail when called from IIS but not when called from QA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources