how to List out all tables indexes having index fragmentation more than 50% of databases.

  • HI to every one

    can any one guide me to find out this

    if any incident occurred in sql server and i was asked to take a report of the last one hour [or] halfanhour what should i do? and

    how to List out all tables indexes having index fragmentation more than 50% of a database.

    for this particular period for all tables in a entire database

    --

    Thanks
    Naga.Rohitkumar

  • What do you mean by 'more than 50% of the database'?

    If you want indexes that have a logical fragmentation higher than 50%, just query sys.dm_db_index_physical_stats and filter on the logical_fragmentation_in_percent column.

    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
  • HI thanks for ur reply

    i mean "List out all tables indexes having index fragmentation more than 50% of a database."

    Thanks
    Naga.Rohitkumar

  • Repeating the exact same sentence doesn't explain anything.

    Do you want indexes that have more than 50% logical fragmentation? If so, see my previous reply. If not, then please explain what 'more than 50% of the database' means.

    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
  • Yes sir

    want indexes that have more than 50% logical fragmentation? from all indexes of tables in a database

    Thanks
    Naga.Rohitkumar

  • Answered that one in the first reply.

    GilaMonster (4/11/2013)


    If you want indexes that have a logical fragmentation higher than 50%, just query sys.dm_db_index_physical_stats and filter on the logical_fragmentation_in_percent column.

    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
  • thank u sir

    Thanks
    Naga.Rohitkumar

  • Use this sql.

    SELECT

    B.name AS TableName

    , C.name AS IndexName

    , C.fill_factor AS IndexFillFactor

    , D.rows AS RowsCount

    , A.avg_fragmentation_in_percent

    , A.page_count

    FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A

    INNER JOIN sys.objects B

    ON A.object_id = B.object_id

    INNER JOIN sys.indexes C

    ON B.object_id = C.object_id AND A.index_id = C.index_id

    INNER JOIN sys.partitions D

    ON B.object_id = D.object_id AND A.index_id = D.index_id

    WHERE C.index_id > 0

    and A.avg_fragmentation_in_percent >50

  • thanks and i got the same from google and issues is solved if any thing regarding required i will revert back

    Thanks
    Naga.Rohitkumar

  • @SSChasing Mays

    What an awesomely useful query! Thanks for sharing!

  • Make sure when you run that query you change the database your in to match the one your querying information about. Otherwise you will get information that might not be correct when the object_id function runs, as object_id is unique for that database.

    Joie Andrew
    "Since 1982"

  • I could not get this to work if the DB is running in SQL 2000 compatibility mode. I think for that the script needs to be changed a bit. Otherwise works well.

  • Try this.

    SELECT

    B.name AS TableName

    , C.name AS IndexName

    , C.fill_factor AS IndexFillFactor

    , D.rows AS RowsCount

    , A.avg_fragmentation_in_percent

    , A.page_count

    FROM sys.dm_db_index_physical_stats(null,NULL,NULL,NULL,NULL) A

    INNER JOIN sys.objects B

    ON A.object_id = B.object_id

    INNER JOIN sys.indexes C

    ON B.object_id = C.object_id AND A.index_id = C.index_id

    INNER JOIN sys.partitions D

    ON B.object_id = D.object_id AND A.index_id = D.index_id

    WHERE C.index_id > 0

  • David Knapp (12/17/2013)


    I could not get this to work if the DB is running in SQL 2000 compatibility mode. I think for that the script needs to be changed a bit. Otherwise works well.

    Change FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) A to use a fixed value for the first parameter rather than the function. The function in parameters is disallowed under compat mode 80.

    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
  • Neeraj Dwivedi (12/17/2013)


    Try this.

    SELECT

    B.name AS TableName

    , C.name AS IndexName

    , C.fill_factor AS IndexFillFactor

    , D.rows AS RowsCount

    , A.avg_fragmentation_in_percent

    , A.page_count

    FROM sys.dm_db_index_physical_stats(null,NULL,NULL,NULL,NULL) A

    INNER JOIN sys.objects B

    ON A.object_id = B.object_id

    INNER JOIN sys.indexes C

    ON B.object_id = C.object_id AND A.index_id = C.index_id

    INNER JOIN sys.partitions D

    ON B.object_id = D.object_id AND A.index_id = D.index_id

    WHERE C.index_id > 0

    That won't work well. With null as the first parameter all databases' index physical stats are pulled, but the sys.objects and sys.indexes are local to the database this is run in, all the other indexes will be filtered out by the inner join or, if the objectID happens to match an object in the current database, return incorrect object names.

    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

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

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