Phân tích SQL Server index

Trong SQL Server, có 1 DMF rất thú vị là sys.dm_db_index_operational_stats cho phép ta phân tích các index trong 1 table nào đó, ví dụ khi một index seek được sử dụng, nhưng phải tốn thêm thao tác lookup, nó sẽ cho ta biết thao tác lookup này đã duyệt qua bao nhiêu record.

Như câu lệnh dưới đây, để ý giá trị singleton_lookup_count trong câu truy vấn dưới đây, nó ta ta biết số lần index đươc sử dụng để lấy một dòng qua thao tác lookup.

SELECT OBJECT_NAME(ios.object_id) AS table_name
,i.name AS index_name
,ios.singleton_lookup_count
FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID('Sales.SalesOrderDetail'),NULL,NULL) ios
INNER JOIN sys.indexes i
ON i.object_id = ios.object_id
AND i.index_id = ios.index_id
ORDER BY ios. singleton_lookup_count DESC

Tham khảo thêm: http://www.jasonstrate.com/2010/09/index-black-ops-part-3-index-usage/

Một ví dụ khác, như trong đoạn script ở đây, tác giả dùng sys.dm_db_index_operational_stats để biết số lượng update, delete, insert của mỗi index trong database

SELECT DB_NAME([database_id]) AS [Database]
    ,iops.[object_id] AS [ObjectID]
    ,QUOTENAME(OBJECT_SCHEMA_NAME(iops.[object_id], [database_id])) 
        + N'.' + QUOTENAME(OBJECT_NAME(iops.[object_id], [database_id])) AS [ObjectName]
    ,i.[index_id] AS [IndexID]
    ,i.[name] AS [IndexName]
    ,i.[fill_factor] AS [IndexFillFactor]
    ,iops.[partition_number] AS [PartitionNumber]
    ,CASE 
        WHEN i.[is_unique] = 1
            THEN 'UNIQUE '
        ELSE ''
        END + i.[type_desc] AS [IndexType]
    ,iops.[leaf_insert_count] AS [LeafInsertCount]
    ,iops.[leaf_delete_count] AS [LeafDeleteCount]
    ,iops.[leaf_update_count] AS [LeafUpdateCount]
FROM [sys].[dm_db_index_operational_stats](DB_ID(), NULL, NULL, NULL) iops
INNER JOIN [sys].[indexes] i ON i.[object_id] = iops.[object_id]
    AND i.[index_id] = iops.[index_id]
ORDER BY iops.[page_latch_wait_count] + iops.[page_io_latch_wait_count] DESC

Tham khảo: http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2013/03/19/using-sysdm_db_index_operational_stats-to-analyse-how-indexes-are-utilised/