主页 / dba / 问题 / 250649
Accepted
jitbit
Asked: 2019-10-10 02:23:54 +0800 CST

SQL Server 每周恢复一次低效计划（聚集索引扫描）

  • 772

我有一个非常简单的查询：

INSERT INTO #tmptbl
SELECT TOP 50 CommentID --this is primary key
FROM Comments WITH(NOLOCK)
WHERE UserID=@UserID
ORDER BY CommentID DESC

针对此表：

CREATE TABLE [dbo].[Comments] (
    [CommentID] int IDENTITY (1, 1) NOT NULL PRIMARY KEY,
    [CommentDate] datetime NOT NULL DEFAULT (getdate()),
    [UserID] int NULL ,
    [Body] nvarchar(max) NOT NULL,
--a couple of other int and bit cols, no indexes on them
)

我在UserID列上有一个简单的索引（不包括列），一切都运行良好且超快。

但是每 5-8 天我就会在应用程序的那部分看到超时。所以我去调查查询存储，我看到服务器停止使用我的索引并恢复到愚蠢的“集群扫描”。删除临时表没有帮助。

为什么，天哪，为什么？？？

为了解决这个问题——我为这个特定的查询重置了计划缓存（只是为了记录我是怎么做的）

select plan_handle FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt
where text like '%SELECT TOP 50 CommentID FROM hdComments%'
--blahblahblah skipped some code
DBCC FREEPROCCACHE (@plan_handle)

然后再次开始正常工作。

执行计划：慢 快

我已经挠头好几天了……有什么想法吗？

sql-server index

3 个回答

  1. Best Answer

    您在 UserID 上的索引不是该查询的最佳索引。它让优化器可以选择使用它并需要按 CommentID 进行额外排序或扫描表（向后）以获取已按 commentID 排序并由 where 子句和 top 运算符动态过滤的行。虽然聚簇的PK列包含在每个非聚簇的列中，但它只是一个指针，不能用于排序。

    对于您描述的关键查询，避免它的最佳方法是提供最佳索引，因此优化器更有可能每次都选择它。根据您提供的信息，您的索引应该是 (UserID, CommentID DESC) 上的复合非聚集索引这将允许直接访问用户行，并且可以按照 CommentID 的顺序扫描前 50 行，使其成为最佳选择，无论统计数据和选择性如何。

    SQL Server 很聪明，可以实现它。试一试... HTH

    • 4

  2. 如果 SQLRaptor 的答案不适合您，您可以尝试的另一件极端的事情是使用查询提示 FORCESEEK。这实质上迫使优化器始终使用执行索引查找而不是索引扫描的计划（如果可能）。

    它不是首选的一个原因是因为它限制了优化器可以选择使用的查询计划的数量，并且在某些情况下会通过说没有可用于该查询提示的计划来出错。通常，查询提示更像是万不得已的创可贴修复（特定边缘情况除外），但可以说不如运行 DBCC FREEPROCCACHE 命令那么激烈，并且可能不如总是使用 KumarHarsh 的回答中的 OPTION RECOMPILE 提示重新编译查询那么激烈。

    （这最终成为我最近遇到的一个特定场景的最佳解决方案，该场景有一个包含数十亿条记录的表，优化器总是尝试使用聚簇索引扫描，但有一个非聚簇索引更适用于查询和实际上总是比寻找更快。）

    有关详细信息，请参阅 Microsoft 文档的 FORCESEEK 部分：https ://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15

    • 4

  3. 我之前在一个设计糟糕的系统上看到过这种情况，其中主键是一个复合键，其中一列在创建之日就非常频繁地更新。这导致在计划索引重建之前的晚上（这是一个 24/7 系统）小时索引碎片。那时 SQL 停止使用最佳查询并显着减慢速度，即使使用实际 PK 的查询仍然更快。当然，重建索引后，SQL 会回到正常的查询计划。

    解决此问题的方法是为查询提供索引提示。您可以通过以下方式做到这一点：

    select …..
from tableA a with(index(pk_tableA)) -- any table index allowed
inner join tableB b on b.Id = a.BId
where etc etc

    感觉这不是一个好的解决方案——理想情况下我们会重新设计表格及其使用方式——但预算。

    注意（正如乔纳森在评论中提醒我的那样）索引是在线重建的，而不是离线重建的。这需要在重建命令中指定。

    以下来自MSSqlTips.com

    ALTER INDEX [IX_Test] ON [dbo].[Test] REBUILD WITH (ONLINE = ON);

    此选项在以下情况下不可用：

    • 索引是一个 XML 索引
    • 该索引是空间索引
    • 索引在本地临时表上
    • 索引是聚簇的并且表包含一个 LOB 数据库列
    • 索引未聚簇且索引本身包含一个 LOB 数据库列

    此外，正如 Denis Rubashkin 所提到的，在线选项仅在 SQL Server 企业版中提供。

    • 0

相关问题