Re: 2 Billion records a table

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:3DBF60C2-0D74-461D-B31A-1D76491054FD@xxxxxxxxxxxxxxxx
> 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@xxxxxxxxx" 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
>>
>>


.

Relevant Pages

  • Re: Leftmost column in an index
    ... That's one example where the optimizer creates these _WA% statistics to ... which is the cost of a table scan. ... But this query: ... >> would likely change to a table scan or clustered index scan. ...
    (microsoft.public.sqlserver.programming)
  • Re: Snapshot Agent Updates statistics on all PK indexes even when there is NOTHING to do!!
    ... the snapshot agent will only run update stats if it ... couldn't find any *statistics* on the leading clustered index column but I ... Wouldn't plan guides be a better solution to ensure more stable\robust ...
    (microsoft.public.sqlserver.replication)
  • Re: Snapshot Agent Updates statistics on all PK indexes even when there is NOTHING to do!!
    ... will "empty" populated statistics. ... it couldn't find any *statistics* on the leading clustered index column ... Wouldn't plan guides be a better solution to ensure more ... where I can see how the snapshot agent update stats behavior will affect ...
    (microsoft.public.sqlserver.replication)
  • Re: Table count
    ... Just to add to Anith Sens's comments, the field name in sysondexes table is ... rowcnt and the value in that field is dependable only for Clustered Index and ... clustered index, then your requirement may get statisfied without any ... > statistics, it can be reasonable. ...
    (microsoft.public.sqlserver.server)
Loading