Post reply

index issue

  • September 13, 2010 at 10:24 pm

    #240273

    Hi All,

    A primary table consists of 1,49,562 rows. i have analysed that table. there is no index at all.

    afterwards, i had checked that fragmentations. its says Actual count:BestCount 12.6% and

    logical fragmentation 42%.

    Afterwards, i decide to create an index for that table. i created a clustered index.

    run the update statistics command for that table. Then i checked the fragmentation of that

    table. It shows Actual Count:BestCount 20%. Logical fragmentation 80%. (1:5)

    My question is. how can i fix this issue(like BestCount:ActualCount) 100%. logical fragmentation 0%

    Anybody give me a suggestion. it would be very great helpful

    Thanks & Regards

    Balaji.G

  • September 13, 2010 at 10:45 pm

    #1220439

    Can you provide the DDL for the table?

    Or even at least the data type(s) of the column(s) in the Clustered Index?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 13, 2010 at 11:35 pm

    #1220451

    Hi,

    create clustered index c_app_xyz on xyz(app) with fillfactor=80

    in xyz table, app is int, and also identity field. after creating a clustered index

    dbcc showcontig(xyz)

    it shows scan density (Bestcount:ActualCount) 84.5

    an other table is abc column name same app field type is varchar(50). no identity field

    create clustered index c_app_abc on abc(app) with fillfactor=80

    dbcc showcontig(abc)

    it shows scandensity(Bestcount:ActualCount) 20.00% (1:5)

    Logical Fragmentation 80.00%

    let me know the reason. and how to fix it..

    Thanks

    Balaji.G

  • September 14, 2010 at 12:09 am

    #1220458

    What is the uniqueness of your data in that second CI? Have you examined using a numeric type for your CI?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • September 15, 2010 at 8:01 am

    #1221334

    1) how much free space is in your database? If you are like almost every one of my clients then you are using autogrowth to manage your file size and you have essentially no empty space in the database for the index to be laid down sequentially --> fragmentation will exist even for new index.

    2) pretty sure that fillfactor of 80 will show up in showcontig, but coffee hasn't kicked in yet... 🙂

    3) switch to using the DMV that replaces showcontig: sys.dm_db_index_physical_stats

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • September 15, 2010 at 8:05 am

    #1221338

    Just out of curiosity..have you run a dbcc dbreindex() or alter index rebuild? Also make sure you update statistics with fullscan after creating/dropping/modifying indexes.

Viewing 6 posts - 1 through 5 (of 5 total)

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