SQL基础【二十、索引】(超细致版本，前理论，后实践，应对sql面试绰绰有余)（三）

SELECT  
avg_user_impact AS average_improvement_percentage,  
avg_total_user_cost AS average_cost_of_query_without_missing_index,  
'CREATE INDEX ix_' + [statement] +  
ISNULL(equality_columns, '_') + 
ISNULL(inequality_columns, '_') + ' ON ' + [statement] +  
' (' + ISNULL(equality_columns, ' ') +  
ISNULL(inequality_columns, ' ') + ')' +  
ISNULL(' INCLUDE (' + included_columns + ')', '')  
AS create_missing_index_command 
FROM sys.dm_db_missing_index_details a INNER JOIN  
sys.dm_db_missing_index_groups b ON a.index_handle = b.index_handle 
INNER JOIN sys.dm_db_missing_index_group_stats c ON  
b.index_group_handle = c.group_handle 
WHERE avg_user_impact > = 4

SELECT TOP 100 execution_count,
           total_logical_reads /execution_count AS [Avg Logical Reads],
           total_elapsed_time /execution_count AS [Avg Elapsed Time],
                db_name(st.dbid) as [database name],
           object_name(st.dbid) as [object name],
           object_name(st.objectid) as [object name 1],
           SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1, 
           ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) 
             / 2) + 1) AS statement_text
  FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
 WHERE execution_count > 100
 ORDER BY 1 DESC

SELECT TOP 10 COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value as int))+'*',
'Resource') AS DBNAME,
SUBSTRING(text,
-- starting value for substring
        CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 + 1 END,
-- ending value for substring
        CASE WHEN statement_end_offset = 0
OR statement_end_offset = -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset = 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2  END + 1
)  AS TSQL,
total_logical_reads/execution_count AS AVG_LOGICAL_READS
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute = 'dbid'
ORDER BY AVG_LOGICAL_READS DESC