索引使用次数、索引效率、占用CPU检测、索引缺失
|
http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html (欣赏别人的,供以后深入了解) 当我们明白了什么是索引，什么时间创建索引以后，我们就会想，我们创建的索引到底效率执行的怎么样？好不好？我们创建的对不对？ 首先我们来认识一下DMV，DMV?(dynamic?management?view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL?Server?2005时，微软介绍了许多被称为dmvs的系统视图，让您可以探测SQL?Server?的健康状况，诊断问题，或查看SQL?Server实例的运行信息。统计数据是在SQL?Server运行的时候开始收集的，并且在SQL?Server每次启动的时候，统计数据将会被重置。当你删除或者重新创建其组件时，某些dmv的统计数据也可以被重置，例如存储过程和表，而其它的dmv信息在运行dbcc命令时也可以被重置。 当你使用一个dmv时，你需要紧记SQL?Server收集这些信息有多长时间了，以确定这些从dmv返回的数据到底有多少可用性。如果SQL?Server只运行了很短的一段时间，你可能不想去使用一些dmv统计数据，因为他们并不是一个能够代表SQL?Server实例可能遇到的真实工作负载的样本。另一方面，SQL?Server只能维持一定量的信息，有些信息在进行SQL?Server性能管理活动的时候可能丢失，所以如果SQL?Server已经运行了相当长的一段时间，一些统计数据就有可能已被覆盖。 因此，任何时候你使用dmv，当你查看从SQL?Server?2005的dmvs返回的相关资料时，请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的，你才能变更数据库或者应用程序代码。 下面就看一下dmv到底能带给我们那些好的功能呢？ 1.51?：索引使用次数 我们下看一下下面两种查询方式返回的结果（这两种查询的查询用途一致） ①---- declare?@dbid?int select?@dbid?=?db_id() select?objectname=object_name(s.object_id),?s.object_id,?indexname=i.name,?i.index_id ????????????,?user_seeks,?user_scans,?user_lookups,?user_updates from?sys.dm_db_index_usage_stats?s, ????????????sys.indexes?i where?database_id?=?@dbid?and?objectproperty(s.object_id,'IsUserTable')?=?1 and?i.object_id?=?s.object_id and?i.index_id?=?s.index_id order?by?(user_seeks?+?user_scans?+?user_lookups?+?user_updates)?asc 返回查询结果 ?
? ②：使用多的索引排在前面 SELECT??objects.name?, ????????databases.name?, ????????indexes.name?, ????????user_seeks?, ????????user_scans?, ????????user_lookups?, ????????partition_stats.row_count FROM????sys.dm_db_index_usage_stats?stats ????????LEFT?JOIN?sys.objects?objects?ON?stats.object_id?=?objects.object_id ????????LEFT?JOIN?sys.databases?databases?ON?databases.database_id?=?stats.database_id ????????LEFT?JOIN?sys.indexes?indexes?ON?indexes.index_id?=?stats.index_id ?????????????????????????????????????????AND?stats.object_id?=?indexes.object_id ????????LEFT??JOIN?sys.dm_db_partition_stats?partition_stats?ON?stats.object_id?=?partition_stats.object_id ??????????????????????????????????????????????????????????????AND?indexes.index_id?=?partition_stats.index_id WHERE???1?=?1 --AND?databases.database_id?=?7 ????????AND?objects.name?IS?NOT?NULL ????????AND?indexes.name?IS?NOT?NULL ????????AND?user_scans>0 ORDER?BY?user_scans?DESC?, ????????stats.object_id?, ????????indexes.index_id 返回查询结果 ?
?
user_seeks?:?通过用户查询执行的搜索次数。?
user_scans:?通过用户查询执行的扫描次数。? 我们可以清晰的看到，那些索引用的多，那些索引没用过，大家可以根据查询出来的东西去分析自己的数据索引和表 1.52?：索引提高了多少性能 新建了索引到底增加了多少数据的效率呢？到底提高了多少性能呢？运行如下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?>?=?40 ? 返回结果 ?
? 虽然用户能够修改性能提高的百分比，但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了 1.53?：最占用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?; ?
? 看到了吗？直接可以定位到你的sql语句，优化去吧。还等什么呢？ 1.54：缺失索引 缺失索引就是帮你查找你的数据库缺少什么索引，告诉你那些字段需要加上索引，这样你就可以根据提示添加你数据库缺少的索引了 SELECT?TOP?10 [Total?Cost]?=?ROUND(avg_total_user_cost?*?avg_user_impact?*?(user_seeks?+?user_scans),0) ,?avg_user_impact ,?TableName?=?statement ,?[EqualityUsage]?=?equality_columns ,?[InequalityUsage]?=?inequality_columns ,?[Include?Cloumns]?=?included_columns FROM????sys.dm_db_missing_index_groups?g INNER?JOIN?sys.dm_db_missing_index_group_stats?s ON?s.group_handle?=?g.index_group_handle INNER?JOIN?sys.dm_db_missing_index_details?d ON?d.index_handle?=?g.index_handle ORDER?BY?[Total?Cost]?DESC; 查询结果如下： ?
? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
