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

2021-07-14 11:41:22

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


返回结果:


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


虽然用户能够修改性能提高的百分比，但以上查询返回所有能够将性能提高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


上一篇：Crumpet – 使用很简单的响应式前端开发框架


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