Exec time variation by user - SQL Server Forums

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 Development (2000)

Exec time variation by user

Author

Topic

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-06 : 16:44:31

I've seen this before... I don't remember the cause..

this query

EXEC dbo.QR_DeptCompletion @CallerAdminID=2209,@CourseID=4032

[Microsoft][ODBC SQL Server Driver]Timeout expired

Only times out (31 seconds) when executed from the ASP page running as a public user.

If I cut and paste the query above into QA, using the same Public user/password it executes in 1 second every time.

Any ideas?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 16:47:05

Well it's not this:

http://weblogs.asp.net/jgalloway/archive/2004/05/05/126958.aspx

since it works fine in QA.

How much data is being returned? You might have a network bottleneck. Is the stored procedure optimized?

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-06 : 16:54:31

The execution plan is great. It returns only about 15 rows of data. Lots of joins totaling completed users by department.

Seems to break when querying a specific client, and only on the web page. The query is posted as an HTML comment on the web page, cut and paste into QA and it execs in 1 second - using the same username / password to access the DB as the web.

Very consistent. I just did it again before I wrote this line of text.

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 16:58:49

This smells of a network problem.

Do you know what the average row size returned is for this query?

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-06 : 17:03:54

This query returns very little data in a row. Department, total users, completed, and a few more character columns.

I can query another client in the same DB, with more, or fewer users, and the query runs great. Returns all rows in under a second.

I think the exec plan is getting scrubbed for some reason for this particular client. That, or something is locked ???

This is a production DB, but it is not busy today. I rebuild the indexes every night. Any experiment I could try that would give some information?

Sam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 17:11:24

I would create a trace to capture what is going on when you run the query from the web. I would then send the trace results through the index tuning wizard. I would also UPDATE STATISTICS, recompile the stored proc, and run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS.

Tara

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-06 : 17:34:02

In what order exactly?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-06 : 17:35:57

Try UPDATE STATISTICS first. Then recompile the stored proc, and run DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS.

If that doesn't work, then run the trace results through the index tuning wizard. I doubt this will help though as you say the execution plan is fine. But it's possible SQL Server has some recommendations.

Tara

cas_o
Posting Yak Master

154 Posts

Posted - 2004-05-06 : 18:04:16

Try adding Rst.Movenext in the ASP script!

;-]... Quack Waddle

SamC
White Water Yakist

3467 Posts

Posted - 2004-05-06 : 22:47:23

Thanks Tara,

I just dropped and added the procedure. It works like a champ now. No need to update statistics.

Very strange problem. Argh !

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources