sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats in SQL Server 2005

sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats in SQL Server 2005

Today I am happy to introduced one more article written by Mr. Mark Will regarding two very useful data management views  (DMV) in SQL Server 2005. I am sure every reader of my blog will be happy to read it as the information provided in the article could become very useful asset for everybody who are using SQL Server 2005.

Introducing SQL 2005 Two Data Management Views

by Mark Wills

In SQL 2005, new Data Management Views were introduced known as DMV's.

I have recently been involved in some discussions as to what these views really do, and thought I might share some relatively light hearted discussion.

At first glance they give information which doesn't reconcile. And that is why they are different, it is more the differences that become important rather than they don't match.

They actually tell us different pieces of the index puzzle, and collectively, are very telling about your index designs (which will be a different post).

The views are :

sys.dm_db_index_usage_stats  and  sys.dm_db_index_operational_stats

sys.dm_db_index_usage_stats only reports on indexes that have been used at least once since the server was last restarted and updated each time the plan is executed.

sys.dm_db_index_operational_stats reports on all indexes regardless of whether they have been used and records how many times the storage engine executes a specific operation on the index.

The way I remember how to use them is by name (duh), as in "is my index useful" then usage, "is my index operating efficiently" then operational.

Let's create a couple of test tables for this purpose :

-- first a 'heap' table ie one with no PK or clustered index

IF object_id('tst_tbl_heap','U') is not null drop table tst_tbl_heap

CREATE TABLE tst_tbl_heap (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

go

-- now a 'clustered' table

IF object_id('tst_tbl_indexes','U') is not null drop table tst_tbl_indexes

CREATE TABLE tst_tbl_indexes (ID int not null, Firstname varchar(60), SecurityNumber varchar(20))

ALTER TABLE tst_tbl_indexes ADD CONSTRAINT PK_tst_tbl_id PRIMARY KEY CLUSTERED(ID)

CREATE UNIQUE INDEX idx_tst_tbl_SN ON tst_tbl_indexes(SecurityNumber)

go

--OK, now lets see what we have :

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- we get nothing - and why not ? remember the name ? we haven't used any indexes yet...

-- but...

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s            -- note : this has parameters

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

 

-- does show us our two indexes, and even shows us a row for our table without an index.

-- So, it is not just for indexes huh !

-- now, remember our name ? operational - but are they working, no, the counts are zero.

-- lets now add some data...

INSERT tst_tbl_heap(ID,FirstName,SecurityNumber) values (1,'Mark','1234567')

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber) values (1,'Mark','1234567')

-- now lets look again at our DMV's

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- Now we get something ! despite being called indexes, not just for indexes.

-- Similarly for below we also return information

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- We wont bother anymore about the heap table, you can play with that

-- the lesson was that even a table without indexes is getting in there.

-- Now lets do an insert

 

INSERT tst_tbl_indexes(ID,FirstName,SecurityNumber)

SELECT 2 as id,'Mark2' as firstname,'2234567' as SecurityNumber union all

SELECT 3 as id,'Mark3' as firstname,'3234567' as SecurityNumber

 

-- now lets look again at our DMV's

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

 

-- So, what happens with a select ?

SELECT * from tst_tbl_indexes where ID = 2

-- now lets look again at our DMV's

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- we get user_seeks=1

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- singleton_lookups=1

-- so let’s now try another query

 

SELECT * from tst_tbl_indexes where ID in (1,2,3)

go

 

select object_name(s.object_id),i.name, *

from sys.dm_db_index_usage_stats s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- we get user_seeks=2

select object_name(s.object_id),i.name, *

from sys.dm_db_index_operational_stats (db_id(),0,NULL,0) s

inner join sys.indexes i on s.object_id=i.object_id and i.index_id=s.index_id

where object_name(s.object_id) in ('tst_tbl_indexes','tst_tbl_heap')

go

-- singleton_lookups=4 on our PK, nothing on the second (1 per selected rows above)

-- and finally clean up those tables

IF object_id('tst_tbl_heap','U') is not null drop table tst_tbl_heap

IF object_id('tst_tbl_indexes','U') is not null drop table tst_tbl_indexes

 

Now I am not going to go into detail - there are plenty of other postings out there

but what I am going to do is to summarise how we can use these differences

sys.dm_db_index_usage_stats

Is cleared when service starts / reboots etc. So keep that firmly in mind

Good to help identify if an index is used - no entry unless it is

Good to help identify if a table is used - no entry unless it is

Has handy dates can help show when types of activity were last used

Has counts to help identify frequency of use

If machine has been up for the entire period that encapsulates all usage then can highlight unused indexes and consider their removal

 

sys.dm_db_index_operational_stats

Exists when table / indexes are created

Shows volumes of activity (plan, rows, pages), not just an instance

Far more detailed to help identify what type of activity

Shows row_lock_wait_count - indicating lock contention

Can measure the cost of having an index, or missing one

 

Lets look at the different types of activity that is going to be recorded:

 

SQL Statement   Read Write

Select          Yes  No

Insert          No   Yes on all indexes

Update          Yes  Yes if row affects the index

Delete          Yes  Yes

 

Armed with the above table, looking at DMV's activity, you can soon work out where possible areas of further inspection are. Now go read books on line for a more detailed understanding of what each column is saying...

 

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

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

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com