Sql Query Sporadically Slow

[klines]

We have Sql Server 2000 and some days performing a query is very slow (takes 15-20 seconds to pull up). It maxes the server processor when running. We turn on profiler and see that it takes 700,000 reads to get the data.

It is typically slow all day Monday and Tuesday morning. Then Tuesday afternoon it gets fast (less than 1 second to pull up) and remains fast through the week. Profiler returns 30 reads.

The days that are fast or slow vary. We can have a whole week of fast or a whole week of slow.

We have analyzed several things - performance on sql, hardware, but cannot come up with anything conclusive between the good and bad days. If it were a bad index we feel that it would not clear up on the same day.

Any advise on how to troubleshoot is greatly appreciated.

Thanks - Kristi

 

[MDXer]

do you have any db optiizations that get ran monday night or tuesday morning?

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck

 

[SQLSister]

I would look at what else is running at the same time it gets slow. Perhaps someo ther processing is causing the problem.

Questions about posting. See faq183-874

 

[klines]

There are 2 extended stored procedures that run M-Th but do not get run on the weekend. We do see slowness on different days through the week that do not coincide with different procedures run.

We also check enterprise manager for different locks or processes running that may slow us down. We talked with our users to see what gets run that could cause the slowness. We cannot pinpoint anything.

From the same database we can run queries from other tables that are fast while this query is slow. There are other queries using the same stored procedure that are fast when this is slow.

So, we don't think it is indexes because other queries pull from the same spot and are fast. We don't think it is server related because other processes run fine. We don't see any deadlocks that are slowing this down.

Any other ideas or places we can troubleshoot or monitor?

 

[nigelrivett]

Try running a job overnight to update statistics and mark tables for recompilation. You'll probably find you only have to do it on one table.
Sounds like the data in the table is changing and the statistics don't cope with it any more and it is causing a table scan.

Your query is probably speeding up because eventually the tsatistics will be automatically updated due to the table settings.


======================================
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.

 

[Kressmark]

Hi,

Can you extract the query taking a long time?

In that case you could run it in the query analyzer with

set statistics profile on
go

and maybe that will give you some hints on why it is slow.

Also, could you post the query here? It might be possible to re-write it for better performance.

As suggested by nigelrivett you could run

update statistics <table> with fullscan

when the query is slow to see if better statistics will speed it up. Use

set statistics time on
go

to see the execution times. Finally do not forget to turn it all off with

set statistics profile off
set statistics time off
go

/mk

 

[klines]

Thanks for your tips. Since I have posted, we have been fast all week, but I'm sure we will see slowness soon. It is very sporadic. I will give these items a try next time we slow down again.

This query is built into a front end application that we do not have much control over. We have another application that was built that also pushes data into the tables. From what Nigelrivett said, it may be when we put data in from this other app, it may be changing the table causing the slowness. We do not know much about sql being able to correct this on its own. We do sometimes see it speed up all on its own.

Do you have any more information on how it could correct itself? Or if there is something we can run live that could correct it on the fly rather than waiting for sql to do it?

 

[nigelrivett]

If it is a statistics problem and you have it set to automatically upadate when they get far enough out they will be regenerated.
A nightly job should help if that is the case.

======================================
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.

 

[nigelrivett]

You can tell by looking at the query plan when it is slow.

======================================
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.

 

[Kressmark]

klines,

Even if you can not extract the query from the application source code you could probably use SQL profiler to catch the statement.

/mk