我有几个查询在SSMS中与在我的.Net应用程序中运行时的行为不同. SSMS在一秒钟内执行正常. .Net呼叫在120秒后超时(连接默认超时).

我做了一个sql跟踪(并收集了一切)我已经看到连接选项是相同的(并匹配sql Server的默认值).然而,SHOWPLAN All在行估计中显示出巨大的差异,因此工作版本会使用积极的表Spool,因为失败的调用没有.

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

在VS2008调试会话中,Cassini下执行失败.成功归功于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'