/*------------------------------------------------------------------------------------------------------------

--find_missing_indexes_with_dynamic_management_views_0025.sql


Anytime you are doing performance tuning in SQL server, it is a challenge to find indexes that will make the 
biggest impact to your query time and stored procedure time.  You can improve your disk usage and execution 
plans by using the correct index choices.

In this script, we are using dynamic management views (dmv) and functions to find missing indexes in the 
current database.  For further explanation, please visit the MSDN site here

http://msdn.microsoft.com/en-us/library/ms345421%28SQL.90%29.aspx



------------------------------------------------------------------------------------------------------------*/

USE [ENTER DATABASE]
GO


SELECT 

migs.group_handle, 
object_name(object_id) obj,
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) impact

FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
    ON (migs.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 avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC;


/*

   
   

Compiled by http://www.learningsqlserver2008.com/, Kash Data Consulting LLC 

Disclaimer:

All sample code and scripts are compiled by Kash Data Consulting LLC for illustrative purposes only. Kash Data Consulting LLC, 
therefore, cannot guarantee or imply reliability, or function of this code or scripts.

All code contained herein are provided to you "AS IS" without any warranties of any kind. Please test all code and 
scripts in test environment before deployment in production systems.

*/