Databases Reference
In-Depth Information
Figure 4-8:
Plan without the Missing Index warning.
Finally, remove the
dbo.SalesOrderDetail
table you've just created by running the
following statement:
DROP
TABLE
dbo
.
SalesOrderDetail
Listing 4-25.
Unused Indexes
I'll end this chapter on indexes by introducing the functionality of the
sys.dm_db_
index_usage_stats
DMV, which you can use to learn about the operations performed
by your indexes, and which is especially helpful in discovering indexes that are not used
by any query, or are only minimally used. As we've already discussed, indexes that are not
being used will provide no benefit to your databases, but
will
use valuable disk space, slow
your update operations, and should be considered for removal.
The
sys.dm_db_index_usage_stats
DMV stores the number of seek, scan, lookup,
and update operations performed by both user and system queries, including the last
time each type of operation was performed. Keep in mind that this DMV, in addition to