sql - SQL Server 2008 事件监视器中的高计划计数

Question

我有一个 MERGE 查询，用于使用通过我的应用程序输入的数据在我的数据库上创建一个更新插入操作。当我调用它来保存我的大型交易数据(>5000)时，它需要非常很长的时间(~20-40 秒)。

这是我的MERGE语句

MERGE TestTable AS target USING (SELECT @Guid) AS source (target.Guid = source.Guid)
WHEN MATCHED THEN
  UPDATE TestTable SET Column1 = @Column1, Column2 = @Column2 WHERE Guid = @Guid
WHEN NOT MATCHED THEN
  INSERT INTO TestTable (Column1, Column2) VALUES (@Column1, @Column2)
OUTPUT $action

我在我的 .NET 代码中一次在一个对象上调用它。

我在 SQL Express 2008 事件监视器的事件监视器中注意到，由于调用查询时使用的所有不同参数排列，计划计数高达 900 左右。我还注意到，如果我在使用略有不同的参数后不久重复相同的保存，保存速度会很多(~2 秒)。

这是潜在的性能问题和保存时间过长的原因吗？

我正在使用 SQL Express 2008 R2。

编辑:这是计划:

|--Compute Scalar(DEFINE:([Expr1044]=CASE WHEN [Action1004]=(1) THEN N'UPDATE' ELSE CASE WHEN [Action1004]=(4) THEN N'INSERT' ELSE N'DELETE' END END))
     |--Assert(WHERE:(CASE WHEN NOT [Pass1238] AND [Expr1237] IS NULL THEN (0) ELSE NULL END))
          |--Nested Loops(Left Semi Join, PASSTHRU:([Action1004]=(3) OR [C:\DATABASE.MDF].[dbo].[DoorTable].[CarTable_Guid] as [target].[CarTable_Guid] IS NULL), OUTER REFERENCES:([C:\DATABASE.MDF].[dbo].[DoorTable].[CarTable_Guid]), DEFINE:([Expr1237] = [PROBE VALUE]))
               |--Clustered Index Merge(OBJECT:([C:\DATABASE.MDF].[dbo].[DoorTable].[DoorTable_PK] AS [target]), OBJECT:([C:\DATABASE.MDF].[dbo].[DoorTable].[DoorTable_FI01] AS [target]), SET:(Insert, [C:\DATABASE.MDF].[dbo].[DoorTable].[Column1] as [target].[Column1] = [Expr1005],[C:\DATABASE.MDF].[dbo].[DoorTable].[Column2] as [target].[Column2] = [Expr1006],[C:\DATABASE.MDF].[dbo].[DoorTable].[Column3] as [target].[Column3] = [Expr1007],[C:\DATABASE.MDF].[dbo].[DoorTable].[Column4] as [target].[Column4] = [Expr1008],[C:\DATABASE.MDF].[dbo].[DoorTable].[Column5] as [target].[Column5] = [Expr1009],[C:\DATABASE.MDF].[dbo].[DoorTable].[Column6] as [target].[Column6] = [Expr1010],[C:\DATABASE.MDF].[dbo].[DoorTable].[Column7] as [target].[Column7] = [Expr1011],[C:\DATABASE.MDF].[dbo].[DoorTable].[Column8] as [target].[Column8] = [Expr1012],...
               |    |--Compute Scalar(DEFINE:([Action1004]=[Action1004], [Expr1198]=[Expr1198]))
               |         |--Top(TOP EXPRESSION:((1)))
               |              |--Compute Scalar(DEFINE:([Expr1198]=CASE WHEN [Action1004] = (1) THEN CASE WHEN [Expr1099] THEN (0) ELSE (1) END ELSE [Action1004] END))
               |                   |--Compute Scalar(DEFINE:([Expr1005]=CONVERT_IMPLICIT(nvarchar(64),[@Column1],0), [Expr1006]=CONVERT_IMPLICIT(nvarchar(64),[@Column2],0), [Expr1007]=CONVERT_IMPLICIT(nvarchar(64),[@Column3],0), [Expr1008]=[@Column4], [Expr1009]=CONVERT_IMPLICIT(nvarchar(64),[@Column5],0), [Expr1010]=[@Column6], [Expr1011]=[@Column7], [Expr1012]=CONVERT_IMPLICIT(float(53),[@Column8],0),[Expr1099]=[Action1004]=(1) AND CASE WHEN [C:\DATABASE.MDF].[dbo].[DoorTable].[CarTable_Guid] as [target].[CarTable_Guid] = CONVERT_IMPLICIT(nvarchar(32),[@CarTable_Guid],0) THEN (1) ELSE (0) END))
               |                        |--Compute Scalar(DEFINE:([Action1004]=ForceOrder(CASE WHEN [TrgPrb1002] IS NOT NULL THEN (1) ELSE (4) END)))
               |                             |--Nested Loops(Left Outer Join)
               |                                  |--Constant Scan
               |                                  |--Compute Scalar(DEFINE:([TrgPrb1002]=(1)))
               |                                       |--Clustered Index Seek(OBJECT:([C:\DATABASE.MDF].[dbo].[DoorTable].[DoorTable_PK] AS [target]), SEEK:([target].[Guid]=CONVERT_IMPLICIT(nvarchar(1),[@Guid],0)) ORDERED FORWARD)
               |--Clustered Index Seek(OBJECT:([C:\DATABASE.MDF].[dbo].[CarTable].[CarTable_PK]), SEEK:([C:\DATABASE.MDF].[dbo].[CarTable].[Guid]=[C:\DATABASE.MDF].[dbo].[DoorTable].[CarTable_Guid] as [target].[CarTable_Guid]) ORDERED FORWARD)

Answer 1

我已经解决了我遇到的问题。让我弄清楚的是看这些人遇到的问题:nHibernate Recompiles and Execution Plans

基本上，在 .NET 代码中，它没有定义 DbParameter.Size 属性。由于不同的参数大小会导致创建不同的执行计划，因此我所有参数的每个排列都会导致创建和缓存不同的计划。

我所要做的就是将 DbParameter.Size 设置为我的 DDL 脚本中每个相应列的大小!哇。