Hello David,

That's really strange. Have you let it generate the execution plan or
does it fallover?

Sometimes with large tables it takes along time to generate a plan.
Rather than using query analyzer to generate the plan try the text
version.

Try the query below:


SET SHOWPLAN_TEXT ON
GO
SELECT COUNT_BIG(1) FROM dbo.SalesOrderDetail WITH (NOLOCK)
GO
SET SHOWPLAN_TEXT OFF


The big count returns as a Big INT instead of INT. This may help your
row count if the number of row exceeds the INT range.


Hope this helps

Myles Matheson
Data Warehouse Architect

If your clustered index is on a natural key (ex: FirstName, LastName,
SSN) as opposed to a sequentially incremented key (such as an identity
column), then data and index fragmentation will result over time as SQL
Server attempts to re-organize data pages in physical order. This can be
especially noticable with a 2 billion row table. Run DBCC SHOWCONTIG on the
table to see what data or index fragmentation, if any, exist. You can use
DBCC INDEXDEFRAG or even better run a script to drop / recreate the indexes.
Remember to re-index the clustered index before the non-clustered indexes.
This may also be the time for you to consider physically vertical
partitioning of your data into seperate tables. Perhaps the data can be
split by year, customer, etc. Read up on how to implement a "partitioned
view" of multiple tables that share the same record structure.

[quoted text, click to view]

Hi Davud,
don't you think you might have a few too many rows in one table for SQL
Server? Is there any reason why you would not want to partition the
table or not want to create some sort of archive....I would be
surprised if you can get any kind of reasonable performance for any
reasonable operations on 2 billions rows.....and how long does it take
to back up? Or are you not backing it up?.. ;-)

Best Regards

Peter