Database Reference
In-Depth Information
from
sys.procedures as p with (nolock) join sys.schemas s with (nolock) on
p.schema_id = s.schema_id
join sys.dm_exec_procedure_stats as ps with (nolock) on
p.object_id = ps.object_id
outer apply sys.dm_exec_query_plan(ps.plan_handle) qp
order by
[Avg IO] desc
option (recompile);
Figure 6-11.
Sys.dm_exec_procedure_stats results
SQL Server collects information about missing indexes in the system, and exposes it via a set of DMVs with
names starting at
sys.dm_db_missing_index
. Moreover, you can see suggestions for creating such indexes in the
execution plans displayed in Management Studio.
There are two caveats when dealing with suggestions about missing indexes. First, SQL Server suggests the index,
which only helps the particular query you are executing. It does not take update overhead, other queries, and existing
indexes into consideration. For example, if a table already has an index that covers the query with the exception of one
column, SQL Server suggests creating a new index rather than changing an existing one.
Moreover, suggested indexes help to improve the performance of a specific execution plan. SQL Server does not
consider indexes that can change the execution plan shape and, for example, use a more efficient join type for the query.
The quality of Database Engine Tuning Advisor (DTA) results greatly depends on the quality of the workload used
for analysis. Good and representative workload data leads to decent results, which is much better than suggestions
provided by missing indexes DMVs. Make sure to capture the workload, which includes data modification queries in
addition to select queries, if you use DTA.
Regardless of the quality of the tools, all of them have the same limitation. They are analyzing and tuning indexes
based on existing database schema and code. You can often achieve much better results by performing database
schema and code refactoring in addition to index tuning.
