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

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