How to get index usage information in SQL Server

By:   |   Comments (18)   |   Related: > Indexing


Problem

Databases have two primary storage needs; data pages and index pages.  Understanding and viewing the actual data in your tables is pretty straightforward by running some sample queries to get an idea of what columns are being used as well as what type of data is actually being stored.  On the flip side of this, it is often difficult to know exactly what indexes are being used and how they are being used.  So how can you get a better understanding of how your indexes are being used and what operations are occurring (inserts, updates, deletes, selects)?

Solution

In SQL Server, many new dynamic management objects have been created that allow you insight into a lot of data that was not accessible or just difficult to get in previous versions of SQL Server.  One new function and one new view that provide data about index usage are sys.dm_db_index_operational_stats and sys.dm_db_index_usage_stats.

sys.dm_db_index_operational_stats

This function gives you information about insert, update and delete operations that occur on a particular index.  In addition, this view also offers data about locking, latching and access methods.  There are several columns that are returned from this view, but these are some of the more interesting columns:

  • leaf_insert_count - total count of leaf level inserts
  • leaf_delete_count - total count of leaf level deletes
  • leaf_update_count  - total count of leaf level updates

Here is a sample query that provides some of the key columns that are helpful to determine insert, update and delete operations.

SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME], 
       I.[NAME] AS [INDEX NAME], 
       A.LEAF_INSERT_COUNT, 
       A.LEAF_UPDATE_COUNT, 
       A.LEAF_DELETE_COUNT 
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A 
       INNER JOIN SYS.INDEXES AS I 
         ON I.[OBJECT_ID] = A.[OBJECT_ID] 
            AND I.INDEX_ID = A.INDEX_ID 
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

Here is the output from the above query.  From this function we can get an idea of how many inserts, updates and delete operations were performed on each table and index.

From this view we can get an idea of how many inserts, updates and delete operations were performed on each table and index

sys.dm_db_index_usage_stats

This view gives you information about overall access methods to your indexes.  There are several columns that are returned from this DMV, but here are some helpful columns about index usage:

  • user_seeks - number of index seeks
  • user_scans- number of index scans
  • user_lookups - number of index lookups
  • user_updates - number of insert, update or delete operations
SELECT   OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
         I.[NAME] AS [INDEX NAME], 
         USER_SEEKS, 
         USER_SCANS, 
         USER_LOOKUPS, 
         USER_UPDATES 
FROM     SYS.DM_DB_INDEX_USAGE_STATS AS S 
         INNER JOIN SYS.INDEXES AS I 
           ON I.[OBJECT_ID] = S.[OBJECT_ID] 
              AND I.INDEX_ID = S.INDEX_ID 
WHERE    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1

Here is the output from the above query.  From this view we can get an idea of how many seeks, scans, lookups and overall updates (insert, update and delete) occurred.

From this view we can get an idea of how many seeks, scans, lookups and overall updates (insert, update and delete) occurred

Notes

The values for these counters get reset each time you restart SQL Server.  In addition, the values for the data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, December 17, 2020 - 5:33:54 AM - David Wiltcher Back To Top (87924)
Hi Greg. Apologies for late reply. Essentially just joining the usage stats DMV to sys.indexes to get index names. But then even without the link to other dmvs it takes forever to open in fact just running a random select top 10 took 2 and a half minutes to return.

Monday, November 23, 2020 - 3:50:08 PM - Greg Robidoux Back To Top (87827)
Hi Dave, not sure why it would be getting slower. Are you joining tables are just doing a straight select? What other DMVs have you noticed this becoming slower?

-Greg

Sunday, November 22, 2020 - 7:32:40 PM - David Wiltcher Back To Top (87823)
Hi

I've been using dm_db_index_usage_stats for years to get index usage stats for index maintenance etc. Recently querying this table has gone extremely slow. The database is an AX Dynamics 2012 database. Even running for 1 table can take over a minute.

I've also noticed similar issues with other stats dmvs.

Any ideas why this can happen and a possible way to resolve it without having to restart the server?

Cheers
Dave

Monday, October 7, 2019 - 8:48:39 AM - wilfred Back To Top (82685)

small correction: add following to the join on SYS.INDEXES
    AND s.database_id= DB_ID()

since SYS.DM_DB_INDEX_USAGE_STATS is server-based and SYS.INDEXES is database-based

If not used, you will get unpredictable results depending on objects in other databases.

Tuesday, October 4, 2016 - 1:45:59 PM - Greg Robidoux Back To Top (43495)

Hi Luis,

I don't think there is a way to get this type of data for SQL Server 2000.

-Greg

Tuesday, October 4, 2016 - 1:12:16 PM - Luis Back To Top (43494)

 Please,

I need to see the unused object BUT in SQL SERVER 2000. How i can do?

 

Thanks friend,

 

Friday, August 26, 2016 - 8:08:53 AM - Jonathan Back To Top (43193)

Thank you for these queries; they definitely helped get me going.

One thing I noticed is that the queries start with the "stats" functions, using them for the conditions in the WHERE clause, then do an INNER JOIN to the sys.indexes view.  Doing it this way means that unused indexes do not show in the results.  I rearranged the tables to start with sys.indexes and do a LEFT JOIN to the "stats" functions (yes, I could have done a RIGHT JOIN but I prefer the base table to come first) then swtiched the WHERE clause to look at the sys.indexes view.  This showed some indexes that were not used, so not visible in the original queries.

