Missing Indexes, Plans & Plan cache.

Below listed are some of t he common queries I use to find missing indexes, costly queries running per day, plans that use a specific index and objects in plan cache.

I normally dont just go and create the missing indexes suggested; I would rather look into the costly queries running per day and how many times are they run and then compare that list with the missing indexes one. Good thing about SQL Server 2008 is that it shows the missing index details and the query to create it in the costly queries list. Please use them wisely as indexes are damn costly !

FIND MISSING INDEXES

SELECT [statement] AS full_object_name,
unique_compiles, avg_user_impact, user_seeks, column_name, column_usage
FROM sys.dm_db_missing_index_groups G
JOIN sys.dm_db_missing_index_group_stats GS 

ON G.index_group_handle = GS.group_handle
JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
CROSS APPLY sys.dm_db_missing_index_columns(D.index_handle) DC 
ORDER BY avg_user_impact DESC;

TOP 25 COSTLY QUERIES FROM YESTERDAY

SELECT TOP 25 qs.execution_count,qs.last_execution_time, 

tot_CPU_inSec = qs.total_worker_time/1000000, 

avg_CPU_inSec =(qs.total_worker_time/1000000)/ qs.execution_count, 

tot_elapsed_time_inSec = qs.total_elapsed_time/1000000, st.text, qp.query_plan

FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE ((qs.total_worker_time/1000000)/ qs.execution_count)> 0 

and qs.last_execution_time >GETDATE()-1
ORDER BY ((qs.total_worker_time/1000000)/ qs.execution_count) DESC;

Plans that use an index

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @IndexName ASNVARCHAR(128)='ix2_spt_values_nu_nc';
-- Make sure the name passed is appropriately quoted
IF (LEFT(@IndexName, 1)<>'['AND RIGHT(@IndexName, 1)<>']') SET @IndexName =QUOTENAME(@IndexName);
--Handle the case where the left or right was quoted manually but not the opposite side
IF LEFT(@IndexName, 1)<>'['SET @IndexName ='['+@IndexName;
IF RIGHT(@IndexName, 1)<>']'SET @IndexName = @IndexName +']' ;
 -- Dig into the plan cache and find all plans using this index
;WITH XMLNAMESPACES
(DEFAULT'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT stmt.value('(@StatementText)[1]','varchar(max)')AS SQL_Text,
obj.value('(@Database)[1]','varchar(128)') AS DatabaseName,
obj.value('(@Schema)[1]','varchar(128)') AS SchemaName,
obj.value('(@Table)[1]','varchar(128)') AS TableName,
obj.value('(@Index)[1]','varchar(128)') AS IndexName,
obj.value('(@IndexKind)[1]','varchar(128)') AS IndexKind, 

cp.plan_handle,query_plan
FROM sys.dm_exec_cached_plansAS cp
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple')AS batch(stmt)

CROSS APPLY stmt.nodes('.//IndexScan/Object[@Index=sql:variable("@IndexName")]')AS idx(obj)
OPTION (MAXDOP 1,RECOMPILE);

List objects in Plan Cache

select count(*) as cached_pages_count,
       obj.name as objectname,
       ind.name as indexname,
       obj.index_id as indexid
from sys.dm_os_buffer_descriptors as bd
    inner join
    ( select object_id as objectid, object_name(object_id) as name,
       index_id, allocation_unit_id
       from sys.allocation_units as au
       inner join sys.partitions as p
       on au.container_id = p.hobt_id
       and (au.type = 1 or au.type = 3)
       union all
        select object_id as objectid, object_name(object_id) as name,
        index_id,allocation_unit_id
        from sys.allocation_units as au
            inner join sys.partitions as p
            on au.container_id = p.partition_id
            and au.type = 2
    ) as obj
       on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind 
  on  obj.objectid = ind.object_id
 and  obj.index_id = ind.index_id
where bd.database_id = db_id()
  and bd.page_type in ('data_page', 'index_page')
group by obj.name, ind.name, obj.index_id
order by cached_pages_count desc;

MvM