sql-server - 运行了几个月的查询现在超时

Question

我有一个从 Delphi 应用程序运行的查询，在完美运行了几个月后，上周突然开始超时。更重要的是，当它运行时，它会降低服务器的速度，让其他用户认为系统已经崩溃从 Management Studio 运行，我在旋转超过 5 分钟后停止查询服务器 ID SqlExpress 2008 R2

现在有问题的查询

SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 365))

有趣的是，当我只更改天数时，所需的时间和行返回。事件监视器似乎没有显示更多的查询正在运行

SELECT     *
FROM        SignelOutilsListeJobs_View4 -- 00.00.02 38882 ROWS

SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 600)) -- 00.00.02 16217 ROWS


SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 500)) -- 00.00.02 13013 ROWS


SELECT     *
FROM        SignelOutilsListeJobs_View4
WHERE     (CreatedDate > (GETDATE() - 200)) -- 00.00.12  4118 ROWS

所以我想知道这里发生了什么？有什么想法吗？

谢谢

Answer 1

来自GETDATE (Transact-SQL) :

Using SWITCHOFFSET with the function GETDATE() can cause the query to run slowly because the query optimizer is unable to obtain accurate cardinality estimates for the GETDATE value. We recommend that you precompute the GETDATE value and then specify that value in the query as shown in the following example. In addition, use the OPTION (RECOMPILE) query hint to force the query optimizer to recompile a query plan the next time the same query is executed. The optimizer will then have accurate cardinality estimates for GETDATE() and will produce a more efficient query plan.

换句话说，您可以尝试按如下方式编辑查询:

SELECT *
FROM SignelOutilsListeJobs_View4
WHERE CreatedDate > (GETDATE() - 200) OPTION (RECOMPILE)

<小时/>

作为上述方法的替代方案，您可以考虑在 View 上创建唯一聚集索引:

CREATE UNIQUE CLUSTERED INDEX SignelOutilsListeJobs_unique_index1
ON SignelOutilsListeJobs_View4 (CreatedDate, <some unique key>)

来自 Microsoft TechNet:

• Designing Indexed Views
• Improving Performance with SQL Server 2008 Indexed Views
• Optimizing Queries That Access Correlated datetime Columns