索引使用次数、索引效率、占用CPU检测、索引缺失

默认分类 | 2017-09-18 20:27:10 | 148次阅读 | 0评

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 2005dmvs返回的相关资料时,请务必将以上的观点装在脑海中。只有当你确信从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: 通过用户查询执行的扫描次数。 
  个人理解:此统计表扫描的次数,无索引配合
user_lookups: 通过用户查询执行的查找次数。 
 个人理解:用户通过索引查找,在使用RID或聚集索引查找数据的次数,对于堆表或聚集表数据而言和索引配合使用次数
user_updates:  通过用户查询执行的更新次数。 
  个人理解:索引或表的更新次数

我们可以清晰的看到,那些索引用的多,那些索引没用过,大家可以根据查询出来的东西去分析自己的数据索引和表

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;

查询结果如下:

 

 

博友评论,共0条
最新评论