Query queued

[DzU]

Hi all,
I've a problem with sql server 2000 EE.

I've a DB with a table with lots of records. I run a SELECT from
query analyzer, and I've results in 10 seconds. Perfect.

When I run the query two times in the same table, seems like
it been queued. Result in 20 seconds.

With another query, I've result in 40 seconds. If I run this query 2
times, I've results in 80 seconds.

How can it be? Does Sql Server queue the query?
thanks, and sorry for bad english! :D

[Erland Sommarskog]

DzU (cl*****@scartozzetto.it) writes:

I've a DB with a table with lots of records. I run a SELECT from
query analyzer, and I've results in 10 seconds. Perfect.

When I run the query two times in the same table, seems like
it been queued. Result in 20 seconds.

With another query, I've result in 40 seconds. If I run this query 2
times, I've results in 80 seconds.

How can it be? Does Sql Server queue the query?

I'm really sure with what you mean that you run the query twice. If you
do:

SELECT ... FROM tbl WHERE ...
SELECT ... FROM tbl WHERE ...

it's not entirely surprising that this takes twice the timeas ii would be
to run the query a single time. (Alhough often, it's faster the second
time, since the data is in cache the second time.)

Or do you mean that you run the queries in parallel from two different
sessions?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[DzU]

Il Tue, 03 Apr 2007 22:04:06 +0000, Erland Sommarskog ha scritto:

it's not entirely surprising that this takes twice the timeas ii would
be to run the query a single time. (Alhough often, it's faster the
second time, since the data is in cache the second time.)

Or do you mean that you run the queries in parallel from two different
sessions?

Yes, I run the query in two different session. It seems that the second
wait for the first and then start running.

It's a strange behaviour.

[Erland Sommarskog]

DzU (cl*****@scartozzetto.it) writes:

Yes, I run the query in two different session. It seems that the second
wait for the first and then start running.

And the first window returns in 20 seconds, and the second 20 seconds later?

Unless you are using some XLOCK hints, I would rather suspect the network
communication. Or are you running these queries on the local server? What
happens if you run the two queries from two different clients.

On thing you could try is from a third window run sp_who2, and study the two
processes. I would expect both to consume CPU and physical IO.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx