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 |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2007-04-12 : 17:59:19
|
| I have a query that for some reason runs for 30-40 minutes every once in a while. The Query is run many times during the day and only takes about 1-2 seconds. I seem to recall an issue with SP4 that causes behavior like this. The one thing that I did notice is that the profiler reported the CPU and Reads to be much higher than normal. Normally the the query has a CPU count of 800 and a Reads count of 2700. In the case today where it took 30-40 mintues to return 4 rows of data the CPU was at 1.1 million and the Reads were at 216 million. Anyone run into an issue like that before? Any suggestions on where I should start looking?Thanks |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-04-12 : 22:26:47
|
| u need to examine the execution plan, then indexes. what else is happening on server at time when it takes long time? does the query modify the data? any blocking? tried sp_who2 and sp_lock when run long? ran profiler trace to see anything else going on? |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2007-04-13 : 09:50:44
|
quote:
Originally posted by russell
u need to examine the execution plan, then indexes. what else is happening on server at time when it takes long time? does the query modify the data? any blocking? tried sp_who2 and sp_lock when run long? ran profiler trace to see anything else going on?
Yeah, I looked into all the easy DBA 101 stuff before posting. Mainly I posted out here cause I thought I heard something about this being a bug with SP4. There was no blocking and the other 100 or so users were not complaining at the time. The execution plan looked ok when I ran the estimated and actual. If it continues to happen I'll snag the plan that the user got from profiler and see if it differs and I have a few other things I'll look into. |
|
|
|
|
|
|
Slow query?