{"id":737,"date":"2010-03-09T10:18:00","date_gmt":"2010-03-09T10:18:00","guid":{"rendered":"\/blogs\/paul\/post\/Inside-sysdm_db_index_physical_stats.aspx"},"modified":"2019-06-12T10:51:02","modified_gmt":"2019-06-12T17:51:02","slug":"inside-sys-dm_db_index_physical_stats","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/inside-sys-dm_db_index_physical_stats\/","title":{"rendered":"Inside sys.dm_db_index_physical_stats"},"content":{"rendered":"

Way back in the mists of time, at the end of the last century, I wrote DBCC SHOWCONTIG<\/span> for SQL Server 2000, to complement my new invention DBCC INDEXDEFRAG<\/span>. <\/span><\/p>\n

I also used to wear shorts all the time, with luminous orange, yellow, or green socks. <\/span><\/p>\n

Many things change – I now have (some) dress sense, for one. One other thing that changed was that DMVs came onto the scene with SQL Server 2005. DBCC SHOWCONTIG<\/span> was replaced by sys.dm_db_index_physical_stats<\/span>. Under the covers though, they both use the same code – and the I\/O characteristics haven’t changed. <\/span><\/p>\n

This is a blog post I’ve been meaning to do for a while now, and I finally had the impetus to do it when I heard about today’s <\/span>T-SQL Tuesday on I\/O in general<\/span><\/a>\u00a0being run by Mike Walsh (Twitter<\/a>|blog<\/a>). It’s a neat idea so I decided to join in this time. In retrospect, reading this over before hitting ‘publish’, I got a bit carried away (spending two hours on this) – but it’s one of my babies, so I’m entitled to! :-) <\/span><\/p>\n

This isn’t a post about how to use DMVs in general, how to use this DMV in particular, or anything about index fragmentation. This is a blog post about how the DMV works. <\/span><\/p>\n

DMV is a catch-all phrase that most people (myself included) use to describe all the various utility views in SQL Server 2005 and 2008. DMV = Dynamic Management View. There’s a catch with the catch-all though – some of the DMVs aren’t views at all, they’re functions. A pure DMV gets info from SQL Server’s memory (or system tables) and displays it in some form.\u00a0A DMF, on the other hand, has to go and so some work before it can give you some results. The sys.dm_db_index_physical_stats<\/span> DMV (which I’m going to call ‘the DMV’ from now on) is by far the most expensive of these – but only in terms of I\/O. <\/span><\/p>\n

The idea of the DMV is to display physical attributes of indexes (and the special case of a heap)\u00a0– to do this it has to scan the pages comprising the index, calculating statistics as it goes. Many DMVs support what’s called predicate pushdown<\/em>, which means if you specify a WHERE<\/span> clause, the DMV takes that into account as it prepares the information. This DMV doesn’t. If you ask it for only the indexes in the database that have logical fragmentation > 30%, it will scan all the indexes, and then just tell you about those meeting your criteria. It has to do this because it has no way of knowing which ones meet your criteria until it analyzes them – so can’t support predicate pushdown.<\/span><\/p>\n

This is where understanding what it’s doing under the covers comes in – the meat of this post.<\/span><\/p>\n

LIMITED<\/span><\/strong> <\/span><\/span><\/p>\n

The default operating mode of the DMV is called LIMITED<\/span>. Kimberly always makes fun of the equivalent option for DBCC SHOWCONTIG<\/span>, which I named as a young and foolish developer – calling it\u00a0WITH FAST<\/span>. Hey – it’s descriptive! <\/span><\/p>\n

The LIMITED<\/span> mode can only return the logical fragmentation of the leaf level plus the page count. It doesn’t actually read the leaf level. It makes use of the fact that the next level up in the index contains a key-ordered list of page IDs of the pages at the leaf level – so it’s trivial to examine the key-ordered list and see if the page IDs are also in allocation order or not, thus calculating logical fragmentation. <\/span><\/p>\n

The idea behind this option is to allow you to find the fragmentation of an index by reading the minimum number of pages, i.e. in the smallest amount of time. This option can be magnitudes faster than using the DETAILED<\/span> mode scan, and it depends on how big the index’s fanout<\/em> is. Without getting too much into the guts of indexes, the fanout is based on the index key size, and determines the number of child-page pointers an index page can hold (e.g. the number of leaf-level pages that a page in the next level up has information about). <\/span><\/p>\n

Consider an index with a char(800)<\/span> 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\u00a0– so 812 bytes. So a page can only hold 8096\/812 = 9 such entries. The fanout is at most 9. <\/span><\/p>\n

Consider an index with a bigint<\/span> key. Each entry is 13 bytes, so a page can hold 8096\/13 = 622 entries. The fanout is at most\u00a0622, but will likely be smaller, depending on operations on the index causing fragmentation at the non-leaf levels. <\/span><\/p>\n

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

I’ve created a 100GB clustered index (on the same hardware as I’m using for the <\/span>benchmarking series<\/span><\/a>) with 13421760 leaf-level pages and a maximum fanout of 540. In reality, I populated the index using 16 concurrent threads, so there’s some fragmentation. The level above the leaf has 63012 pages, an effective fanout of 213. Still, the LIMITED<\/span> mode scan will read 213x less than a DETAILED<\/span> scan, but will it be 213x faster?<\/span><\/p>\n

Here’s a perfmon capture of the LIMITED<\/span> mode scan on my index: <\/span><\/p>\n

\u00a0\"\" <\/span><\/p>\n

There’s nothing special going on under the covers in a LIMITED<\/span> mode scan – the chain of pages at the level above the leaf is read in page-linkage order, with no readahead. The perfmon capture shows: <\/span><\/p>\n