This is the first time I'm confronted with this type of problem,
probably since I was not using larger databases before, but now I'm
stuck and hoping someone can point me in the right direction.

I have a table in a database on my SQL Server 2005 Standard edition
server, running on a W2k3 box with 4Gb memory, 1 quad core processor.
The table has 1.2 million records. A query like "select count(id) from
table" returns almost instantly. When I change the query to this
"select count(id) from table where columnA = 'test'" the query times
out. Execution error timing out. But it gets worse. When I wait for
ten minutes or something and retry, this second query also returns
almost instantly. From this point I start running these type of
queries a lot, this works well for five or ten minutes, then suddenly
stops and returns timeouts again for the exact same query. At this
point the processor load is 1 or 2 procent (basically flatlining) and
memory usage is somewhere around 1.2 Gb of which SQL uses 800Mb. No
other big things on the server. The queries timeout both using
Management Studio and a JDBC connection from my application server.

When this behavior was constantly the same, I would not be surprised.
Maybe I need an index on that columnA. But it is sometimes fast, then
ten minutes later it is a timeout. Can't explain it. Is there anybody
with a quick tip?

It is either blocking (sp_who, sp_who2, Current Activity etc) or that you get different execution
plans for the two cases.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


<ivo@ritense.com> wrote in message
news:28544efd-5e74-4bea-9c2d-5d87ffc27fea@x35g2000hsb.googlegroups.com...
> This is the first time I'm confronted with this type of problem,
> probably since I was not using larger databases before, but now I'm
> stuck and hoping someone can point me in the right direction.
>
> I have a table in a database on my SQL Server 2005 Standard edition
> server, running on a W2k3 box with 4Gb memory, 1 quad core processor.
> The table has 1.2 million records. A query like "select count(id) from
> table" returns almost instantly. When I change the query to this
> "select count(id) from table where columnA = 'test'" the query times
> out. Execution error timing out. But it gets worse. When I wait for
> ten minutes or something and retry, this second query also returns
> almost instantly. From this point I start running these type of
> queries a lot, this works well for five or ten minutes, then suddenly
> stops and returns timeouts again for the exact same query. At this
> point the processor load is 1 or 2 procent (basically flatlining) and
> memory usage is somewhere around 1.2 Gb of which SQL uses 800Mb. No
> other big things on the server. The queries timeout both using
> Management Studio and a JDBC connection from my application server.
>
> When this behavior was constantly the same, I would not be surprised.
> Maybe I need an index on that columnA. But it is sometimes fast, then
> ten minutes later it is a timeout. Can't explain it. Is there anybody
> with a quick tip?

One more item to Tibor's list. It's also possible that the timeout coincided
with a large autogrow.

Linchi

"ivo@ritense.com" wrote:

> This is the first time I'm confronted with this type of problem,
> probably since I was not using larger databases before, but now I'm
> stuck and hoping someone can point me in the right direction.
>
> I have a table in a database on my SQL Server 2005 Standard edition
> server, running on a W2k3 box with 4Gb memory, 1 quad core processor.
> The table has 1.2 million records. A query like "select count(id) from
> table" returns almost instantly. When I change the query to this
> "select count(id) from table where columnA = 'test'" the query times
> out. Execution error timing out. But it gets worse. When I wait for
> ten minutes or something and retry, this second query also returns
> almost instantly. From this point I start running these type of
> queries a lot, this works well for five or ten minutes, then suddenly
> stops and returns timeouts again for the exact same query. At this
> point the processor load is 1 or 2 procent (basically flatlining) and
> memory usage is somewhere around 1.2 Gb of which SQL uses 800Mb. No
> other big things on the server. The queries timeout both using
> Management Studio and a JDBC connection from my application server.
>
> When this behavior was constantly the same, I would not be surprised.
> Maybe I need an index on that columnA. But it is sometimes fast, then
> ten minutes later it is a timeout. Can't explain it. Is there anybody
> with a quick tip?
>

Thanks a lot both for the tips. I can now better reproduce the error
and it is a memory issue.

When I run the query for the first time, I see memory consumption go
up with 600Mb immediately. After that it runs smoothly, but memory
consumption is going up little by little. Then when it hits total mem
usage of approximately 1.65 Gb, it stops. Other queries still run, but
this one is dead. When I restart SQL Server, it all starts over again.

I guess that when I run a couple of these big queries together, the
problem starts faster.

Will investigate further, but at least I know what to look for.

Again, thanks for your .