Post reply

Index and Page Split

  • July 30, 2014 at 9:22 pm

    #297211

    Hi all,

    I have some indexes which get fragmented very quickly ...

    I set the fill factor starting from 70 and keep monitoring the result . Yesterday I set fill factor 30 but it still get fragmented easily even though it is a bit better compared with fill factor 70 ...

    What should I do ?

    Should I lower it down again , for example fill factor 20 ??

    Moreover , I noticed the PAGE SPLIT/Sec is still high ..around 90 . How do i know which table that cause that page split ?

    Many thanks....

  • July 31, 2014 at 9:44 am

    #1734133

    What is data type, length, and number of columns?

    How intensively it is used - from sys.dm_db_index_usage_stats ?

  • July 31, 2014 at 6:25 pm

    #1734318

    SQL Guy 1 (7/31/2014)

    What is data type, length, and number of columns?

    How intensively it is used - from sys.dm_db_index_usage_stats ?

    Thanks for your kind response . Appreciate it!

    Here is the data for 2 different databases ( the indexes get fragmented in only a few hours after rebuild ):

    on Database DB1

    Index IX_P (Non clustered index)

    Index key column :

    P1(datetime) , size : 8 , Identity : No

    included column :

    ReaderId (Int)

    fragmentation : 92.31%

    pages : 806

    fill factor 65%

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 702 0 2826040

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

    on Database DB2

    Index IX_A

    Profile :

    Index Key columns :

    A1 (int)

    A2 (TinyInt)

    A3 (Bigint)

    Fragmentation :81.44 %

    Pagecount : 62593

    Fill Factor : 70 %

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    49476 0 0 0

    0 0 65176 4753949

    8247327 0 2551491 2873118

    0 1 0 2

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

    Index B (nonclustered)

    Profile :

    Index Key columns:

    B1 (Bigint)

    Included column :

    X1 (tinyint)

    X2 (varbinary(max)

    X3 (bit)

    X4 (int)

    X5 (tinyint)

    X6 (datetime)

    X7 (bigint)

    X8 (uniqueidentifier)

    Fragmentation : 97.24%

    Pagecount : 63379

    Fill Factor : 70%

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 0 0 2873118

    2 weeks ago :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 4 0 15808493

    7580190 0 11550284

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

    Index IX_C (nonclustered)

    Profile :

    Index key columns :

    C1(bigint)

    Fragmentation : 79.43%

    Pagecount : 2163

    Fillfactor : 70%

    Usage stat:

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    225313662780 563608

    1 0 0 0

    2845411199900 10

    What do you think about it ?

    Many thanks 🙂

  • July 31, 2014 at 8:50 pm

    #1734323

    murnilim9 (7/31/2014)

    SQL Guy 1 (7/31/2014)

    What is data type, length, and number of columns?

    How intensively it is used - from sys.dm_db_index_usage_stats ?

    Thanks for your kind response . Appreciate it!

    Here is the data for 2 different databases ( the indexes get fragmented in only a few hours after rebuild ):

    on Database DB1

    Index IX_P (Non clustered index)

    Index key column :

    P1(datetime) , size : 8 , Identity : No

    included column :

    ReaderId (Int)

    fragmentation : 92.31%

    pages : 806

    fill factor 65%

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 702 0 2826040

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

    on Database DB2

    Index IX_A

    Profile :

    Index Key columns :

    A1 (int)

    A2 (TinyInt)

    A3 (Bigint)

    Fragmentation :81.44 %

    Pagecount : 62593

    Fill Factor : 70 %

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    49476 0 0 0

    0 0 65176 4753949

    8247327 0 2551491 2873118

    0 1 0 2

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

    Index B (nonclustered)

    Profile :

    Index Key columns:

    B1 (Bigint)

    Included column :

    X1 (tinyint)

    X2 (varbinary(max)

    X3 (bit)

    X4 (int)

    X5 (tinyint)

    X6 (datetime)

    X7 (bigint)

    X8 (uniqueidentifier)

    Fragmentation : 97.24%

    Pagecount : 63379

    Fill Factor : 70%

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 0 0 2873118

    2 weeks ago :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 4 0 15808493

    7580190 0 11550284

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

    Index IX_C (nonclustered)

    Profile :

    Index key columns :

    C1(bigint)

    Fragmentation : 79.43%

    Pagecount : 2163

    Fillfactor : 70%

    Usage stat:

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    225313662780 563608

    1 0 0 0

    2845411199900 10

    What do you think about it ?

    Many thanks 🙂

    Are any of these causing performance issues?

    Also, except for unique indexes (which may be part of constraints), I'd be tempted to simply drop any index that had a large number of User_Updates and very few or zero index seeks. User_Scans (a bit faster than heap or CI scans) can be useful indexes but, with 2,826,040, 0 seeks, and only 702 scans, Index IX_P (Non clustered index) looks pretty useless to me and probably drop that one as well, unless it's a unique index.

    --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)

  • July 31, 2014 at 10:04 pm

    #1734326

    Jeff Moden (7/31/2014)

    murnilim9 (7/31/2014)

    SQL Guy 1 (7/31/2014)

    What is data type, length, and number of columns?

    How intensively it is used - from sys.dm_db_index_usage_stats ?

    Thanks for your kind response . Appreciate it!

    Here is the data for 2 different databases ( the indexes get fragmented in only a few hours after rebuild ):

    on Database DB1

    Index IX_P (Non clustered index)

    Index key column :

    P1(datetime) , size : 8 , Identity : No

    included column :

    ReaderId (Int)

    fragmentation : 92.31%

    pages : 806

    fill factor 65%

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 702 0 2826040

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

    on Database DB2

    Index IX_A

    Profile :

    Index Key columns :

    A1 (int)

    A2 (TinyInt)

    A3 (Bigint)

    Fragmentation :81.44 %

    Pagecount : 62593

    Fill Factor : 70 %

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    49476 0 0 0

    0 0 65176 4753949

    8247327 0 2551491 2873118

    0 1 0 2

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

    Index B (nonclustered)

    Profile :

    Index Key columns:

    B1 (Bigint)

    Included column :

    X1 (tinyint)

    X2 (varbinary(max)

    X3 (bit)

    X4 (int)

    X5 (tinyint)

    X6 (datetime)

    X7 (bigint)

    X8 (uniqueidentifier)

    Fragmentation : 97.24%

    Pagecount : 63379

    Fill Factor : 70%

    Usage stat :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 0 0 2873118

    2 weeks ago :

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    0 4 0 15808493

    7580190 0 11550284

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

    Index IX_C (nonclustered)

    Profile :

    Index key columns :

    C1(bigint)

    Fragmentation : 79.43%

    Pagecount : 2163

    Fillfactor : 70%

    Usage stat:

    USER_SEEKSUSER_SCANSUSER_LOOKUPSUSER_UPDATES

    225313662780 563608

    1 0 0 0

    2845411199900 10

    What do you think about it ?

    Many thanks 🙂

    Are any of these causing performance issues?

    Also, except for unique indexes (which may be part of constraints), I'd be tempted to simply drop any index that had a large number of User_Updates and very few or zero index seeks. User_Scans (a bit faster than heap or CI scans) can be useful indexes but, with 2,826,040, 0 seeks, and only 702 scans, Index IX_P (Non clustered index) looks pretty useless to me and probably drop that one as well, unless it's a unique index.

    ohh thanks

    All of them cause trouble

    I have removed that index hohohohoh

    but what i need to do with the rest ?

    But I

  • August 1, 2014 at 10:22 am

    #1734498

    You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.

    Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.

    --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)

  • August 3, 2014 at 5:54 am

    #1734664

    Jeff Moden (8/1/2014)

    You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.

    Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.

    Thanks for your response ..It is a good idea .. I could disable 1 index that has zero index seek but I highly hesitate to disable the others that has high number of index seek ...

    I still don't know what to do with the indexes that have high number of index seek....

  • August 3, 2014 at 5:56 am

    #1734665

    murnilim9 (7/30/2014)

    Moreover , I noticed the PAGE SPLIT/Sec is still high ..around 90 . How do i know which table that cause that page split ?

    That counter is misleading. It is not the number of mid-index page splits (which are the ones which cause fragmentation)

    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

  • August 3, 2014 at 6:04 am

    #1734666

    GilaMonster (8/3/2014)

    murnilim9 (7/30/2014)

    Moreover , I noticed the PAGE SPLIT/Sec is still high ..around 90 . How do i know which table that cause that page split ?

    That counter is misleading. It is not the number of mid-index page splits (which are the ones which cause fragmentation)

    Hi..

    Thx for your response ...I wonder what do you mean by mid-index page splits ?

  • August 3, 2014 at 9:48 am

    #1734688

    WhiteLotus (8/3/2014)

    Jeff Moden (8/1/2014)

    You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.

    Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.

    Thanks for your response ..It is a good idea .. I could disable 1 index that has zero index seek but I highly hesitate to disable the others that has high number of index seek ...

    I still don't know what to do with the indexes that have high number of index seek....

    Apologies... I was relying on the flow of this thread to avoid having to include specific context...

    Of course you wouldn't disable indexes with a large number of index seeks. You would only do such a thing on indexes that had little or no seeks compared to updates. Scans on indexes would definitely be a judgement call after some further investigation to see if the index could/should (not all should necessarily be) be modified to use seeks.

    You would also avoid disabling UNIQUE indexes whether they have seeks or scans against them or not.

    --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)

  • August 3, 2014 at 10:06 am

    #1734693

    WhiteLotus (8/3/2014)

    GilaMonster (8/3/2014)

    murnilim9 (7/30/2014)

    Moreover , I noticed the PAGE SPLIT/Sec is still high ..around 90 . How do i know which table that cause that page split ?

    That counter is misleading. It is not the number of mid-index page splits (which are the ones which cause fragmentation)

    Hi..

    Thx for your response ...I wonder what do you mean by mid-index page splits ?

    Page splits which occur in the middle (anywhere other than the ends) of an index, resulting in fragmentation.

    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

  • August 3, 2014 at 7:21 pm

    #1734715

    Jeff Moden (8/3/2014)

    WhiteLotus (8/3/2014)

    Jeff Moden (8/1/2014)

    You could disable them which drops all of the data from the index but keeps the meta data and see if peformance suffers. Of course, you need to do a test on whatever is using the indexes before you do that. If performance does suffer, rebuild the index and it will reform, data and all.

    Of course, there are the normal warnings about doing this on high usage or large tables during business hours and that such action could cause some substantial logfile growth like any index reorg (no matter the recovery model) or index rebuild (in the FULL recovery model) can cause.

    Thanks for your response ..It is a good idea .. I could disable 1 index that has zero index seek but I highly hesitate to disable the others that has high number of index seek ...

    I still don't know what to do with the indexes that have high number of index seek....

    Apologies... I was relying on the flow of this thread to avoid having to include specific context...

    Of course you wouldn't disable indexes with a large number of index seeks. You would only do such a thing on indexes that had little or no seeks compared to updates. Scans on indexes would definitely be a judgement call after some further investigation to see if the index could/should (not all should necessarily be) be modified to use seeks.

    You would also avoid disabling UNIQUE indexes whether they have seeks or scans against them or not.

    I have dropped the index but I still have the script to create the index .... and i will keep monitoring the performance of the query ....

Viewing 12 posts - 1 through 11 (of 11 total)

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