Databases Reference
In-Depth Information
determines that a particular index not present in the database would help reduce the cost of a query,
it will note that fact. Over time, as your workload runs on your database server, you will likely see a
growing number of proposed new indexes returned when you run this query. I strongly caution you
to not get overly enthusiastic about creating new indexes based solely on the results of this query.
Many people have proudly told me that they wrote a script that automatically creates every single
index that SQL Server identii es in this query, which is a huge mistake!
Instead, you should consider a number of factors before you start adding new indexes to your
tables. First, consider what type of workload you have and how volatile your table is. If you have
an OLTP type of workload, with a lot of writes to your table, you should be much more hesitant
about adding new indexes, as more indexes will slow down your insert/update/delete performance
on that table. Second, you should look at the
last_user_seek
column to get an idea of whether
this “missing” index would really affect your normal workload. If your
last_user_seek
is from a
few seconds or a few minutes ago, it is more likely to be part of your normal workload. If it is from
a few days or a few weeks ago, it is more likely to be from an ad hoc query or a reporting query,
and I would be much less inclined to add that index. You should also look at the
user_seeks
column to get an idea of how many times SQL Server has determined it would need this index,
along with the
avg_user_impact
and
avg_total_user_cost
columns to help assess how impor-
tant the index really might be.
You should also consider your existing indexes on the table in question. In many cases this query
will recommend a new index that is almost an exact duplicate of an existing index. You need to
apply some judgment and common sense and consider your complete workload before you start
adding new indexes based solely on the results of this query. Finally, you should be aware that if
you make any kind of index change on a particular table, the missing index statistics for that table
will be cleared out, and it will take some time (as your workload is running) for the missing index
statistics to show any information for that table. To understand how this could bite you, suppose
you had a table that needed three new indexes to help an important part of your normal work-
load. After a thorough analysis, you decide to add the i rst index. After that index is added, you
run this query again, and no results are returned for the table in question. This might lead you to
conclude that SQL Server does not need the other two indexes, which would probably be incor-
rect. You just need to wait for a period of time, depending on your workload, to see whether SQL
Server really needs any more indexes on the table. You can use the query shown in Listing 15-45
along with this query to help zero in on which indexes are really needed on each table.
Next, you will look for missing index warnings in the cached execution plans for stored procedures
in this database, using the query shown in Listing 15-47.
LISTING 15-47:
Missing index warnings for cached plans
-- Find missing index warnings for cached plans in the current database
-- Note: This query could take some time on a busy instance
SELECT TOP(25) OBJECT_NAME(objectid) AS [ObjectName],query_plan,
cp.objtype, cp.usecounts
FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
WHERE CAST(query_plan AS NVARCHAR(MAX)) LIKE N'%MissingIndex%'
AND dbid = DB_ID()
Search WWH ::
Custom Search