Sql Server 索引使用情况及优化的相关Sql语句分享

我来为您详细讲解一下“Sql Server 索引使用情况及优化的相关Sql语句分享”的攻略。

一、索引使用情况的查看

1.1 查看表索引的使用情况

使用以下命令可以查看表的索引使用情况：

SELECT OBJECT_NAME(s.[object_id]) AS [Object Name], 
       i.name AS [Index Name], 
       s.user_seeks,
       s.user_scans,
       s.user_lookups,
       s.user_updates,
       s.last_user_seek,
       s.last_user_scan,
       s.last_user_lookup,
       s.last_user_update
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
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
ORDER BY [Object Name], [Index Name];

该脚本可以列出每个表的索引及其使用情况，其中包括 user_seeks、user_scans 和 user_lookups 三个指标：

• user_seeks：索引被使用进行精确查找的次数；
• user_scans：索引被扫描的次数；
• user_lookups：所有表查询中使用该索引进行查找的次数。

使用此脚本，可以了解哪些索引很少使用或几乎没被使用，进而优化这些索引。

1.2 查看每个索引最近使用的时间

使用以下命令可以查看每个索引在数据库中最近一次被使用的时间：

SELECT OBJECT_NAME(s.object_id) AS [Table Name], 
       i.name AS [Index Name], 
       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
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1;

该脚本查看了每个表的索引的最近使用情况，包括 last_user_seek、last_user_scan 和 last_user_lookup 三个指标，这些指标可以告诉我们，每个索引实际上最近一次被查找的时间是什么，这对于我们优化索引至关重要。

二、索引优化的方法

2.1 索引优化的方法

使用以下命令可以查看索引的建议及数据库的实际情况：

SELECT
    OBJECT_NAME(s.object_id) AS [Table Name],
    i.name AS [Index Name],
    i.type_desc AS [Index Type],
    i.is_primary_key AS [Primary Key?],
    i.is_unique AS [Unique?],
    i.fill_factor AS [Fill Factor],
    s.avg_fragmentation_in_percent AS [Avg Fragmentation %],
    s.page_count AS [Page Count]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
ORDER BY [Avg Fragmentation %] DESC OPTION (RECOMPILE);

该脚本可以查询数据库中所有表的索引的平均碎片化度、索引页数等信息，根据这些信息，可以确定哪些索引需要优化，比如：

• 平均碎片化度过高的索引需要进行重建或重新组织；
• 页面数量过大的索引可能会影响查询的性能，需要考虑拆分或优化索引等。

2.2 慢查询的优化

使用以下命令可以查询慢查询的情况：

SELECT TOP 20
    total_worker_time/execution_count AS [Avg CPU Time],
    total_elapsed_time/execution_count AS [Avg Duration],
    (SELECT TOP 1 [text]
        FROM sys.dm_exec_sql_text([sql_handle])) AS [Query Text],
    creation_time AS [Cached Time],
    execution_count,
    total_worker_time,
    total_elapsed_time
FROM sys.dm_exec_query_stats 
WHERE total_elapsed_time/execution_count > 300000 -- 只查看执行时间超过5分钟的查询
ORDER BY [Avg Duration] DESC;

该脚本可以查询执行时间超过5分钟的查询，并且按照平均持续时间排序。

使用此脚本，我们可以分析慢查询的执行计划，进而优化查询，例如增加或优化索引等。