Post reply

Blocking by select against sys.dm_db_index_physical_stats

  • January 10, 2020 at 7:51 pm

    #3714551

    We recently upgraded from 2014 to 2017 (Enterpise Edition, CU 15) and since the upgrade we've seen a large amount of blocking by a procedure that finds fragmented indexes.  We've had this same procedure in use for years for out nightly maintenance and have not seen problems.  It appeared at first to be blocking calls using insert bulk but after doing some further digging it appears that the spid running the select, at times, will hold onto many object locks at one time...at others only 1 object at a time.  I suspect we're seeing blocking issues now due to the extended length of time that these locks are held.  I can reproduce at times when I run the select on its own but it's not always consistent.  Wondering if anyone else has seen an unusual pattern of behavior when querying sys.dm_db_index_physical_stats?

    Here's my simple select...

    DECLARE @MaxFrag float = 5,

    @DatabaseName varchar(128) = DB_NAME(DB_ID())

    SELECT

    fi.index_id,

    fi.object_id,

    db_id(@DatabaseName),

    @DatabaseName,

    cast(fi.partition_number as varchar(10)),

    fi.avg_page_space_used_in_percent,

    fi.avg_fragmentation_in_percent,

    fi.page_count,

    ISNULL(fi.fragment_count,0)

    FROM sys.dm_db_index_physical_stats(db_id(@DatabaseName), NULL, NULL, NULL, 'SAMPLED') AS fi

    WHERE fi.avg_fragmentation_in_percent >= @MaxFrag

    AND page_count >= 750

    AND fi.index_id > 0

     

  • January 11, 2020 at 8:10 pm

    #3714692

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • January 12, 2020 at 8:28 pm

    #3714770

    It probably is the "Insert Bulk" things happening.  MS tried to make code faster faster with an "improvement" called "Fast Inserts", which came about in SQL Server 2016.  It allocates full extents at a time and gets its speed from not checking to see if any partially empty extents already exist.

    Guess what happens when "Insert Bulk" (or anything else the could be minimally logged occurs) inserts even a 1 byte row?  It allocates a full extent.

    Most people never realize this is happening because index maintenance covers it up.  Index maintenance also takes longer and is required more often especially if you check for average page density.

    See the following article on Trace Flags and look for Trace Flag 692 where all is explained.  I strongly recommend that you turn the trace flag on server wide and only turn it off in code that can actually make use of it the right way (for large batches of imports or other bulk logged inserts.  Anything that is counting extents, like sys.dm_db_index_physical_stats could be blocked by the gross allocation of extents.

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

  • January 13, 2020 at 6:34 pm

    #3715031

    Thank you for the info Jeff, I'll start doing some research on that trace flag and the changes around the Fast Inserts.

  • January 17, 2020 at 8:06 pm

    #3716412

    Ran a test after enabling TF 692 and continued to see blocking by the select against sys.dm_db_index_physical_stats, unfortunately.

  • January 28, 2023 at 4:22 pm

    #4142819

    Josh B wrote:

    Ran a test after enabling TF 692 and continued to see blocking by the select against sys.dm_db_index_physical_stats, unfortunately.

    Ooooo lordy.   My apologies for missing your reply to your now 3 year old post.

    Unfortunately, that doesn't change anything for you.  That was the only suggestion I had at the time.

    If you're still having such issues (time passing frequently makes thing worse rather than better), my only other suggestion would be to write a control loop that would execute the DMV on table or index at a time with, possibly, a control table for what time of day certain indexes can be looked at.

    Shifting gears a bit, I'll also state that, except for the "Index Inversion" that can occur on an index during a "Shrink File", logical fragmentation doesn't matter for things in memory and matter only little if the data files are on SSDs.  What does matter, is how much memory might be used by fragmented indexes due to the page splits that occurred.  After reading an article about a rather benign OS level dmv that I didn't previously know about, it looks like we might be able to erg out some good page density information about any given index without the blocking that sys.dm_db_index_physical_stats() 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)

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

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