DBCC REINDEX on table with 970GB data and 10GB index with 5 billions rows of count

  • As per topic :

    --------------------------------------

    SQL SERVER 2005 SP3 ENT 64BIT

    WINDOWS SERVER 2003 ENT SP2

    Drive total size Freespace

    C:\ 129 GB 113 GB - BINARY

    D:\ 272 GB 191 GB - SQL LOG FILE LOCATION

    E:\ 1.7 TB 39.1 MB -SQL DATA FILE LOCATION

    sp_spaceused

    Name rows reserved data_sizeindex_sizeunused

    FileList_T 5303873667971053408 KB960,138,384 KB10,818,096 KB96,928 KB

    sp_helpdb 'FILELIST'

    name fileidfilename Filegroupsize maxsizegrowth

    FILELIST 1E:\MSSQL\Data\FILELIST.mdF PRIMARY1834926144 KBUnlimited10240 KB

    FILELIST_Log 2D:\Databases\FILELIST.ldf NULL104985600 KBUnlimited102400KB

    FILELIST

    ALLOCATED SPACE : 1,623,174.88 MB

    FREE SPACE : 546,475.69 MB (33%)

    FILELIST_LOG

    ALLOCATED SPACE : 59392.50 MB

    FREE SPACE : 53831.14 MB (90%)

    -------------------------------------------

    Question : I am tyring to reindex the table FileList , based on the finding above, i failed for the first time when the drive E is failing up when runninng command DBCC DBREINDEX (FileList_T,fileidx1) with no_infomsgs.

    1. I need to add additonal space into the mdf files , please advice how much space should be added ?

    2. If i am using sort in tempDB , how much spaces hould be added to tempDB data and log files and the FIlelist data file ?

    3. i know we should not allow such big files located in one mdf . If i would like to add another mdf file to same filegroup , run the dbcc reindex , when it is completed , dbcc shrinkfile with empty data file option . Will it re-create the fragmentation ?

    4. If i can only have a temporarily additional 1 TB or 2 TB let say for 1 month , what option do you have ?

  • stanley wong-314201 (2/12/2012)


    1. I need to add additonal space into the mdf files , please advice how much space should be added ?

    You need roughly the size of the index free in the data file if you're using sort_in_tempDB, 120% of the size of the index if not. These are rough figures, not exact.

    2. If i am using sort in tempDB , how much spaces hould be added to tempDB data and log files and the FIlelist data file ?

    You need roughly 20% of the size of the index for sort space. This is a rough figure

    3. i know we should not allow such big files located in one mdf . If i would like to add another mdf file to same filegroup , run the dbcc reindex , when it is completed , dbcc shrinkfile with empty data file option . Will it re-create the fragmentation ?

    Yes, it will.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The table have only one index which is a cluster index.

    So, if i reindex these table using DBCC DBREINDEX, Is the size of the index equal the size of the data ?

    reserved 971,053,408 KB

    data_size 960,138,384 KB

    index_size 10,818,096 KB

    unused 96,928 KB

  • Yes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I was curious what approach would be used to fix this particular situation.

    Not trying to hijack, I don't have anything this large :-D.

    The overall goal is shortest amount of down time and top performance.

    At a high level,

    Need to rebuild indexes, and plan regular maintenance approch.

    Need to spread over multiple files possibly filegroups if supported (one per CPU equal size?)

    SQL Standard vs. Enterprise limitations or different approch?

  • Maramor (2/13/2012)


    Need to spread over multiple files possibly filegroups if supported (one per CPU equal size?)

    The 1 per CPU, equal size is an old and outdated recommendation for TempDB only. More recently the recommendation is 1/4 to 1/2 the number of files as CPU cores, don't go above 8 without a good reason.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Good to know.

    I need to set up a few tests in my environment to see the difference 🙂 Thats why I put a ? mark as I was unsure about those recemendations.

  • Does DBCC DBREINDEX (dbo.YourTableName, PK_YourTableName ) similar to CREATE UNIQUE CLUSTERED INDEX PK_YourTableName ON dbo.YourTableName(YourPKFields) WITH (DROP_EXISTING = ON) ON [NewFilegroup]

    ? Except it is rebuild the index and moved data into diffrent filegroup ?

    Given the table details below, Currently TableA located in Primary filegroup , if i am going to move tableA with cluster index key from existing file group to ANOTHER new File group , what is the data size required for my new filegroup ?

    Table Details -

    rows5303873667

    reserved 971,053,408 KB

    data_size 960,138,384 KB

    index_size 10,818,096 K

  • rows5303873667

    reserved 971,053,408 KB

    data_size 960,138,384 KB

    index_size 10,818,096 KB

  • I just check on my MSSQL table and found out the cluster index is build with multiple column .

    UNIQUE CLUSTERED INDEX [fileidx1] ON [dbo].[FileList]

    [Account] ASC,

    [PathId] ASC,

    [FileName] ASC,

    [FileNameLength] ASC,

    [Tdate] ASC

    ON [PRIMARY]

    So, If i run the drop_existing script , will it still move the entire table include the data and index to the secondary filegroup ?

    CREATE UNIQUE CLUSTERED INDEX [fileidx1] ON [dbo].[FileList]

    ([Account] ASC,

    [PathId] ASC,

    [FileName] ASC,

    [FileNameLength] ASC,

    [Tdate] ASC)

    WITH (DROP_EXISTING = ON)

    ON [SecondaryFG]

    GO

  • Maramor (2/13/2012)


    I was curious what approach would be used to fix this particular situation.

    If you have the Enterprise edition, partition the large tables by "row date" and "align the indexes" (See Books Online for more but that a fancy phrase for "partition the indexes, as well").

    If you have the Standard edition, consider a "partitioned view".

    The advantage of both is that you can reindex the much smaller partitions with a lot less overhead and a whole lot less time. If you have truly done the paritioning by some date having to do with the age of the row since it was inserted, then you may not have to reindex any but the "last" partition because only the last partition will have inserts and, more likely than not, the older data won't suffer many updates.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • stanley wong-314201 (2/13/2012)


    So, If i run the drop_existing script , will it still move the entire table include the data and index to the secondary filegroup ?

    CREATE UNIQUE CLUSTERED INDEX [fileidx1] ON [dbo].[FileList]

    ([Account] ASC,

    [PathId] ASC,

    [FileName] ASC,

    [FileNameLength] ASC,

    [Tdate] ASC)

    WITH (DROP_EXISTING = ON)

    ON [SecondaryFG]

    GO

    It'll move the clustered index, and the clustered index is the data, so the table moves to the new filegroup. Any LOB data will stay behind, as will any nonclustered indexes (you'd have to move them separately)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I'm surprised Gail and Jeff haven't jumped all over this but that does not look like a good candidate for a clustered index, its certainly not narrow or ever-increasing.

    Changing the cluster could save some of that 10GB down to indexes (you only have a clustered index?) and reduce the occurrence of fragmentation in the first place.

    ---------------------------------------------------------------------

  • george sibbald (2/14/2012)


    I'm surprised Gail and Jeff haven't jumped all over this but that does not look like a good candidate for a clustered index, its certainly not narrow or ever-increasing.

    Agreed, maybe take a read through this: http://www.sqlservercentral.com/articles/Indexing/68563/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for the explanation .

    Does below variable data type consider BOL data type in sql 2005 ? Will this column data transfered to the new filegroup when running create unique cluster index with option drop existing?

    This is some of column exits in the table.

    [FileKey] [binary](16) NOT NULL,

    [Name] [binary](8) NOT NULL,

    [FileAttribute] [varbinary](4096) NOT NULL,

    [FileNameLength] AS (datalength([FileName

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply