Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

How to get index usage information in SQL Server

MSSQLTips author Greg Robidoux By:   |   Read Comments (10)   |   Related Tips: More > 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


Last Update: 5/8/2007


About the author
MSSQLTips author Greg Robidoux
Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, September 09, 2014 - 11:00:04 AM - Jose Fidalgo Read The Tip

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


Monday, September 08, 2014 - 9:24:37 AM - Greg Robidoux Read The Tip

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 08, 2014 - 6:12:15 AM - kousalya Read The Tip

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


Tuesday, December 10, 2013 - 11:57:16 AM - Malek Muraywed Read The Tip

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 Read The Tip

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 04, 2012 - 12:36:59 PM - Greg Robidoux Read The Tip

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 04, 2012 - 12:18:33 PM - it_is_me Read The Tip

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

 

 


Monday, March 29, 2010 - 3:47:59 PM - xyvyx Read The Tip

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 Read The Tip

 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 Read The Tip

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




 
Sponsor Information