Cumulative wait time on server replies ???

[Yannick Turgeon]

Hello all,

I've got a query which suddently became very slow. It now takes about 10
secs instead of 2 secs.

I've got to identical DB (one is for test and the other is production). The
query is slow only in production.

When running this query in both DB and looking at execution plan,
statistics, etc, the onle difference is the Cumulative wait time on server
replies.

In test DB, I get the value: 2200
And in production DB: 1.22344e+009

What does this mean concretly? What do I have to do to solve this problem?

TIA.

Yannick

PS I'm using SS2000 SP3 on NT4.0

[Erland Sommarskog]

Yannick Turgeon (no****@nowhere.com) writes:

I've got a query which suddently became very slow. It now takes about 10
secs instead of 2 secs.

I've got to identical DB (one is for test and the other is production).
The query is slow only in production.

When running this query in both DB and looking at execution plan,
statistics, etc, the onle difference is the Cumulative wait time on server
replies.

In test DB, I get the value: 2200
And in production DB: 1.22344e+009

What does this mean concretly? What do I have to do to solve this problem?

Books Online says:

Cumulative amount of time the driver spent waiting for replies from the
server.

I would suppose that if you have had that query window open for a long
time, this number becomes quite high. I doubt that it has anything to
do with the slowness of your query.

Why your query is suddently slow, I have no idea, but if you have identical
plans on two servers with identical data (I assume!), then maybe you should
check so that there is no other activity on the production machine.

DBCC SHOWCONTIG on the involved tables may show some difference in
fragmentation.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Yannick Turgeon]

> > In test DB, I get the value: 2200

And in production DB: 1.22344e+009

What does this mean concretly? What do I have to do to solve this

problem?
I would suppose that if you have had that query window open for a long
time, this number becomes quite high. I doubt that it has anything to
do with the slowness of your query. It corresponds quite acuratly with the time I'm waiting though. But maybe it
is simply the consequences of something else.
Why your query is suddently slow, I have no idea, but if you have identical plans Yes they are.
on two servers with identical data (I assume!), On the same server with data slightly different: Test env. being 1 or 2 days
older.
then maybe you should
check so that there is no other activity on the production machine.

DBCC SHOWCONTIG on the involved tables may show some difference in
fragmentation.

Test
------------
DBCC SHOWCONTIG scanning 'ReservationTravaux' table...
Table: 'ReservationTravaux' (1920725895); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 158
- Extents Scanned..............................: 21
- Extent Switches..............................: 20
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 95.24% [20:21]
- Logical Scan Fragmentation ..................: 0.63%
- Extent Scan Fragmentation ...................: 38.10%
- Avg. Bytes Free per Page.....................: 808.3
- Avg. Page Density (full).....................: 90.01%

Production
------------
DBCC SHOWCONTIG scanning 'ReservationTravaux' table...
Table: 'ReservationTravaux' (1920725895); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 165
- Extents Scanned..............................: 21
- Extent Switches..............................: 20
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [21:21]
- Logical Scan Fragmentation ..................: 7.88%
- Extent Scan Fragmentation ...................: 19.05%
- Avg. Bytes Free per Page.....................: 786.1
- Avg. Page Density (full).....................: 90.29%
What do you think about that? Looks like there are a lot of differences
between only those two values:
- Logical Scan Fragmentation
- Extent Scan Fragmentation

Yannick

[Erland Sommarskog]

Yannick Turgeon (no****@nowhere.com) writes:

Test
------------
DBCC SHOWCONTIG scanning 'ReservationTravaux' table...
Table: 'ReservationTravaux' (1920725895); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 158
- Extents Scanned..............................: 21
- Extent Switches..............................: 20
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 95.24% [20:21]
- Logical Scan Fragmentation ..................: 0.63%
- Extent Scan Fragmentation ...................: 38.10%
- Avg. Bytes Free per Page.....................: 808.3
- Avg. Page Density (full).....................: 90.01%

Production
------------
DBCC SHOWCONTIG scanning 'ReservationTravaux' table...
Table: 'ReservationTravaux' (1920725895); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 165
- Extents Scanned..............................: 21
- Extent Switches..............................: 20
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [21:21]
- Logical Scan Fragmentation ..................: 7.88%
- Extent Scan Fragmentation ...................: 19.05%
- Avg. Bytes Free per Page.....................: 786.1
- Avg. Page Density (full).....................: 90.29%
What do you think about that? Looks like there are a lot of differences
between only those two values:
- Logical Scan Fragmentation
- Extent Scan Fragmentation

Books Online says that these two should be as low as possible,
preferably close to 0, but below 10 is OK. From this the 38% percent
of Extent Scan Fragmentation on Test is the most scary-looking.

In any case, this is not the answer to why your query runs so much
slower in production. However, if there are additional indexes for the
table, you should run SHOWCONTIG for these as well. The above is only
for the clustered index.

The only other possibility I can think of is that you have a blocking
issue in production. Rather than going for a cup of coffee next you're
waiting for the query, run an sp_who and see if the Blk column has
anything to offer.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Yannick Turgeon]

Erland,

Thanks for your support. Yesterday I've restarted my server and the problem
is now solved. I'll look to defragment my indexes on this table and probably
some other too.

Yannick