SQL Server查询突然变慢

Question

我遇到一个SQL数据库查询问题，该查询突然(但大约每三周一次)变慢。

安装程序如下:

Windows Server 2008(非R2)64位，8 GB RAM

SQL Server Express 2008 R2的

数据库的大小为6 GB(mdf文件大小)

查询主要从中选择的表(Orders)大约有24000条记录，其他五个连接的表很小(100条或更少的记录)

表Orders的varbinary(MAX)列Report包含二进制数据(PDF文档)，平均大小约为200至300 kB(但有时最多2 MB)。在这24000个订单中，有超过90％的列已填满，对于其他列，它是NULL，即6 GB数据库大小中的90％以上是二进制数据。

有问题的查询具有以下结构:

SELECT TOP (30) [Project2].[OrderID] AS [OrderID]
                -- around 20 columns more
FROM ( SELECT [Project2].[OrderID] AS [OrderID],
              -- around 20 columns more
              row_number() OVER (ORDER BY [Project2].[OrderID] ASC) AS [row_number]
       FROM ( SELECT [Filter1].[OrderID] AS [OrderID]
              -- around 20 columns more
              FROM ( SELECT [Extent1].[OrderID] AS [OrderID]
                     -- around 20 columns more
                     FROM [dbo].[Orders] AS [Extent1]
                     INNER JOIN -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     WHERE ([Extent1].[Status] IS NOT NULL) 
                       AND (4 = CAST( [Extent1].[Status] AS int))
                       AND ([Extent1].[SomeDateTime] IS NULL)
                       AND ([Extent1].[Report] IS NULL)
                   ) AS [Filter1]
              OUTER APPLY  (SELECT TOP (1) [Project1].[C1] AS [C1]
                            FROM ( SELECT CAST( [Extent7].[CreationDateTime] AS datetime2) AS [C1],
                                                [Extent7].[CreationDateTime] AS [CreationDateTime]
                                   FROM [dbo].[OtherTable] AS [Extent7]
                                   WHERE [Filter1].[OrderID] = [Extent7].[OrderID]
                                 ) AS [Project1]
                             ORDER BY [Project1].[CreationDateTime] DESC
             ) AS [Limit1]
       )  AS [Project2]
)  AS [Project2]
WHERE [Project2].[row_number] > 0
ORDER BY [Project2].[OrderID] ASC

它是由Entity Framework从LINQ-to-Entities查询生成的。该查询以几种变体形式出现，仅在第一个 WHERE子句中有所不同:

五个变体

WHERE ([Extent1].[Status] IS NOT NULL) 
  AND (X = CAST( [Extent1].[Status] AS int))

X可以在0和4之间。这些查询从来都不是问题。

和两个变体(*)

WHERE ([Extent1].[Status] IS NOT NULL) 
  AND (4 = CAST( [Extent1].[Status] AS int))
  AND ([Extent1].[SomeDateTime] IS NULL)
  AND ([Extent1].[Report] IS NULL)

或最后一行中的... IS NOT NULL...。我只有以下两个查询才有下面描述的问题。

“现象”是:

这两个查询(*)每天(每周5天)运行100到200次。他们的表演时间不到一秒钟，持续了大约三个星期。

三个星期后，两个查询突然都需要超过60秒的时间。 (实际上，此时间随着数据库大小的增加而增加。)由于超时，用户会收到错误消息(在网页上；它是一个Web应用程序)。 (默认情况下，Entity Framework等待结果的时间不会超过30秒。)

如果我将查询粘贴到SSMS中并让查询运行(等待60秒)，则结果将成功返回，并且下一个相同的查询将在不到一秒钟的时间内再次运行。

大约三周后，同样的情况再次发生(但是查询运行的时间将是65或70秒)

另一个观察结果:

如果我在查询正常执行时重新启动SQL Server服务进程，则该进程的内存使用会缓慢增加。它大约在一周内逐步达到约1.5 GB(任务管理器中的私有(private)工作集)的限制。

如果我在查询突然变慢时重新启动SQL Server服务过程并再次触发查询，我可以在任务管理器中看到该服务在几秒钟内加载了将近1 GB。

我以某种方式怀疑整个问题与Express版本的内存限制(1 GB)和 varbinary(MAX)列有关，尽管我只是在 WHERE子句中使用它来检查列值是 NULL还是 NULL而不是。 Report列本身不是所选列之一。

由于我明年会遇到Express版的限制(10 GB mdf文件大小)，因此无论如何我都在考虑更改:

要么将二进制列移动到另一个表，然后通过FILESTREAM将内容存储在外部，请继续使用Express Edition

使用不受Express限制的“大型” SQL Server版本之一，将二进制列保留在Orders表

中

都做

问题:查询突然变慢的原因可能是什么？我正在计划的更改之一可以解决问题，还是有其他解决方案？

编辑

在下面的评论中，按照bhamby的技巧，在再次运行查询之前，我已经在SSMS中设置了 SET STATISTICS TIME ON。当查询再次变慢时，我会获得较高的 SQL Server parse and compile time值，即: CPU time = 27,3 sec和 Elapsed time = 81,9 sec。查询的执行时间仅为CPU时间= 0.06秒，耗时= 2.8秒。此后第二次运行查询将为SQL Server解析和编译时间提供0,06 sec的CPU时间和0,08的耗时。

Answer 1

这似乎很浪费

SELECT TOP (1) [Project1].[C1] AS [C1]
FROM ( SELECT CAST( [Extent7].[CreationDateTime] AS datetime2) AS [C1],
                    [Extent7].[CreationDateTime] AS [CreationDateTime]
         FROM [dbo].[OtherTable] AS [Extent7]
        WHERE [Filter1].[OrderID] = [Extent7].[OrderID]
     ) AS [Project1]
ORDER BY [Project1].[CreationDateTime] DESC

是

SELECT max( CAST( [Extent7].[CreationDateTime] AS datetime2) ) AS [C1]
  FROM [dbo].[OtherTable] AS [Extent7]
 WHERE [Filter1].[OrderID] = [Extent7].[OrderID]

为什么不将日期存储为日期时间？

我不喜欢那件外衣
我将创建一次运行的#temp并加入其中
确保并将[OrderID]声明为PK

SELECT [Extent7].[OrderID], max( CAST( [Extent7].[CreationDateTime] AS datetime2) ) AS [C1]
FROM [dbo].[OtherTable] AS [Extent7]
GROUP BY [Extent7].[OrderID]

您可能会进行循环加入

接下来，我将其放在＃temp2中，以确保它只运行一次
再次确保将OrderID声明为PK

SELECT [Extent1].[OrderID] AS [OrderID]
                     -- around 20 columns more
                     FROM [dbo].[Orders] AS [Extent1]
                     INNER JOIN -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     LEFT OUTER JOIN  -- small table
                     WHERE ([Extent1].[Status] IS NOT NULL) 
                       AND (4 = CAST( [Extent1].[Status] AS int))
                       AND ([Extent1].[SomeDateTime] IS NULL)
                       AND ([Extent1].[Report] IS NULL)

如果Order仅24,000行，那么愚蠢的事情将使您进行查询的时间超过几秒钟。