Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

How to get index usage information in SQL Server

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



Comments and Feedback:
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


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)."

 


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]

 


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.....

 

 


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. 


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....

 


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



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.