SQL Server查询计划差异

当从参数化查询更改为非参数化查询时,我很难理解SQL Server中语句的估计查询计划的行为。

我有以下疑问:

DECLARE @p0 UniqueIdentifier = '1fc66e37-6eaf-4032-b374-e7b60fbd25ea'
SELECT [t5].[value2] AS [Date], [t5].[value] AS [New]
FROM (
    SELECT COUNT(*) AS [value], [t4].[value] AS [value2]
    FROM (
        SELECT CONVERT(DATE, [t3].[ServerTime]) AS [value]
        FROM (
            SELECT [t0].[CookieID]
            FROM [dbo].[Usage] AS [t0]
            WHERE ([t0].[CookieID] IS NOT NULL) AND ([t0].[ProductID] = @p0)
            GROUP BY [t0].[CookieID]
            ) AS [t1]
        OUTER APPLY (
            SELECT TOP (1) [t2].[ServerTime]
            FROM [dbo].[Usage] AS [t2]
            WHERE ((([t1].[CookieID] IS NULL) AND ([t2].[CookieID] IS NULL)) 
            OR (([t1].[CookieID] IS NOT NULL) AND ([t2].[CookieID] IS NOT NULL) 
            AND ([t1].[CookieID] = [t2].[CookieID]))) 
            AND ([t2].[CookieID] IS NOT NULL)          
            AND ([t2].[ProductID] = @p0)
            ORDER BY [t2].[ServerTime]
            ) AS [t3]
        ) AS [t4]
    GROUP BY [t4].[value]
    ) AS [t5]
ORDER BY [t5].[value2]

此查询由Linq2SQL表达式生成,并从LINQPad中提取。这将生成一个很好的查询计划(据我所知),并在大约10秒内在数据库上执行。但是,如果我用精确的值替换参数的两个用法,也就是用'='1fc66e37-6eaf-4032-b374-e7b60fbd25ea'替换两个'=@p0'部分,我会得到一个不同的估计查询计划,查询现在运行的时间要长得多(超过60秒,还没有看透)。

我真正的问题是,我可以忍受10秒的执行时间(至少有一段时间),但我不能忍受60秒以上的执行时间。我的查询(如上所示)将由Linq2SQL生成,因此它将在数据库上按如下方式执行

exec sp_executesql N'
        ...
        WHERE ([t0].[CookieID] IS NOT NULL) AND ([t0].[ProductID] = @p0)
        ...
        AND ([t2].[ProductID] = @p0)
        ...
       ',N'@p0 uniqueidentifier',@p0='1FC66E37-6EAF-4032-B374-E7B60FBD25EA'

这会产生同样糟糕的执行时间(我认为这是非常奇怪的,因为这似乎是在使用参数化查询)。

我不是在寻找关于创建哪些索引之类的建议,我只是想了解为什么在三个看似相似的查询上查询计划和执行如此不同。

编辑: Heinz )使用不同的GUID here

希望它能帮助你帮助我:)