index frag

  • I see one index which even after rebuilding with online=on every day fragmentation is 85% .

    Why and what are the fixes to defrag the index?

  • How many pages are in the index? How many writes vs. reads does the index encounter?

    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

  • Is the index on a UNIQUEIDENTIFIER column?

  • Hi,
        as Jason's suggesting it's probably because it's a small table/index.  Initially SQL Server allocates storage in what are called mixed extents - i.e. the extent is shared with other tables.  These won't defragment, but then again fragmentation in a dataset that small isn't going to have a noticable performance impact anyway

    1. “In general,fragmentation on small indexes is often not controllable. The pages of smallindexes are stored on mixed extents. Mixed extents are shared by up to eightobjects, so the fragmentation in a small index might not be reduced afterreorganizing or rebuilding the index. For more information about mixed extents,see Understanding Pages and Extents.”

      https://social.technet.microsoft.com/Forums/Lync/en-US/b02ed2c9-38a7-4762-9bf8-1cf79638c8f2/fragmentation-does-not-reduce-for-some-tables?forum=sqltools

    if you run this query, it also has a column for the number of pages, which will  help you identify if this is the case 

     

    SELECT

           dbschemas.[name] as 'Schema',

           dbtables.[name] as 'Table',

           dbindexes.[name] as 'Index',

           indexstats.avg_fragmentation_in_percent,

           indexstats.page_count,

           indexstats.record_count

    FROM

           sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats

                 INNER JOIN

                 sys.tables dbtables

                        on dbtables.[object_id]= indexstats.[object_id]

                 INNER JOIN

                 sys.schemas dbschemas

                        on dbtables.[schema_id]= dbschemas.[schema_id]

                 INNER JOIN

                 sys.indexes AS dbindexes

                        ON     dbindexes.[object_id] =indexstats.[object_id]

                              AND

                              indexstats.index_id =dbindexes.index_id

    WHERE

           indexstats.database_id = DB_ID()

    ORDER BY

           indexstats.avg_fragmentation_in_percent DESC;

    I'm a DBA.
    I'm not paid to solve problems. I'm paid to prevent them.

Viewing 4 posts - 1 through 4 (of 4 total)

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