SQLServer2005 评估和管理索引

概述SQLServer2005动态管理视图(DMVs)返回会话、事务、请求的服务器状态信息。它可用于诊断、内存和过程调优、监控（SQLServer2000不支持）。SQLServer引擎跟踪详细的资源使用情况，用select语句从DMVs中可查到，但是这些信息不会长期驻留在磁盘上。 由于索引提供了代替表扫描的一个选择，且DMVs返回索引使用计数，所以可以比较索引的成本和其性能。这个比较包括保持索引最新

sqlServer2005动态管理视图(DMVs)返回会话、事务、请求的服务器状态信息。它可用于诊断、内存和过程调优、监控（sqlServer2000不支持）。sqlServer引擎跟踪详细的资源使用情况，用select语句从DMVs中可查到，但是这些信息不会长期驻留在磁盘上。

由于索引提供了代替表扫描的一个选择，且DMVs返回索引使用计数，所以可以比较索引的成本和其性能。这个比较包括保持索引最新的成本，与使用索引而不是表扫描读数据的性能之比。谨记一个更新或删除 *** 作先要读数据从而定位数据，然后对定位的数据进行写 *** 作。一个插入 *** 作在所有的索引上只是写 *** 作。因此，一个大量的插入将使写 *** 作次数超过读 *** 作次数。一个大量的更改 *** 作（包括更新和删除），读和写的次数通常很接近（假定没有‘记录找不到’的情况发生）。一个大量的读 *** 作，读的次数将超过写。引用约束如外键还要求额外的读 *** 作（对于插入、更新、删除而言）去确保引用完整性得到维护

哪些表和索引是没用或者很少用的？

---1. 未使用的表和索引。表都有一个索引ID，如果是0则为堆表，1则为聚集索引
Declare @dbID int
Select @dbID = db_ID('northwind')
Select  objectname=object_name(i.object_ID)
       ,indexname=i.name

,i.index_ID
from sys.indexes i,sys.objects o
where objectproperty(o.object_ID,'IsUsertable') = 1
and i.index_ID NOT IN (select s.index_ID
             fromsys.dm_db_index_usage_stats s
              where s.object_ID=i.object_IDand
                       i.index_ID=s.index_ID and
                       database_ID = @dbID )
and o.object_ID = i.object_ID
order by objectname,i.index_ID,indexname asc

--2. 缺失的索引

SELECT  top 50 
 [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
,user_seeks,user_scans
 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
WHERE statement liKE '%tablename%'
ORDER BY [Total Cost] DESC;

--3.使用很少的索引和频繁使用的索引一样，都会记录在sys.dm_db_index_usage_stats中。为了找出这些索引，需要查看 诸如user_seeks、 user_scansuser_lookups和user_updates的列。

;WITH IXC AS(
SELECT IXC.object_ID,
IXC.index_ID,
IXC.index_column_ID,
IXC.is_descending_key,
IXC.is_included_column,
column_name = C.name
FROM sys.index_columns IXC
INNER JOIN sys.columns C ON IXC.object_ID = C.object_ID AND IXC.column_ID = C.column_ID
)
SELECT top 50
o.name AS 表名
,i.name AS 索引名
,i.index_ID AS 索引ID
,dm_ius.user_seeks AS 搜索次数
,dm_ius.user_scans AS 扫描次数
,dm_ius.user_lookups AS 查找次数
,dm_ius.user_updates AS 更新次数
,p.tableRows as 表行数
,index_columns = Stuff(IXC_Col.index_columns,1,2,N'')
,index_columns_include = Stuff(IXC_Col_INCLUDE.index_columns_include,'DROP INDEX ' + QUOTEname(s.name) + '.' + QUOTEname(OBJECT_name(i.OBJECT_ID))
 +  '.' + QUOTEname(i.name) AS '删除语句'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_ID = dm_ius.index_ID AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_ID = s.schema_ID
INNER JOIN (SELECT SUM(p.rows) tableRows,p.index_ID,p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_ID,p.OBJECT_ID) p
ON p.index_ID = dm_ius.index_ID AND dm_ius.OBJECT_ID = p.OBJECT_ID
CROSS APPLY(
SELECT index_columns = (
SELECT N',' + quotename(column_name) + CASE is_descending_key
WHEN 1 THEN N' DESC '
ELSE N''
END
FROM IXC
WHERE object_ID = I.object_ID
AND index_ID = I.index_ID
AND is_included_column = 0
ORDER BY index_column_ID
FOR xml path(''),root('r'),TYPE
).value('/r[1]','nvarchar(max)')
) IXC_Col
OUTER APPLY(
SELECT index_columns_include = (
SELECT N',' + quotename(column_name) 
FROM IXC
WHERE object_ID = I.object_ID
AND index_ID = I.index_ID
AND is_included_column = 1
ORDER BY index_column_ID
FOR xml path(''),'nvarchar(max)')
) IXC_Col_INCLUDE
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUsertable') = 1
AND dm_ius.database_ID = DB_ID()
--AND i.type_desc = 'nonclustered'
--AND i.is_primary_key = 0
--AND i.is_unique_constraint = 0
and o.name='tablename'   --根据实际修改表名
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC

结论：user_updates很大，而发现user_seeks和user_scans很少或者就是0，那就说明该索引一直在更新， 但是从来不被使用，仅仅创建和修改，没有为查询提供任何帮助，就可以考虑删除了

总结

以上是内存溢出为你收集整理的SQLServer2005 评估和管理索引全部内容，希望文章能够帮你解决SQLServer2005 评估和管理索引所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。