469,610 Members | 1,649 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

Cumulative wait time on server replies ???

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
Jul 20 '05 #1
4 6881
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
Jul 20 '05 #2
> > 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

Jul 20 '05 #3
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
Jul 20 '05 #4
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
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Matt Larkin | last post: by
1 post views Thread by Victor | last post: by
15 posts views Thread by Snuyt | last post: by
reply views Thread by James Hallam | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.