Post reply

Avg_fragmentation\Index rebuilds in SQL Server

  • June 7, 2016 at 9:26 am

    #314365

    I am running query below to find out about fragmentation in percent.

    SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,

    avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count

    FROM sys.dm_db_index_physical_stats

    (DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL , 'SAMPLED')

    ORDER BY avg_fragmentation_in_percent DESC

    After this I run query from Brent Ozar to rebuild\reorg indexes and update stat:

    EXECUTE dbo.IndexOptimize

    @databases = 'USER_DATABASES',

    @FragmentationLow = NULL,

    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',

    @FragmentationLevel1 = 5,

    @FragmentationLevel2 = 30,

    @UpdateStatistics = 'ALL',

    @OnlyModifiedStatistics = 'Y'

    After running the second script, I run the first script, but still the frag percentage remains the same.

    What I am missing here?

  • June 7, 2016 at 9:37 am

    #1883661

    tiny tables.

    small tables will not benefit from reindexing. only your large tables.

    offhand, i think it's 1000 rows or less don't get any fragmentation benefit/results from reindexing, but we could look it up.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • June 7, 2016 at 10:59 am

    #1883690

    Also, this script is not from Brent Ozar, it's from Ola Hallengren.

    https://ola.hallengren.com/

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • June 7, 2016 at 11:07 am

    #1883692

    Thanks for the correction

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

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