Database Reference
In-Depth Information
4.
On the show plan window, we can observe the hint about Missing Index (Impact %)
and suggestion to create an appropriate index displayed along with syntax to create
that index.
5.
The preceding information can also be obtained by using sys.dm_exec_cached_
plans DMV along with the information about how many times the plan has been
used during the query execution. Use the following TSQL:
SELECT * FROM
(SELECT user_seeks * avg_total_user_cost * (avg_user_impact *
0.01) AS
index_advantage, migs.* FROM sys.dm_db_missing_index_group_stats
migs) AS
migs_adv
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs_adv.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
ORDER BY migs_adv.index_advantage
6.
Further, let us obtain more details on missing index information. Right-click in the
Execution plan to select the Missing Index Details option to open a new Query
window to execute the creation of new index.
7.
Further, let us obtain the aggregated performance statistics based on the preceding
query execution. To get the relevant information, execute the following TSQL:
SELECT t.text,
st.total_logical_reads
FROM sys.dm_exec_query_stats st
CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) t
8.
Also, execute the following TSQL to obtain additional query monitoring performance:
;WITH CTE([QExecCount], [Total Disk IO], [Avg Disk IO],
[QueryStmtText], [QPlan], [QueyHash], [QueryPlanHash])
 
Search WWH ::




Custom Search