Server 2008 R2 DataCenter中查询持续时间过长

Question

我们有一个查询和这是一个实际的执行计划。

​

如您所见，聚集索引搜索占99%。此外，它还查找主键(类型为、int、)。

表来源有275 000行。

表AuthorSource有2 275 000行。

不使用分区和压缩。

问题是第一次执行需要25-40秒。但是第二次跑需要1-2秒。

此外，我们在此服务器上运行了复制、队列读取器、日志读取器代理。

内存数量: 4GB

Server使用: 3.7GB

我们认为，sql在第一次执行之后缓存查询一段时间，这就是第二次运行只需1-2秒的原因。

但是，不管缓存和其他原因，很奇怪的是，主键索引查找查询需要20-40秒。

这个问题是重复的。我们提供给查询的任何不同的参数-我们得到相同的结果:非常长的第一次查询和快速的第二次和以下。

可能是一些额外的设置或资源调控器的能力，我们必须使用？

        exec sp_executesql N'
SELECT [Project1].[C1] AS         [C1]
FROM   ( SELECT CAST(1 AS bit) AS X
       ) AS [SingleRowTable1]
       LEFT OUTER JOIN
              (SELECT [GroupBy1].[A1] AS                        [C1]
              FROM    ( SELECT COUNT(CAST(1 AS bit)) AS         [A1]
                      FROM    (SELECT [Extent1].[Mention_ID]                       AS [Mention_ID]                      ,
                                      [Extent1].[Theme_ID]                         AS [Theme_ID]                        ,
                                      [Extent1].[Mention_Weight]                   AS [Mention_Weight]                  ,
                                      [Extent1].[AuthorSource_ID]                  AS [AuthorSource_ID1]                ,
                                      [Extent1].[Mention_CreationDate]             AS [Mention_CreationDate]            ,
                                      [Extent1].[Mention_DeletedMark]              AS [Mention_DeletedMark]             ,
                                      [Extent1].[Mention_AuthorTags]               AS [Mention_AuthorTags]              ,
                                      [Extent1].[Mention_Tonality]                 AS [Mention_Tonality]                ,
                                      [Extent1].[Mention_Comment]                  AS [Mention_Comment]                 ,
                                      [Extent1].[Mention_AdditionDate]             AS [Mention_AdditionDate]            ,
                                      [Extent1].[UserToAnswer_ID]                  AS [UserToAnswer_ID]                 ,
                                      [Extent1].[GeoName_ID]                       AS [GeoName_ID]                      ,
                                      [Extent1].[Geo_ID]                           AS [Geo_ID]                          ,
                                      [Extent1].[Mention_PermaLinkHash]            AS [Mention_PermaLinkHash]           ,
                                      [Extent1].[Mention_IsFiltredByAuthor]        AS [Mention_IsFiltredByAuthor]       ,
                                      [Extent1].[Mention_IsFiltredByGeo]           AS [Mention_IsFiltredByGeo]          ,
                                      [Extent1].[Mention_IsFiltredBySource]        AS [Mention_IsFiltredBySource]       ,
                                      [Extent1].[Mention_IsFiltredBySourceType]    AS [Mention_IsFiltredBySourceType]   ,
                                      [Extent1].[GengineLog_InstanceId]            AS [GengineLog_InstanceId]           ,
                                      [Extent1].[Mention_PermaLinkBinaryHash]      AS [Mention_PermaLinkBinaryHash]     ,
                                      [Extent1].[Mention_APIType]                  AS [Mention_APIType]                 ,
                                      [Extent1].[Mention_IsFilteredByAuthorSource] AS [Mention_IsFilteredByAuthorSource],
                                      [Extent1].[Mention_IsFavorite]               AS [Mention_IsFavorite]              ,
                                      [Extent1].[Mention_SpamType]                 AS [Mention_SpamType]                ,
                                      [Extent1].[MentionContent_ID]                AS [MentionContent_ID]               ,
                                      [Extent2].[AuthorSource_ID]                  AS [AuthorSource_ID2]                ,
                                      [Extent2].[Author_ID]                        AS [Author_ID]                       ,
                                      [Extent2].[Source_ID]                        AS [Source_ID]                       ,
                                      [Extent2].[Author_Nick]                      AS [Author_Nick]                     ,
                                      [Extent2].[Author_UrlBinaryHash]             AS [Author_UrlBinaryHash]            ,
                                      [Extent2].[AuthorSource_Type]                AS [AuthorSource_Type]               ,
                                      [Extent2].[Author_Url]                       AS [Author_Url]                      ,
                                      [Extent2].[AuthorSource_Description]         AS [AuthorSource_Description]        ,
                                      [Extent2].[AuthorSource_Gender]              AS [AuthorSource_Gender]
                              FROM    [dbo].[Mention]                              AS [Extent1]
                                      LEFT OUTER JOIN [dbo].[AuthorSource]         AS [Extent2]
                                      ON      [Extent1].[AuthorSource_ID] = [Extent2].[AuthorSource_ID]
                              WHERE   (
                                              [Extent1].[Mention_DeletedMark] <> CAST(1 AS bit)
                                      )
                              AND
                                      (
                                              [Extent1].[Mention_IsFiltredByAuthor] <> CAST(1 AS bit)
                                      )
                              AND
                                      (
                                              [Extent1].[Mention_IsFilteredByAuthorSource] <> CAST(1 AS bit)
                                      )
                              AND
                                      (
                                              [Extent1].[Mention_IsFiltredByGeo] <> CAST(1 AS bit)
                                      )
                              AND
                                      (
                                              [Extent1].[Mention_IsFiltredBySource] <> CAST(1 AS bit)
                                      )
                              AND
                                      (
                                              [Extent1].[Mention_IsFiltredBySourceType] <> CAST(1 AS bit)
                                      )
                              )                              AS [Filter1]
                              LEFT OUTER JOIN [dbo].[Source] AS [Extent3]
                              ON      [Filter1].[Source_ID] = [Extent3].[Source_ID]
                      WHERE   (
                                      [Filter1].[Theme_ID] = @p__linq__49557
                              )
                      AND
                              (
                                      [Extent3].[Source_Type] <> @p__linq__49558
                              )
                      ) AS [GroupaBy1]
              ) AS [Project1]
       ON     1 = 1
',N'@p__linq__49557 int,@p__linq__49558 int',@p__linq__49557=7966,@p__linq__49558=8

IndexSeeking性能信息

此外，我们使用以下简单代码在sql中手动编写了查询：

        Select COUNT(1) from Mention m inner join AuthorSource auth on m.AuthorSource_ID = auth.AuthorSource_ID inner join
    Source s on auth.Source_ID = s.Source_ID where 
    m.Mention_DeletedMark = 0 AND m.Mention_IsFilteredByAuthorSource = 0 AND m.Mention_IsFiltredByAuthor = 0 
    AND m.Mention_IsFiltredByGeo = 0 AND m.Mention_IsFiltredBySource = 0 AND m.Mention_IsFiltredBySourceType = 0
    AND m.Theme_ID = 7966
    and s.Source_Type <> 8 

执行计划和我们发布的一样。

Answer 1

在马丁氏在对问题的注释中提出建议之后，答案是理解Server如何构建执行计划和计数第一次查询运行所需的磁盘读取操作。

在我们的特殊情况下，强制的inner hash join而不是inner join给了我们预期的结果，默认情况下给出了不同的执行计划。

Answer 2

这个查询非常复杂，但是看起来您在Mention.Theme_ID上缺少了一个索引吗？

server有问题，因为它使用了大量的<>，这意味着它不能使用索引，必须获取所有内容，然后再对其进行排序。