sql - .Net SqlCommand.ExecuteNonQuery 中的查询超时，适用于 SQL Server Management Studio

Question

更新:问题已解决，并保持解决。 如果您想查看该站点的运行情况，请访问 Tweet08

我有几个查询在 SSMS 中的行为与在我的 .Net 应用程序中运行时的行为不同。 SSMS 在不到一秒钟的时间内执行得很好。 .Net 调用在 120 秒后超时(连接默认超时)。

我做了一个 SQL 跟踪(并收集了所有内容)我看到连接选项是相同的(并且与 SQL Server 的默认值匹配)。然而，SHOWPLAN All 在行估计中显示出巨大的差异，因此工作版本执行了积极的 Table Spool，而失败的调用则没有。

在 SSMS 中，临时变量的数据类型是基于 .Net 中生成的 SQL 参数，因此它们是相同的。

故障在 Cassini 下在 VS2008 调试 session 中执行。成功是在 SSMS 2008 下。两者都在同一台机器上的同一网络上针对同一目标服务器运行。

在 SSMS 中查询:

DECLARE @ContentTableID0 TINYINT
DECLARE @EntryTag1 INT
DECLARE @ContentTableID2 TINYINT
DECLARE @FieldCheckId3 INT
DECLARE @FieldCheckValue3 VARCHAR(128)
DECLARE @FieldCheckId5 INT
DECLARE @FieldCheckValue5 VARCHAR(128)
DECLARE @FieldCheckId7 INT 
DECLARE @FieldCheckValue7 VARCHAR(128)
SET @ContentTableID0= 3
SET @EntryTag1= 8
SET @ContentTableID2= 2
SET @FieldCheckId3= 14
SET @FieldCheckValue3= 'igor'
SET @FieldCheckId5= 33
SET @FieldCheckValue5= 'a'
SET @FieldCheckId7= 34
SET @FieldCheckValue7= 'a'

SELECT COUNT_BIG(*)
FROM dbo.ContentEntry AS mainCE
WHERE GetUTCDate() BETWEEN mainCE.CreatedOn AND mainCE.ExpiredOn
AND (mainCE.ContentTableID=@ContentTableID0)
AND ( EXISTS (SELECT *
              FROM dbo.ContentEntryLabel
              WHERE ContentEntryID = mainCE.ID
              AND GetUTCDate() BETWEEN CreatedOn AND ExpiredOn
              AND LabelFacetID = @EntryTag1))
      AND (mainCE.OwnerGUID IN (SELECT TOP 1 Name
                                FROM dbo.ContentEntry AS innerCE1
                                WHERE GetUTCDate() BETWEEN innerCE1.CreatedOn AND innerCE1.ExpiredOn
                                AND (innerCE1.ContentTableID=@ContentTableID2
                                     AND EXISTS (SELECT *
                                                 FROM dbo.ContentEntryField
                                                 WHERE ContentEntryID = innerCE1.ID
                                                 AND (ContentTableFieldID = @FieldCheckId3
                                                      AND DictionaryValueID IN (SELECT dv.ID
                                                                                FROM dbo.DictionaryValue AS dv
                                                                                WHERE dv.Word LIKE '%' + @FieldCheckValue3 + '%'))
                                                )
                                    )
                               )
           OR EXISTS (SELECT *
                      FROM dbo.ContentEntryField
                      WHERE ContentEntryID = mainCE.ID
                      AND (   (ContentTableFieldID = @FieldCheckId5
                               AND DictionaryValueID IN (SELECT dv.ID
                                                         FROM dbo.DictionaryValue AS dv
                                                         WHERE dv.Word LIKE '%' + @FieldCheckValue5 + '%')
                              )
                           OR (ContentTableFieldID = @FieldCheckId7
                               AND DictionaryValueID IN (SELECT dv.ID
                                                         FROM dbo.DictionaryValue AS dv
                                                         WHERE dv.Word LIKE '%' + @FieldCheckValue7 + '%')
                               )
                          )
                     )
          )

Trace 的 .Net 调用版本(添加了一些格式):

exec sp_executesql N'SELECT COUNT_BIG(*) ...'
,N'@ContentTableID0 tinyint
,@EntryTag1 int
,@ContentTableID2 tinyint
,@FieldCheckId3 int
,@FieldCheckValue3 varchar(128)
,@FieldCheckId5 int
,@FieldCheckValue5 varchar(128)
,@FieldCheckId7 int
,@FieldCheckValue7 varchar(128)'
,@ContentTableID0=3
,@EntryTag1=8
,@ContentTableID2=2
,@FieldCheckId3=14
,@FieldCheckValue3='igor'
,@FieldCheckId5=33
,@FieldCheckValue5='a'
,@FieldCheckId7=34
,@FieldCheckValue7='a'

Answer 1

这不是您的索引。

这是参数嗅探，因为它通常发生在参数化存储过程中。它并不广为人知，即使在了解参数嗅探的人中也是如此，但是当您通过 sp_executesql 使用参数时，它也可能发生。

您会注意到您在 SSMS 中测试的版本和探查器显示的版本不同，因为探查器版本显示您的 .Net 应用程序正在通过 sp_executesql 执行它。如果您提取并执行实际为您的应用程序运行的完整 sql 文本，那么我相信您会在相同的查询计划中看到相同的性能问题。

仅供引用:查询计划不同是参数嗅探的关键指标。

修复:假设它在 SQL Server 2005 或 2008 上执行，修复此问题的最简单方法是添加子句“OPTION (RECOMPILE)”作为 SELECT 语句的最后一行。预先警告，您可能需要执行两次它才能工作，而且它在 SQL Server 2005 上并不总是有效。如果发生这种情况，那么您可以采取其他步骤，但它们涉及更多一些。

您可以尝试的一件事是检查是否已为您的数据库打开“强制参数化”(它应该在 SSMS 数据库属性中，在选项页面下)。要关闭强制参数化，请执行以下命令:

    ALTER DATABASE [yourDB] SET  PARAMETERIZATION SIMPLE