sql server索引功能資料_ZenDei技術網路在線

無論何時對基礎數據執行插入、更新或刪除操作，SQL Server 資料庫引擎都會自動維護索引。隨著時間的推移，這些修改可能會導致索引中的信息分散在資料庫中（含有碎片）。當索引包含的頁中的邏輯排序（基於鍵值）與數據文件中的物理排序不匹配時，就存在碎片。碎片非常多的索引可能會降低查詢性能，導致應用程式響 ...

無論何時對基礎數據執行插入、更新或刪除操作，SQL Server 資料庫引擎都會自動維護索引。隨著時間的推移，這些修改可能會導致索引中的信息分散在資料庫中（含有碎片）。當索引包含的頁中的邏輯排序（基於鍵值）與數據文件中的物理排序不匹配時，就存在碎片。碎片非常多的索引可能會降低查詢性能，導致應用程式響應緩慢。下麵是一些簡單的查詢索引的sql。MSSQL的 DBA_Huangzj 提供。

判斷無用的索引：

__何問起 hovertree.com
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
SELECT TOP 30  
        DB_NAME() AS DatabaseName ,  
        '[' + SCHEMA_NAME(o.Schema_ID) + ']' + '.' + '['  
        + OBJECT_NAME(s.[object_id]) + ']' AS TableName ,  
        i.name AS IndexName ,  
        i.type AS IndexType ,  
        s.user_updates ,  
        s.system_seeks + s.system_scans + s.system_lookups AS [System_usage]  
FROM    sys.dm_db_index_usage_stats s  
        INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]  
                                    AND s.index_id = i.index_id  
        INNER JOIN sys.objects o ON i.object_id = O.object_id  
WHERE   s.database_id = DB_ID()  
        AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0  
        AND s.user_seeks = 0  
        AND s.user_scans = 0  
        AND s.user_lookups = 0  
        AND i.name IS NOT NULL  
ORDER BY s.user_updates DESC

判斷哪些索引缺失：

__何問起 hovertree.com
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
SELECT TOP 30  
        ROUND(s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks  
                                                            + s.user_scans ),  
              0) AS [Total Cost] ,  
        s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks  
                                                              + s.user_scans ) AS Improvement_Measure ,  
        DB_NAME() AS DatabaseName ,  
        d.[statement] AS [Table Name] ,  
        equality_columns ,  
        inequality_columns ,  
        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  
WHERE   s.avg_total_user_cost * ( s.avg_user_impact / 100.0 ) * ( s.user_seeks  
                                                              + s.user_scans ) > 10  
ORDER BY [Total Cost] DESC ,  
        s.avg_total_user_cost * s.avg_user_impact * ( s.user_seeks  
                                                      + s.user_scans ) DESC

看看那些索引維護成本很高通俗的說就是更新次數大於使用這個索引的次數

__何問起 hovertree.com
SELECT TOP 20  
        DB_NAME() AS DatabaseName ,  
        '[' + SCHEMA_NAME(o.Schema_ID) + ']' + '.' + '['  
        + OBJECT_NAME(s.[object_id]) + ']' AS TableName ,  
        i.name AS IndexName ,  
        i.type AS IndexType ,  
        ( s.user_updates ) AS update_usage ,  
        ( s.user_seeks + s.user_scans + s.user_lookups ) AS retrieval_usage ,  
        ( s.user_updates ) - ( s.user_seeks + user_scans + s.user_lookups ) AS maintenance_cost ,  
        s.system_seeks + s.system_scans + s.system_lookups AS system_usage ,  
        s.last_user_seek ,  
        s.last_user_scan ,  
        s.last_user_lookup  
FROM    sys.dm_db_index_usage_stats s  
        INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]  
                                    AND s.index_id = i.index_id  
        INNER JOIN sys.objects o ON i.object_id = O.object_id  
WHERE   s.database_id = DB_ID('{0}')  
        AND i.name IS NOT NULL  
        AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0  
        AND ( s.user_seeks + s.user_scans + s.user_lookups ) > 0  
ORDER BY maintenance_cost DESC

常常使用的索引查看看看你常用使用的索引是否建立的合理

__何問起 hovertree.com
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED  
SELECT TOP 20  
DB_NAME() AS DatabaseName  
, '['+SCHEMA_NAME(o.Schema_ID)+']'+'.'+'['+OBJECT_NAME(s.[object_id]) +']'AS TableName  
, i.name AS IndexName  
, i.type as IndexType  
, (s.user_seeks + s.user_scans + s.user_lookups) AS Usage  
, s.user_updates  
FROM sys.dm_db_index_usage_stats s  
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id]  
AND s.index_id = i.index_id  
INNER JOIN sys.objects o ON i.object_id = O.object_id  
WHERE s.database_id = DB_ID()  
AND i.name IS NOT NULL  
AND OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0  
ORDER BY Usage DESC

