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

-- Query 3: CI Scan
select count(*) from dbo.UsageDemo with (index=IDX_CI);
-- Query 4: NCI Seek (Singleton lookup + Key Lookup)
select Placeholder from dbo.UsageDemo where Col1 = 5;
-- Query 5: NCI Seek (Range Scan - all data from the table)
select count(*) from dbo.UsageDemo where Col1 > -1;
-- Query 6: NCI Seek (Range Scan + Key Lookup)
select sum(Col2)
from dbo.UsageDemo with (index = IDX_NCI1)
where Col1 between 1 and 5;
-- Queries 7-8: Updates
update dbo.UsageDemo set Col2 = -3 where Col1 = 3
update dbo.UsageDemo set Col2 = -4 where Col1 = 4
If you run select, which displays index usage statistics again, you would see results shown in Figure 6-8 .
Figure 6-8. Index usage statistics after several queries
There are a couple important things to note here. First, sys.dm_db_index_usage_stats returns how many times
queries had corresponding operations in the execution plan, rather than the number of times that operations were
executed. For example, there are only four Lookup operations returned for the IDX_CI index, even though SQL Server
did Key Lookup for eight rows.
Second, sys.dm_db_index_usage_stats DMV counts both Singleton Lookup and Range Scan as Seek, which
corresponds to the Index Seek operator. This could mask the situation when Index Seek performs range scans on a
large number of rows. For example, the fifth query in our example scanned all rows from the IDX_NCI1 index although
it was counted as Seek rather than Scan.
When you do such an analysis in production systems, you can consider removing indexes, which handle more
updates than reads, similar to IDX_NCI2 from our example. In some cases, it is also beneficial not to count s can
operations towards reads, especially in OLTP environments, where queries, which perform Index Scan, should be
optimized.
While sys.dm_db_index_usage provides a good high-level overview of index usage based on operations from the
execution plan, sys.dm_db_index_operation_stats dives deeper and provides detailed level I/O, access methods,
and locking statistics for the indexes.
The key difference between two DMOs is how they collect data. Sys.dm_db_index_usage_stats tracks how
many times an operation appeared in the execution plan. Alternatively, s ys.dm_db_index_operation_stats tracks
operations at the row level. In our Key Lookup example, sys.dm_db_index_operation_stats would report eight
operations rather than four.
Even though sys.dm_db_index_operation_stats provides very detailed information about index usage, I/O, and
locking overhead, it could become overwhelming, especially during the initial performance tuning stage. It is usually
easier to do an initial analysis with sys.dm_db_index_usage_stats and use sys.dm_db_index_operation_stats later
when fine-tuning the system.