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 |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2003-02-22 : 09:14:52
|
| I've got a stored procedure with a complex query that selects from a table of logged URLs to calculate the time a specific visitor spends on each page.Average execution time is 4 seconds.Yesterday, for unknown reasons, the web page that executed that procedure began returning SQL timeout. In query analyzer, I executed the procedure and it took about 45 seconds to execute.Subsequent executes of the same procedure were 4 seconds.Does this sound like recompile time overhead?Sam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-22 : 09:59:44
|
| The recompile might've picked a bad plan, and a subsequent recompile picked a better one, but it doesn't reflect the actual time to recompile a procedure.It's also more likely that some other process was running that might've held locks that blocked the web query from running full steam. If you've got auto update statistics or scheduled jobs running that might explain it. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-02-22 : 13:26:28
|
| If I drop the procedure and recreate it - the execution time grows to 5 seconds. I can't recreate the 45 second execution problem.It sure sounds like a usage conflict. URLs being inserted in the table at the same time this procedure is running.The evidence against this theory is that once I let the stored procedure run to completion in query analyzer, subsequent execution times were 4 seconds.I'll check it again during a busy period.Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-22 : 16:18:46
|
| Then it probably was because of cached data. The 45 second time might've had to reach out to disk to get data, while 4 seconds ran off of the data cache. You can't run a procedure once, and then run it again right after and make any kind of judgement about how it performs or should perform. Best way to tell how BADLY a sproc will run is to run these before you execute it:DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERSThose will clear the procedure and data caches, respectively. Whatever performance you see in the sproc will reflect its poorest performance, and you should set your expectations accordingly. Performance can only improve from there, once caching occurs. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-02-22 : 17:46:09
|
| First time I've tried DBCC DBCC FREEPROCCACHEDBCC DROPCLEANBUFFERS consistently gives me an 8 second execution time for the first execution of the stored procedure, followed by 4 second execution on subsequent executions.Maybe this problem is gone for a while. If it occurs again, I'll know it and investigate further then.By the way, this table has a clustered index, lotsa rows. Any chance the problem is linked to this?Thanks,Sam |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-02-22 : 18:34:13
|
| If it's not clustered on the columns you're searching, then yeah, it might be jumping around a lot to find them. It also depends on what other columns you're pulling too. If you only need a few columns, consider adding an index that contains all of them. This is a covering index, and the query optimizer may be able to pull the data from the index instead of the entire table. If you do create a covering index, make sure to make the most frequently searched column first in the list, then the 2nd most, etc. |
|
|
|
|
|
|
|
|
Surprise ! ( Needs to recompile )