5.2 索引提高了多少性能
新建了索引到底增加了多少数据的效率呢？到底提高了多少性能呢？运行如下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
返回结果:
虽然用户能够修改性能提高的百分比，但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了
5.3 最占用CPU、执行时间最长命令
这个和索引无关，但是还是在这里提出来，因为他也属于DMV带给我们的功能吗，他可以让你轻松查询出，那些sql语句占用你的cpu最高
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
