程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQLServer2005 評估和管理索引

SQLServer2005 評估和管理索引

編輯:關於SqlServer

       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_scans、user_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,1,2,N'')

      ,'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(''),root('r'),TYPE

      )。value('/r[1]','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,那就說明該索引一直在更新,

      但是從來不被使用,僅僅創建和修改,沒有為查詢提供任何幫助,就可以考慮刪除了

    1. 上一頁:
    2. 下一頁:
    Copyright © 程式師世界 All Rights Reserved