sys.dm_db_index_physical_stats – Day 1 – One DMV a day

Hello Geeks and welcome to the first day of the long series to come in One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

Most of the seasoned DBAs and Developers would already know about the DMVs (Dynamic Management Views – if you have not already realized or known Β ) and their usage during troubleshooting or monitoring SQL Server. DMVs are system views provided in SQL Server introduced in 2005 πŸ™‚ )which exposes a lot of monitoring and internal data for the simplification of usage of SQL Server. These DMVs makes the lives of Developers and DBAs much easier.

In the first blog in this series I am going to start with the most popular and widely used DMV by both Developers and DBAs during troubleshooting and understanding Indexes. Yes, if you have already guessed it right its sys.dm_db_index_physical_stats. Rather than a DMV it is a Dynamic Management Function (DMF). But as the usage goes both DMVs and DMFs are commonly referenced as DMVs.

So what does this DMV return? Simple, It gives the physical statistics of the index. Now before you start getting bored with the story. Let me go to the main essence of this DMV. This returns the most important information about the indexes like the index level, page count, fragmentation details, avg page space used at each level of index, record count, record size information, compressed page count, etc. Yes, a lot of information to help visualize the index structure and use the data for troubleshooting.

Let’s see how to use it. This DMV accepts five parameters and here is the syntax.

Select * from sys.dm_db_index_physical_stats (<database id>, <object id>, <index id>, <partition number>, <mode>)

All the first four parameters are self-explanatory but the last parameter which is mode of scan is what determines the accuracy of the results and the load it puts on the server to get the data. There are three mode.

LIMITED – Fastest scan which scans only the non-leaf level pages of the index.

SAMPLED – 1 % of the total pages is sampled to get the stats. Upto 10000 pages this scans all pages.

DETAILED – This scans al the pages in the index and is considered most heavy scan.

 

It is important to choose the right mode to not add problems while troubleshooting problems. Here is the sample output from the DMV and the results help in deciding de-fragmenting the index or table.

SELECT  DB_NAME(database_id) AS DBName,
		OBJECT_NAME(i.id) AS TableName,
		i.name AS IndexName,
		avg_fragmentation_in_percent,
		fragment_count,
		page_count,
		record_count
FROM
sys.dm_db_index_physical_stats
	(DB_ID(), OBJECT_ID('publogger_tbl'), NULL, NULL, 'DETAILED') ips
INNER JOIN sysindexes i
ON ips.index_id = i.indid and i.id = OBJECT_ID('publogger_tbl')

IndexPhysicalStats

 

So now you know how to use and when to use this DMV and what information does it give to help you analyze and troubleshoot performance issues with fragmentation. Tomorrow I will talk about another DMV. Till then

 

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

Avatar

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

4 Comments on “sys.dm_db_index_physical_stats – Day 1 – One DMV a day”

  1. Nice informative, adding bit of idea on how to analyze the result set to determine correct action,would have been added more weightage to this post.

    1. Thanks for the suggestion Alok. I have started this series to talk about the DMVs and what information they exposes. Later in this series I will move from talking about specific individual DMVs and cover scenarios where you can use this information to deal with the scenarios. Appreciate your insight and interest. πŸ™‚

      Regards,
      Manu

  2. Hi Manu Sir ,

    I went through the above DMF , its really good one that we can understand easily . Really Thanks for sharing such a good things to us I am going to read all the DMV and DMF explained by you and hope after that No need to google any more πŸ™‚

    Thanks
    Firoj

Leave a Reply

Your email address will not be published. Required fields are marked *