Databases Reference
In-Depth Information
The Missing Indexes Feature
SQL Server does provide a second approach that can help you to find useful indexes
for your existing queries. Although not as powerful as the DTA, this option, called the
Missing Indexes feature, does not require the database administrator to decide when
tuning is needed, to explicitly identify what workload represents the load to tune, or to
run any tool. This is a lightweight feature which is always on and, like the DTA, was also
introduced with SQL Server 2005. Let's take a look at what it does.
During optimization, the Query Optimizer defines what the best indexes for a query are
and, if these indexes don't exist, it will make this index information available in the XML
plan for a particular plan (as well as the graphical plan, as of SQL Server Management
Studio 2008). Alternatively, it will aggregate this information for queries optimized since
the instance was started, and make it all available on the
sys.dm_db_missing_index
DMVs. Note that, just by displaying this information, the Query Optimizer is not only
warning you that it might not be selecting an efficient plan; it is also showing you which
indexes
may
help to improve the performance of your query. In addition, database
administrators and developers should be aware of the limitations of this feature, as
described on the Topics Online entry,
Limitations of the Missing Indexes Feature
.
So, with all that in mind, let's take a quick look to see how this feature works. Create
the
dbo.SalesOrderDetail
table on the
AdventureWorks
database by running the
following statement:
SELECT
*
INTO
dbo
.
SalesOrderDetail
FROM
sales
.
SalesOrderDetail
Listing 4-19.
