Timeout Occurs When Selecting From 11 million Row Table

[JohnBates]

Hi experts,

I have 2 identically-indexed tables that are in separate SQL Server 2000 databases residing on the same server.

A manager has built a complex view.....

When we run the view in Enterprise Manager (by right-clicking -->show all) in DB1, it displays the requested rows.

When we run the view in DB2, we get the timeout error.

- (Almost the same numbers of rows in both tables.... approx 11 million in each)

- Each table has the same indexes

My questions:

1) How can I know if the table in DB2 has a corrupted index?

2) When I run the DBCC SHOWCONTIG command, I dont really know how to interpret the info... % Logical Scan Fragmentation, Average Page Density etc What should these figures be if all is OK?


Please note: I'm not asking how to set the timeout value, etc.

Maybe I should go ahead and run the INDEXDEFRAG or DBREINDEX, but I would appreciate some insight before I start that lengthy process.

Thanks ! John

 

[SonOfEmidec1100]

Maybe start with DBCC CHECKDB

From BOL:- For each table in the database, DBCC CHECKDB checks that:
Index and data pages are correctly linked.
Indexes are in their proper sort order.
Pointers are consistent.
The data on each page is reasonable.
Page offsets are reasonable.

Errors indicate potential problems in the database and should be corrected immediately.

 

[JohnBates]

thanks SonOfEmedic1100,

DBCC CHECKDB didn't identify any problems.

I also ran DBCC SHOWCONTIG for same table name in both databases. There are big differences.

Results of DBCC SHOWCONTIG(table name):

DB1 (OK, no Timeout) DB2(view gives Timeout)
Pages scanned 84,275..............231,000
No logical scan fragmentation.....4.1 %
Avg Bytes free per page: 290 .....76

With the same number of rows (11,800,000) in both tables and the same indexes... the differences in 'Pages scanned' and 'Avg Bytes free per page' is surprising.

In DB2, where the table timesout, I have ran DBCC INDEXDEFRAG on a couple of major indexes and then shrunk the db. Still timesout and SHOWCONTIG shows same statistics.

I really don't understand why the 2 tables are so different in terms of statistics and performance.

Can anyone think of anything else I can do?

Thanks, John

 

[JohnBates]

... here is some more precise info:
DBCC SHOWCONTIG ([Fund Transactions])

** The following stats are for the table that gives the query Timeout error:

WHAT is the number below and why so much larger than in the other table?
Table: 'Fund Transactions' (1044914794); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 233938
- Extents Scanned..............................: 29345
- Extent Switches..............................: 29344
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.65% [29243:29345]
- Logical Scan Fragmentation ..................: 4.84%
- Extent Scan Fragmentation ...................: 0.17%
- Avg. Bytes Free per Page.....................: 75.2
- Avg. Page Density (full).....................: 99.07%
_______________________
The table below is OK, query doesn't timeout:
DBCC SHOWCONTIG scanning 'Fund Transactions' table...
Table: 'Fund Transactions' (228195863); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 341492
- Extents Scanned..............................: 42756
- Extent Switches..............................: 42755
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.84% [42687:42756]
- Logical Scan Fragmentation ..................: 0.01%
- Extent Scan Fragmentation ...................: 0.10%
- Avg. Bytes Free per Page.....................: 134.7
- Avg. Page Density (full).....................: 98.34%


Both tables have the same exact structure in terms of columns and indexes and the row count is very close too.
I ran DBCC CHECKDB and it doesnt report any errors.

Do you notice anything that might cause the first table to perform slower than the second table?

They never said it would be easy... !
Thanks, John

 

[TheBugSlayer]

On both tables Scan Density is close to 100%, which means there is very little fragmentation (page extents are very contiguous). Your pages (they make up the extent) are almost full too, the higher the percentage the better. Each table seems to be quite optimal.

Now, are both databases part of the same filegroup and stored on the same disk? Are the indexes on separate filegroups/disks?

Are the two databases used with the same intensity? Is AutoClose on on any one of them?

 

[JohnBates]

Thanks for the feedback TheBugSlayer,

"...are both databases part of the same filegroup and stored on the same disk? Are the indexes on separate filegroups/disks?"

* We have a 3 disk RAID-5 configuration. The server is new with a 3 Ghz Xeon processor. 1GB RAM (we will install more soon). Yes, all are using the same filegroup PRIMARY. *


"Are the two databases used with the same intensity?"

*it varies *

"Is AutoClose on on any one of them?"

* No, AutoClose and AutoShrink are OFF *

John