I also appreciated the well-structured code.  It works and it looks pretty; bonus!

Wednesday, March 25, 2015 - 3:49:06 AM - Jens Schmitt Back To Top (36678)

Use OBJECT_NAME(object_id, database_id). object_id is only unique within a database. Otherwise you will get wrong object names!!!

Tuesday, September 9, 2014 - 11:00:04 AM - Jose Fidalgo Back To Top (34453)

How do I know who consulted a table of a database , it is possible, how do I?

Monday, September 8, 2014 - 9:24:37 AM - Greg Robidoux Back To Top (34440)

Hi Kousalya,

Indexes help SQL Server find the data faster, so if you have the correct indexes on your tables your queries will run much faster.

By having extra indexes that are not used can also slow down SQL Server, because work has to be done to maintain the index but the index is never used to retrieve the data.

-Greg

Monday, September 8, 2014 - 6:12:15 AM - kousalya Back To Top (34435)

What is the usee of index..why we going for index

Tuesday, December 10, 2013 - 11:57:16 AM - Malek Muraywed Back To Top (27753)

I found this to be rather important considering I deal with databses containing multiple schemas - thanks everyone

SELECT [Database] = DB_Name(),
 [Schema] = SCHEMA_NAME(OBJ.UID),
 [OBJECT NAME] = OBJECT_NAME(S.[OBJECT_ID]),
 [INDEX NAME] = I.[NAME],
 USER_SEEKS,
 USER_SCANS,
 USER_LOOKUPS,
 USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS S         
 Cross Apply
  SYS.INDEXES I           
 Cross Apply
  sys.sysobjects  obj
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
And I.[type] != 1 -- exclude primary keys
And I.[OBJECT_ID] = S.[OBJECT_ID]              
AND I.INDEX_ID = S.INDEX_ID
And obj.id = I.object_id
Order by obj.name

Friday, May 25, 2012 - 8:21:26 AM - rohit Back To Top (17658)

I am trying to convert varchar to nvarchar in sql server.my table have more than 1000 tables .primery key also have varchar and many index on my table.please give me the procedure by which i can chanage all the varchar to nvarchar. i am using 

USE [EASETEST]

GO

/****** Object:  StoredProcedure [dbo].[VarCharToNvarChar]    Script Date: 05/25/2012 17:50:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

create   procedure databasechanger

as

begin

declare curChangeTypes cursor for select column_name,table_name,character_maximum_length from information_schema.columns where DATA_TYPE='varchar' and table_name in (select distinct table_Name from information_schema.columns where DATA_TYPE='varchar')

open curChangeTypes

declare @cn varchar(50),@tn varchar(50),@cml int

declare @str varchar(8000)

Fetch Next From curChangeTypes into @cn,@tn,@cml

while(@@fetch_status=0)

begin

if(@cml=-1)

set @str = 'alter table '+@tn+' alter  column '+@cn+' nvarchar(max)'

else

set @str = 'alter table '+@tn+' alter  column '+@cn+' nvarchar('+cast(@cml as varchar)+')'

exec(@str)

Fetch Next From curChangeTypes into @cn,@tn,@cml

end

close curChangeTypes

deallocate curChangeTypes

end

 

but it is not working....

 

Wednesday, April 4, 2012 - 12:36:59 PM - Greg Robidoux Back To Top (16775)

it_is_me - you are correct sys.dm_db_index_operational_stats is a function because it takes parameters. I updated the tip to reflect this.

Thanks for pointing this out. 

Wednesday, April 4, 2012 - 12:18:33 PM - it_is_me Back To Top (16774)

SYS.DM_DB_INDEX_OPERATIONAL_STATS is NOT a view, it IS a function.....

 

 

Monday, March 29, 2010 - 3:47:59 PM - xyvyx Back To Top (5141)

FYI, the SYS.DM_DB_INDEX_USAGE_STATS  view returns server-wide usage info & includes database_id in the result set.  Since it's possible for ObjectIDs to overlap, modify the usage stats query like below to include info for only the current database:

SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
S.[OBJECT_ID],
I.[NAME] AS [INDEX NAME],
d.name as DBName,
USER_SEEKS, USER_SCANS, USER_LOOKUPS, USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I
ON I.[OBJECT_ID] = S.[OBJECT_ID]
AND I.INDEX_ID = S.INDEX_ID
and s.database_id = DB_ID()
inner join sys.databases d on d.database_id = s.database_id
order by [OBJECT NAME],[INDEX NAME]

 

Friday, August 15, 2008 - 7:04:05 AM - aprato Back To Top (1643)

 Probably because the indexes are not in cache

 http://msdn.microsoft.com/en-us/library/ms174281.aspx

"The data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available. This data is neither persistent nor transactionally consistent. This means you cannot use these counters to determine whether an index has been used or not, or when the index was last used. For information about this, see sys.dm_db_index_usage_stats (Transact-SQL)."

 

Friday, August 15, 2008 - 6:37:21 AM - bouzahme Back To Top (1642)

Hi,

I want to ask why when running the following query SELECT * FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL); not all tables with indexes are displayed.

Thanks














get free sql tips
agree to terms