Anonymous
  • 0

SQL Server: How does SQL server DMV work?

  • 0

In particular sys.dm_db_index_physical_stats for fragmentation collection.

Share

1 Answer

  1. This answer was edited.

    DMV = Dynamic Management View. Contrary to its name, some of the DMVs aren’t views at all. There are functions too. Non-function DMV gets info from SQL Server’s memory and/or system tables. A DMF must do some work before it can give you results. For example - sys.dm_db_index_physical_stats are a mosRead more

    DMV = Dynamic Management View.

    Contrary to its name, some of the DMVs aren’t views at all. There are functions too.

    Non-function DMV gets info from SQL Server’s memory and/or system tables. A DMF must do some work before it can give you results. For example – sys.dm_db_index_physical_stats are a most expensive DMV in terms of I/O.  It to displays physical attributes of indexes and heaps.  Since physical attribute of an index is not in the memory (and not in system table because it is not schema kind meta data) it must come from disk. This DMV is not also a predicate pushdown type supportive, which means no allowance of WHERE clause.   So, to find a result of condition such as fragmentation >25% it must scan entire database for all indexes.  There is no other way to tell current information from database.

    So, there are few options to reduce this heavy workload with a reasonable compromise.

    1. LIMITED mode: Can only return the logical fragmentation of the leaf level plus the page count. It doesn’t read the leaf level. It reads minimum number of pages with less time.

    It makes use of the fact that the one level up in the index contains a key-ordered list of page IDs of the pages at the leaf level. Examines the key-ordered list and see if the page IDs are also in allocation order or not, thus calculating logical fragmentation (OF PAGES). Since it checks for the page IDs allocation order only, it omits the fragmentation inside the page. So, this finding is a quick and dirty check before you decide to confirm it with SAMPLED or DETAILED checks.  Index key size determines the number of child-page pointers an index page can hold.

    Consider an index with a char (800) key. Each entry in a page in the level above the leaf has to include a key value (the lowest key that can possibly appear on the page being referred to), plus a page ID, plus record overhead, plus slot array entry – so 812 bytes. So, a page can only hold 8096/812 = 9 such entries. The fanout is at most 9.

    For a table with 1 million pages at the leaf level, the first index will have 1 million/9 = 111112 pages at least at the level above the leaf. The second index will have at least 1608 pages. The savings in I/O from using the LIMITED mode scan will clearly differ based on the fanout.

    2. DETAILEDmode: Calculates fragmentation by LIMITED mode PLUS by reading all pages (leaf, above the limited pages).  It is slowest among all. It also uses readahead mechanism which is aggressive and will choke IO with high disk queue length.

    3. SAMPLED mode: Calculates fragmentation like DETAILED mode but will read 1% if leaf pages are >= 10000. If not, it will read all pages.

    Verifying progress:

    Use ‘reads’ column in ‘sys.dm_exec_sessions’ to find how far it has progressed.  LIMITED mode doesn’t use ReadAhead mechanism and so the duration is not proportional to the number of leaf pages used in DETAILED mode. A test may show around 0.8 to 0.1 times duration DETAILED.

    Consider these Strategies depending on situation:

    • Run it on a restored backup of the database.
    • only run on interested indexes. Don’t run on all indexes.
    • Try LIMITED (logical fragmentation of pages) first and then SAMPLED or DETAILED only if required.
    See less
    • 2

You must login to add an answer.