sys.dm_db_index_usage_stats – Day 2 – One DMV a Day

Hello Geeks and welcome to the Day 2 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.

Today I will be talking about sys.dm_db_index_usage_stats. This DMV is a very helpful for the DBA who has to monitor the usage of existing indexes and a developer to identify how well is the index he created being used. This also in a way helps in understanding the load on a particular table/index.

Sys.dm_db_index_usage_stats is a dynamic management view and has the data cumulative since the instance restart for all the indexes which are used at least once. First time the index is used a record is added to the view and all the counters/values are set to zero. When an index is used for seek, scan, lookup or is updated the respective column is incremented. It also has different columns for user usage and system usage. Then it keeps track of last event for type of usage.

Sys.dm_db_index_usage_stats will help you pull the information for both user run queries and system usage like collecting data for statistics.

1. When was my index last used and how efficient is my index?

• Check for the last_user_seek/scan/lookup and counts of user_seeks vs scans vs lookup. In most cases Seeks are better than Scans except few exceptions like table having varchar data.

2. What is the maintenance overhead due to my index?

• If the index usage is low and still you see more number of user_updates and high system counters then you have to think about modifying or dropping the index as it is adding more overhead than its usage.

You can directly run the query of sys.dm_db_index_usage_stats with necessary filters on database_id, object_id and index_id to narrow down your results.

SELECT * FROM sys.dm_db_index_usage_stats WHERE database_id = <databaseId> and object_id = <objectId> and index_id = <indexId>

Lets run the below script which will record 100 seeks and you can see them from the dumped data of sys.dm_db_index_usage_stats. The database_id, object_id and index_id should change in your script.

SET NOCOUNT ON
GO
--create dump table for select
IF EXISTS(SELECT 1 from tempdb.dbo.sysobjects where name like '#dump%')
	DROP TABLE #dump
SELECT * INTO #dump FROM publogger_tbl WHERE 1 = 0

--take a dump of sys.dm_db_index_usage_stats
IF EXISTS(SELECT 1 from tempdb.dbo.sysobjects where name like '#x1%')
	DROP TABLE #x1
SELECT * 
INTO #x1
FROM sys.dm_db_index_usage_stats
WHERE database_id = 6 and object_id = 245575913 and index_id = 1

--run a index seek operation 100 times
DECLARE @i INT = 0

WHILE @i < 100
BEGIN

	INSERT INTO #dump SELECT * from publogger_tbl where eId = 10
	SET @i = @i + 1
END

--Check the differential change in user_seeks count
SELECT	curr.user_seeks - old.user_seeks AS Total_Seeks, 
		old.last_user_seek AS last_Seek, 
		curr.last_user_seek AS curr_seek 
FROM sys.dm_db_index_usage_stats curr
INNER JOIN #x1 old 
ON	curr.database_id = old.database_id AND 
	curr.object_id = old.object_id AND 
	curr.index_id = old.index_id

SET NOCOUNT OFF
GO

Here is a very good tip for all DBAs. Use sys.dm_db_index_usage_stats to dump data into a table and when a Manager/ Business users or Senior DBA asks you for the usage stats you will have the trend by pulling the differential stats of each index for each day. As a good DBA you need data. 🙂

That’s all about sys.dm_db_index_usage_stats. 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