-------------------------------------------------------------------------------------------------
-- Author:		 Leonardo Pasta
-- Name:		 sp_DBA_QueriesMissingIndexes
-- Created on:   02/05/2008
-- Description:  Return the heavier queries in the procedure cache for whom the optimzer judges 
--               it could use a better index.
-- Sintax:       sp_DBA_QueriesMissingIndexes	@Table - Table name in current database for which 
--                                                       new indexes were suggested.
--                                              @CutOff (default=50) - Number of rows to be displayed.
--                                              @MinReadCost - Minimum IO cost to be considered.
--------------------------------------------------------------------------------------------------
USE master
GO
IF OBJECT_ID('sp_DBA_QueriesMissingIndexes') IS NOT NULL
  DROP PROCEDURE sp_DBA_QueriesMissingIndexes
GO
CREATE PROCEDURE sp_DBA_QueriesMissingIndexes(@Table varchar(100), @MinReadCost int=1000, @CutOff int=50) AS  
	DECLARE @DbName varchar(100)
	SET @DbName = '[' + DB_NAME() + ']'

    SET @Table = QUOTENAME(@Table)

	SELECT TOP (@CutOff)  
	 SUBSTRING(est.text, eqs.statement_start_offset/2, (CASE WHEN eqs.statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), est.text)) * 2 ELSE eqs.statement_end_offset end - eqs.statement_start_offset)/2) AS [Statement]   
	 , OBJECT_NAME(est.objectid) AS [Stored Procedure]  
     , eqp.query_plan  
	 , eqs.execution_count   
	 ,eqs.min_logical_reads, eqs.max_logical_reads, (eqs.total_logical_reads/eqs.execution_count) AS avg_logical_reads  
	 ,eqs.min_logical_writes, eqs.max_logical_writes, (eqs.total_logical_writes/eqs.execution_count) AS avg_logical_writes  
	 ,eqs.min_worker_time, eqs.max_worker_time, (eqs.total_worker_time/eqs.execution_count) AS avg_worker_time  
	 ,eqs.min_elapsed_time, eqs.max_elapsed_time, (eqs.total_elapsed_time/eqs.execution_count) AS avg_elapsed_time   
	  
	FROM  sys.dm_exec_query_stats eqs  
	CROSS APPLY sys.dm_exec_sql_text(eqs.sql_handle) est  
	CROSS APPLY sys.dm_exec_query_plan(eqs.plan_handle) eqp  
	WHERE eqp.query_plan.exist('declare default element namespace "http://schemas.microsoft.com/sqlserver/2004/07/showplan";
								//MissingIndexes//MissingIndex[@Database = sql:variable("@DbName") and @Table = sql:variable("@Table")]') = 1
	  AND eqs.max_logical_reads > @MinReadCost  
	ORDER BY avg_logical_reads DESC  
GO
EXEC sp_MS_marksystemobject sp_DBA_QueriesMissingIndexes
GO
GRANT EXEC ON sp_DBA_QueriesMissingIndexes TO PUBLIC
GO