Queries Occasionally Slowing Down

Giganews Newsgroups
Subject: Queries Occasionally Slowing Down
Posted by:  bob laughland (peter.mcclymo…@gmail.com)
Date: Mon, 17 Nov 2008

Hi All,

I have a system which is running SQL queries almost constantly against
a database. One problem I am having is that sometimes queries become
slow for no reason. The query looks like this,

select * from DataStore where dataStoreName = 'name' and rowid in
(select RowId from DataStore where dataStoreName ='name' and name =
'keyname' and value = 'keyvalue') order by rowid

So the query is nothing too special. This is a summary of what I have
seen, and what I know

1) The query 99% of the times runs below 1 second
2) The other 1% of the time it will take anywhere from 5 - 20 seconds
to run
3) I can copy and paste the slow query out of the log file (I am
logging the slow queries) and it runs instantly in management studio
4) I have optimised the query adding an index that was suggested to me
by management studio, and the query plan looks quite good now

I ran the profiler to see what I could see and I found something
interesting. The profiler has a number of columns per SQL statement
that is run, CPU, Reads, Writes, Duration.

For the queries that are running fine the stats are like this

CPU - between 0 and 16
Reads - between 20 and 70
Writes - 0
Duration - between 2000 and 60000

But for one of the queries I caught which is running very slow the
stats were this,

CPU - 2574
Reads - 8944
Writes - 24
Duration - 9243528

So yeah, the difference is quite dramatic, and the speed difference is
quite dramatic too.

What should I do next?