決定使用哪種碎片整理方法的第一步是分析索引以確定碎片程度 DBCC SHOWCONTIG(表名) WITH ALL_INDEXES 先查碎片信息。

重新組織：

若要重新組織一個或多個索引，可以使用帶 REORGANIZE 子句的 ALTER INDEX 語句。此語句可以替代 DBCC INDEXDEFRAG 語句。若要重新組織已分區索引的單個分區，可以使用 ALTER INDEX 的 PARTITION 子句。

重新組織索引是通過對葉頁進行物理重新排序，使其與葉節點的邏輯順序（從左到右）相匹配，從而對錶或視圖的聚集索引和非聚集索引的葉級別進行碎片整理。使頁有序可以提高索引掃描的性能。索引在分配給它的現有頁內重新組織，而不會分配新頁。如果索引跨多個文件，將一次重新組織一個文件，不會在文件之間遷移頁。

重新組織還會壓縮索引頁。如果還有可用的磁碟空間，將刪除此壓縮過程中生成的所有空頁。壓縮基於 sys.indexes 目錄視圖中的填充因數值。

重新組織進程使用最少的系統資源。而且，重新組織是自動聯機執行的。該進程不持有長期阻塞鎖，所以不會阻止運行查詢或更新。

索引碎片不太多時，可以重新組織索引。請參閱上面的表，瞭解有關碎片的指導原則。不過，如果索引碎片非常多，重新生成索引則可以獲得更好的結果。

重新組織索引時，除了重新組織一個或多個索引外，預設情況下還將壓縮聚集索引或基礎表中包含的大型對象數據類型 (LOB)。數據類型 image、text、ntext、varchar(max)、nvarchar(max)、varbinary(max) 和 xml 都是大型對象數據類型。壓縮此數據可以改善磁碟空間使用情況：

重新組織指定的聚集索引將壓縮該聚集索引的葉級別（數據行）包含的所有 LOB 列。
重新組織非聚集索引將壓縮該索引中屬於非鍵（包含性）列的所有 LOB 列。
如果指定 ALL，將重新組織與指定的表或視圖相關聯的所有索引，並壓縮與聚集索引、基礎表或帶有包含列的非聚集索引相關聯的所有 LOB 列。
如果 LOB 列不存在，則忽略 LOB_COMPACTION 子句。

http://www.cnblogs.com/roucheng/p/3541165.html

重新生成：

重新生成索引將刪除該索引並創建一個新索引。此過程中將刪除碎片，通過使用指定的或現有的填充因數設置壓縮頁來回收磁碟空間，併在連續頁中對索引行重新排序（根據需要分配新頁）。這樣可以減少獲取所請求數據所需的頁讀取數，從而提高磁碟性能。

可以使用下列方法重新生成聚集索引和非聚集索引：

帶 REBUILD 子句的 ALTER INDEX。此語句將替換 DBCC DBREINDEX 語句。
帶 DROP_EXISTING 子句的 CREATE INDEX。

重新組織或重新生成索引

在“對象資源管理器”中，展開包含您要重新組織索引的表的資料庫。
展開“表”文件夾。
展開要為其重新組織索引的表。
展開“索引”文件夾。
右鍵單擊要重新組織的索引，然後選擇“重新組織”。
在“重新組織索引”對話框中，確認正確的索引位於“要重新組織的索引”網格中，然後單擊“確定”。
選中“壓縮大型對象列數據”覆選框，以指定也壓縮所有包含大型對象 (LOB) 數據的頁。
單擊“確定”。

重新組織表中的所有索引

在“對象資源管理器”中，展開包含您要重新組織索引的表的資料庫。
展開“表”文件夾。
展開要為其重新組織索引的表。
右鍵單擊“索引”文件夾，然後選擇“全部重新組織”。
在“重新組織索引”對話框中，確認正確的索引位於“要重新組織的索引”中。若要從“要重新組織的索引”網格中刪除索引，請選擇該索引，再按 Delete 鍵。
選中“壓縮大型對象列數據”覆選框，以指定也壓縮所有包含大型對象 (LOB) 數據的頁。
單擊“確定”。

重新生成索引

在“對象資源管理器”中，展開包含您要重新組織索引的表的資料庫。
展開“表”文件夾。
展開要為其重新組織索引的表。
展開“索引”文件夾。
右鍵單擊要重新組織的索引，然後選擇“重新組織”。
在“重新生成索引”對話框中，確認正確的索引位於“要重新生成的索引”網格中，然後單擊“確定”。
選中“壓縮大型對象列數據”覆選框，以指定也壓縮所有包含大型對象 (LOB) 數據的頁。
單擊“確定”。