Database Reference
In-Depth Information
SQL Server tracks index usage statistics internally and exposes it through
sys.dm_db_index_usage_stats
and
sys.dm_db_index_operation_stats
DMOs.
The first data management view-
sys.dm_db_index_usage_stats
provides information about different types
of index operations and the time when such an operation was last performed. Let's look at an example and create a
table, populate it with some data, and look at index usage statistics. The code for doing this is shown in Listing 6-11.
Listing 6-11.
Index usage statistics: Table creation
create table dbo.UsageDemo
(
ID int not null,
Col1 int not null,
Col2 int not null,
Placeholder char(8000) null
);
create unique clustered index IDX_CI
on dbo.UsageDemo(ID);
create unique nonclustered index IDX_NCI1
on dbo.UsageDemo(Col1);
create unique nonclustered index IDX_NCI2
on dbo.UsageDemo(Col2);
;with N1(C) as (select 0 union all select 0) -- 2 rows
,N2(C) as (select 0 from N1 as T1 CROSS JOIN N1 as T2) -- 4 rows
,N3(C) as (select 0 from N2 as T1 CROSS JOIN N2 as T2) -- 16 rows
,IDs(ID) as (select row_number() over (order by (select NULL)) from N3)
insert into dbo.UsageDemo(ID, Col1, Col2)
select ID, ID, ID
from IDs;
select
s.Name + N'.' + t.name as [Table]
,i.name as [Index]
,ius.user_seeks as [Seeks], ius.user_scans as [Scans]
,ius.user_lookups as [Lookups]
,ius.user_seeks + ius.user_scans + ius.user_lookups as [Reads]
,ius.user_updates as [Updates], ius.last_user_seek as [Last Seek]
,ius.last_user_scan as [Last Scan], ius.last_user_lookup as [Last Lookup]
,ius.last_user_update as [Last Update]
from
sys.tables t join sys.indexes i on
t.object_id = i.object_id
join sys.schemas s on
t.schema_id = s.schema_id
left outer join sys.dm_db_index_usage_stats ius on
ius.database_id = db_id() and
ius.object_id = i.object_id and
ius.index_id = i.index_id
