Designing and Tuning the Indexes - Pro SQL Server Internals - page 138

where
s.name = N'dbo' and t.name = N'UsageDemo'
order by
s.name, t.name, i.index_id

The User_seeks , user_scans , and user_lookups columns in sys.dm_db_index_usage_stats indicate how many
times the index was used for Index Seek , Index Scan , and Key Lookup operations respectively. User_updates indicates
the number of inserts, updates, and deletes the index handled. Sys.dm_index_usage_stats DMV also returns
statistics about index usage by the system as well as the last time the operation occurred.
As you can see in Figure 6-7 , both clustered and nonclustered indexes were updated once, which is the insert
statement in our case. Neither of the indexes were used for any type of read activity.

Figure 6-7. Index usage statistics after table creation

One thing worth mentioning is that we are using an outer join in the select. Sys.dm_db_index_usage_stats and
sys.dm_index_operation_stats DMO does not return any information about the index if it has not been used since a
statistics counters reset.

■ index usage statistics resets on sQL server restarts. Moreover, it clears whenever the database is
detached or shut down when the AUTO_CLOSE database property is on. Moreover, in sQL server 2012 and 2014, statistics
resets when the index is rebuilt.

Important

You must keep this behavior in mind during index analysis. It is not uncommon to have indexes to support
queries that execute on a given schedule. As an example, you can think about an index that supports a payroll process
running on a bi-weekly or monthly basis. Index statistics information could indicate that the index has not been used
for reads if SQL Server was recently restarted or, in the case of SQL Server 2012 and 2014, if index was recently rebuilt.

■ You can consider creating and dropping such an index on the schedule in order to avoid update overhead in
between-process executions.

Tip

Now let's run a few queries against the dbo.UsageDemo table, as shown in Listing 6-12.

Listing 6-12. Index usage statistics: Queries

-- Query 1: CI Seek (Singleton lookup)
select Placeholder from dbo.UsageDemo where ID = 5;

-- Query 2: CI Seek (Range Scan)
select count(*)
from dbo.UsageDemo with (index=IDX_CI)
where ID between 2 and 6;