主页 / dba / 问题 / 1714
Accepted
Gaius
Asked: 2011-03-13 03:31:21 +0800 CST

SQL Server 性能突然下降

  • 772

我有一个 SQL Server 2005，它最近变得不可预测，我正在摸索为什么。在几秒钟内执行的查询正在更改计划并花费几分钟（在全表扫描或索引假脱机中花费时间）。现在第一件也是最明显的事情是，统计数据已经过时导致优化器感到困惑，但我相信情况并非如此 - 首先因为基础数据没有显着变化（例如，在一年的数据之上添加一天的数据已经在表格中），其次是因为自动创建统计信息和自动更新统计信息都是正确的。然而，优化器变得困惑；在 Tuning Advisor 中运行 SQL 给了我很多多列CREATE STATISTICS语句，这些语句似乎可以修复它（直到下一个 SQL 错误行为）。

我可以用来解决根本原因的策略有什么想法吗？为什么“正常”统计数据还不够？

sql-server-2005 performance

3 个回答

  1. 来自MSDN：

    "在升序或降序键列上发生插入操作 关于升序或降序键列 的统计信息，例如 IDENTITY 或实时时间戳列，可能需要比查询优化器执行的更频繁的统计信息更新。插入操作将新值附加到升序或降序列. 添加的行数可能太少而无法触发统计信息更新。如果统计信息不是最新的并且查询从最近添加的行中选择，则当前统计信息将没有这些新值的基数估计。这可以导致基数估计不准确和查询性能下降。

    例如，如果统计信息未更新以包括最近销售订单日期的基数估计，则从最近的销售订单日期中选择的查询将具有不准确的基数估计。

    维护操作之后 考虑在执行更改数据分布的维护过程（例如截断表或执行大部分行的批量插入）之后更新统计信息。这可以避免在查询等待自动统计更新时查询处理的未来延迟。”

    您可能会不时在系统上使用“EXEC sp_updatestats”（计划一段时间）或对所有对象使用函数 STATS_DATE 并查看上次实际更新其统计信息的时间，如果从那时起时间过长，请使用 UPDATE该特定对象的统计信息。根据我的经验，即使启用了自动统计信息，我们仍然被迫不时更新统计信息，因为插入操作不会触发自动更新。

    添加我的个人代码（用于为统计更新构建动态语句的每周工作）：

    select distinct
        'update statistics [' + stats.SchemaName + '].[' + stats.TableName + ']'
            + case when stats.RowCnt > 50000 then ' with sample 30 percent;'
            else 
                ';' end
        as UpdateStatement
    from (
        select
            ss.name SchemaName,
            so.name TableName,
            so.id ObjectId,
            st.name AS StatsName, 
            STATS_DATE(st.object_id, st.stats_id) AS LastStatisticsUpdateDate
            , si.RowModCtr
            , (select case si2.RowCnt when 0 then 1 else si2.RowCnt end from sysindexes si2 where si2.id = si.id and si2.indid in (0,1)) RowCnt
        from sys.stats st
            join sysindexes si on st.object_id = si.id and st.stats_id = si.indid
            join sysobjects so on so.id = si.id and so.xtype = 'U' --user table
            join sys.schemas ss on ss.schema_id = so.uid
    ) stats
    where cast(stats.RowModCtr as float)/cast(stats.RowCnt as FLOAT)*100 >= 10 --more than 10% of the rows have changed
    or ( --update statistics that were not updated for more than 3 months (and rows no > 0)
        datediff(month, stats.LastStatisticsUpdateDate, getdate()) >= 3
        and stats.RowCnt > 0
    )

    在这里，我得到了所有超过 3 个月没有更新统计信息的对象，或者自上次更新统计信息以来，它有超过 10% 的行发生了变化。

    • 8
  2. Best Answer

    如果您的首要等待是 SOS_SCHEDULER_YIELD，那么您似乎对 CPU 有一些压力。但这可能是其他原因的结果，例如您的设计不再足以满足您的查询。我知道您说过您只添加了一天的数据，但您可能已经达到了临界点。

    您的查询是如何发出的？是动态SQL吗？你在使用存储过程吗？你在使用 sp_executesql 吗？您是否有可能遇到参数嗅探的情况？你的数据库设计是什么样的？PK和FK的关系是什么？

    你有一个好的计划的例子吗？如果您能够确定一个好的计划，您可以使用计划指南来强制查询以特定方式执行。

    你能举一个好的计划失败的例子吗？

    最后，从 Adam Machanic 那里获取一份 sp_whoIsActive ( http://whoisactive.com/ ) 的副本，并使用它来确定有关正在运行的查询的更多信息。如果您希望能够从 sp_whoIsActive 捕获输出，请转到此处http://www.littlekendra.com/2011/02/01/whoisactive/

    • 8

  3. 我的猜测是您的一个或多个表变得足够大，以至于它们没有达到帮助将当前统计信息标记为过时所需的 20% 的更改，以便自动更新统计信息将启动，但仍有足够的更新（或插入) 更新统计数据会有很大帮助。从 SQL 2000 升级到 SQL 2008 后，我最近在特定环境中发现了同样的情况。

    除了上述答案中提到的其他网站外，我建议您查看以下在线资源。

    1) Red-Gate 有许多免费电子书可供下载，包括 Holger Schmeling 的“SQL Server Statistics”，您可以在其中找到以下引述：

    http://www.red-gate.com/our-company/about/book-store/

    “具有超过 500 行的表必须更改至少 20% 的列数据才能使任何链接的统计信息无效”

    2) SQL Sentry 有一个免费的计划资源管理器工具，可以帮助跟踪 SQL 计划中的问题，例如与查询中给定表的实际行数相比，估计的行数过多或过少。只需从 SSMS 中保存实际的执行计划，然后使用 Plan Explorer 浏览计划的不同部分。并不是说这些信息在使用图形执行计划的 SSMS 中不可用，而是 SQL Sentry 的工具确实使它更容易查看。

    http://www.sqlsentry.com/plan-explorer/sql-server-query-view.asp

    3) 使用 STATS_DATE() 查看您最感兴趣的查询中的表的统计更新日期，您可以使用以下讨论中的查询找到一个快速查询来获取最旧的统计信息。

    http://blog.sqlauthority.com/2010/01/25/sql-server-find-statistics-update-date-update-statistics/

    我希望这有帮助！

    我想你会特别喜欢 Red-Gate 的书！

    -杰夫

    • 3

相关问题