SQL Server 2008:处理特定日期边界时查询性能非常低
对,这是一个奇怪的问题。SQL Server 2008:处理特定日期边界时查询性能非常低
我们有一个将数据返回给silverlight客户端的web服务。这些查询是使用实体框架针对SQL Server 2008数据库生成的。这些查询中的大多数都是基于日期范围的 - 例如,在此日期和之间拉取结果。此外,视图用于使事情变得更容易一些。
我们已经注意到,当发出一组在特定日期或之后开始的特定查询时,执行将非常缓慢。这个日期是任何一年的11月5日。如果我们的开始日期早于一天,则执行将立即执行。所以,5号到6号很慢。 4 - 6日 - 快速。
下面是EF生成SQL:
该查询会很慢(30秒)
SELECT
1 AS [C1],
[GroupBy1].[K1] AS [Name],
[GroupBy1].[A1] AS [C2]
FROM (SELECT
[Extent1].[Name] AS [K1],
SUM([Extent1].[Value]) AS [A1]
FROM (SELECT
[view_answers].[Value] AS [Value],
[view_answers].[Comment] AS [Comment],
[view_answers].[NewStockist] AS [NewStockist],
[view_answers].[NewDistPoint] AS [NewDistPoint],
[view_answers].[VoucherUsed] AS [VoucherUsed],
[view_answers].[CashTotal] AS [CashTotal],
[view_answers].[AnswerType] AS [AnswerType],
[view_answers].[StartTime] AS [StartTime],
[view_answers].[ActualEndTime] AS [ActualEndTime],
[view_answers].[Complete] AS [Complete],
[view_answers].[UserID] AS [UserID],
[view_answers].[UserName] AS [UserName],
[view_answers].[QuestionType] AS [QuestionType],
[view_answers].[ProductSKU] AS [ProductSKU],
[view_answers].[BrandID] AS [BrandID],
[view_answers].[TeamID] AS [TeamID],
[view_answers].[Name] AS [Name],
[view_answers].[Stage] AS [Stage],
[view_answers].[Question] AS [Question]
FROM [dbo].[view_answers] AS [view_answers]) AS [Extent1]
WHERE
([Extent1].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D')
AND (N'Distribution Points' = [Extent1].[QuestionType])
AND ([Extent1].[StartTime] >= '11/05/2009 00:00:00')
AND ([Extent1].[StartTime] <= '11/08/2009 00:00:00')
AND (1 = [Extent1].[Complete])
AND (2 = [Extent1].[BrandID])
AND (N'Distribution Points' = [Extent1].[QuestionType])
GROUP BY
[Extent1].[Name])
AS [GroupBy1]
这将是一个快得多
SELECT
1 AS [C1],
[GroupBy1].[K1] AS [Name],
[GroupBy1].[A1] AS [C2]
FROM (SELECT
[Extent1].[Name] AS [K1],
SUM([Extent1].[Value]) AS [A1]
FROM (SELECT
[view_answers].[Value] AS [Value],
[view_answers].[Comment] AS [Comment],
[view_answers].[NewStockist] AS [NewStockist],
[view_answers].[NewDistPoint] AS [NewDistPoint],
[view_answers].[VoucherUsed] AS [VoucherUsed],
[view_answers].[CashTotal] AS [CashTotal],
[view_answers].[AnswerType] AS [AnswerType],
[view_answers].[StartTime] AS [StartTime],
[view_answers].[ActualEndTime] AS [ActualEndTime],
[view_answers].[Complete] AS [Complete],
[view_answers].[UserID] AS [UserID],
[view_answers].[UserName] AS [UserName],
[view_answers].[QuestionType] AS [QuestionType],
[view_answers].[ProductSKU] AS [ProductSKU],
[view_answers].[BrandID] AS [BrandID],
[view_answers].[TeamID] AS [TeamID],
[view_answers].[Name] AS [Name],
[view_answers].[Stage] AS [Stage],
[view_answers].[Question] AS [Question]
FROM [dbo].[view_answers] AS [view_answers]) AS [Extent1]
WHERE
([Extent1].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D')
AND (N'Distribution Points' = [Extent1].[QuestionType])
AND ([Extent1].[StartTime] >= '11/04/2009 00:00:00')
AND ([Extent1].[StartTime] <= '11/08/2009 00:00:00')
AND (1 = [Extent1].[Complete])
AND (2 = [Extent1].[BrandID])
AND (N'Distribution Points' = [Extent1].[QuestionType])
GROUP BY
[Extent1].[Name])
AS [GroupBy1]
如果我们将开始日期设置为去年11月5日,那么执行速度将会比较缓慢,即11月4日耳朵,又快又快。看看数据库中的数据,在第五期间没有什么不寻常的。此外,看起来开始日期在5日之后的查询将运行缓慢。
我很难过!
UPDATE
感谢THR回答家伙(所以我没有直接访问其数据库远程托管)。首先,我应该清楚地说明,我对sql server的了解很深入。我构建了不同数量级的数据库,然后使用像Linq To SQL或EF这样的工具来处理它们。所以我觉得这里有一点我的深度。
关于连接 - 我查询的视图确实包含了大约6-7张表的数据。我将尝试下次在工作时抓取一些统计数据,并在此处添加更多信息。我真的不知道很多关于执行计划,或者是他们的东西我可以通过SQL Server Management Studio中
UPDATE 统计来自(受影响的3行(S))慢查询
Table 'tblProducts'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestionTypes'. Scan count 0, logical reads 1496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestions'. Scan count 0, logical reads 1496, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblSessions'. Scan count 0, logical reads 28551, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblAnswers'. Scan count 1, logical reads 1976256, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblCalls'. Scan count 1, logical reads 439, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUsers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
看从快速的查询统计
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblAnswers'. Scan count 1, logical reads 7008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblProducts'. Scan count 0, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestions'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblQuestionTypes'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblCalls'. Scan count 1, logical reads 439, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblSessions'. Scan count 1, logical reads 47, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tblUsers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
UPDATE 查询执行计划(新薄g给我)建议我在表中的一行中增加一个新索引 - questionid在答案表中。建议的sql如下:
USE [brandfourcoke]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblAnswers] ([QuestionID])
INCLUDE ([CallID],[Value])
GO
查询成本的预计改进是93%。 我应该继续做下去吗?该数据库位于实时环境中,并使用sql server 2008的更改跟踪和同步框架。因此,我总是担心数据库的更改会影响跟踪的数据并为我的客户破坏事情。将添加索引重置更改跟踪数据? 感谢您的帮助。我觉得这里是一个完整的初学者。
使用SET sTATISTICS IO ON
运行缓慢且快速的查询,查看两者之间的逻辑读取/物理读取数量是否有显着差异。
数据中很可能存在较大的偏差。例如,快速计划中的计划会执行由10行结果驱动的嵌套循环(导致10个嵌套查找),而慢速计划会突然看到前一个10行的10000行,从而导致10000次查找。虽然您的查询没有连接,但引擎可能会使用各种访问索引并将索引与集群索引连接起来。实际执行计划将始终显示发生了什么。
不知道任何额外的事情,首先猜测你可能是某个特定操作的索引使用的hitting the tipping point,或者对于给定的列有过时/错误的统计分布信息。如果您使用的是Sql 2008,可能还有其他一些问题,例如次优索引(可能是错误的,也许是错误的键排序等),您确定您没有创建过滤索引而不是传统完整索引特定列等 - 然而,为了确定,我们需要看到更多的信息(例如模式,索引,查询计划,数据分布,统计数据等)。
如果您可以发布用于上面列出的每个查询的查询计划,可以帮助我们确定您是否得到截然不同的计划。
好吧,看执行计划是告诉我,我没有索引!我想这是一件坏事。我的问题是,我应该添加一个。我正在使用SQL更改跟踪,并担心添加索引可能会重置更改跟踪,这也是一件坏事。香港专业教育学院还增加了IO信息在原岗位 感谢 – Sergio 2009-11-16 18:24:44
SQL管理工作室提示如下: 使用[brandfourcoke] GO 创建非聚集索引[] ON [DBO]。 [tblAnswers]([QuestionID]) INCLUDE([CallID],[Value]) GO – Sergio 2009-11-16 18:28:53
那么,这不是一件容易的事情来回答(即你应该添加索引)。当然,如果你希望这个特定的查询运行得更快,并且添加索引确实可以显着提高执行效率,那么你应该添加它;然而,这并不是一个简单而干涩的答案,因此您应该在自己的环境中尝试一下,以确定它是否有意义(索引的主题是一个非常广泛,非常涉及的主题)。如果没有看到您正在谈论的视图和/或现有结构,就很难给出索引的索引。 – chadhoc 2009-11-16 18:50:31
该查询是为什么使用生成的sql通常是一个可怕的想法的原因之一。至于我可以告诉大家这是,如果你有自己写它的等效查询:
SELECT 1 AS [C1],
[view_answers].[Name] AS [K1],
SUM([view_answers].[Value]) AS [C2]
FROM [view_answers]
WHERE ([view_answers].[UserID] = '16E3692F-806E-40A0-BB99-ABBBCC13060D')
AND (N'Distribution Points' = [view_answers].[QuestionType])
AND ([view_answers].[StartTime] >= '11/04/2009 00:00:00')
AND ([view_answers].[StartTime] <= '11/08/2009 00:00:00')
AND (1 = [view_answers].[Complete])
AND (2 = [view_answers].[BrandID])
AND (N'Distribution Points' = [view_answers].[QuestionType])
GROUP BY [view_answers].[Name]
有两个日期试试这个,看看你是否使用十一月5时字段是什么得到相同的结果和相同的延迟索引?
虽然你的查询很整洁,但它仍然运行缓慢。查看更多问题的更多信息 – Sergio 2009-11-16 19:21:36
为什么在[中间]选择中使用基本上只有[名称]和[值]的[view_answers]中的所有多余列? – DeckerDK 2009-11-16 17:55:00
你必须责怪那个实体框架:) – Sergio 2009-11-16 19:00:24