MS SQL Data Warehousing - 2 Billion records a table

Author

2 Billion records a table

David Liu

2005-07-21, 11:23 am

I have a problem in one of my production server:
the rows number is exactly 2 billion records and when you do delete, insert,
that row number was not changed( use sp_spaceused to get the row number)
when you do the DBCC check, you got 0 rows in xxxxxxx pages.

I know SQL Server do not have limit on rows per table, but this is really
wired

The table only can be query and DML when you specify the cluster index key
on the where clause anyway.

Does anyone met this before

Thanks
--
David Liu
Sr. DBA
MCSE/MCDBA
Moneris Solutions

Myles.Matheson@gmail.com

2005-07-21, 8:23 pm

Hello David,

Have you tried doing a straight row count?

I think the sp_spaceused uses the table statistics to return row
counts.

SQL server may not have updated the table statistics after operation
was completed. This is a common problem with large tables.

Hope this Helps

Myles Matheson
Data Warehouse Architect

David Liu

2005-07-22, 3:23 am

Thanks for the reply.
Major problem is when you do select * from that table, you can not query out
( even generate estimate execute plan ), the process hang on there.

--
David Liu
Sr. DBA
MCSE/MCDBA
Moneris Solutions

"Myles.Matheson@gmail.com" wrote:

> Hello David,
>
> Have you tried doing a straight row count?
>
> I think the sp_spaceused uses the table statistics to return row
> counts.
>
> SQL server may not have updated the table statistics after operation
> was completed. This is a common problem with large tables.
>
>
>
>
> Hope this Helps
>
> Myles Matheson
> Data Warehouse Architect
>
>

Myles.Matheson@gmail.com

2005-07-22, 3:23 am

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

2005-07-22, 11:23 am

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.

"David Liu" < DavidLiu@discussions
.microsoft.com> wrote in message
news:3DBF60C2-0D74-461D-B31A- 1D76491054FD@microso
ft.com...[color=darkred]
> Thanks for the reply.
> Major problem is when you do select * from that table, you can not query
> out
> ( even generate estimate execute plan ), the process hang on there.
>
> --
> David Liu
> Sr. DBA
> MCSE/MCDBA
> Moneris Solutions
>
>
>
> "Myles.Matheson@gmail.com" wrote:
>

Peter Nolan

2005-07-22, 8:23 pm

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

Sponsored Links