High Plan Account in SQL Server 2008 Activity Monitor

Question

I have a request MERGEto create an upsert operation in my database with data entered through my application. When I switch to a call to save my data on large transactions (> 5000), it takes a very long time (~ 20-40 seconds).

Here is my operator 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

I call this one object at a time in my .NET code.

I noticed in the activity monitor of the SQL Express 2008 activity monitor that the Count of the plan gets about 900 due to all the different permutations of the parameters with which the query is called. I also notice that if I repeat the same saving shortly after slightly different parameters, it saves much faster (~ 2 seconds).

Is this a potential performance issue and cause a long retention time?

I am using SQL Express 2008 R2.

Edit: Here's the plan:

|--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

I solved the question that I had. What made me realize this was to look at the problem these guys were facing: nHibernate Recompiles and Execution Plans

Basically, in .NET code, it did not define the DbParameter.Size property. Since different sizes of parameters cause the creation of different execution plans, each permutation of all my parameters caused the creation and caching of different plans.

, , - DbParameter.Size DDL! .

Answer 2

, 5000 , , TABLE :

CREATE TYPE paramTable AS TABLE
        (
        guid UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
        column1 INT,
        column2 VARCHAR(100)
        )

CREATE PROCEDURE prcMergeInput(@mytable paramTable)
AS 
        MERGE   TestTable AS target
        USING   (
                SELECT  column1, column2, guid
                FROM    @mytable
                ) AS source
        ON      (target.Guid = source.Guid)
        WHEN MATCHED THEN
                UPDATE  TestTable
                SET     Column1 = source.Column1,
                        Column2 = source.Column2
        WHEN NOT MATCHED THEN
                INSERT
                INTO    TestTable (Column1, Column2)
                VALUES  (source.Column1, source.Column2)
        OUTPUT  INSERTED.guid

, TestTable (guid) PRIMARY KEY.

Answer 3

, , :

SELECT  text
FROM    sys.dm_exec_cached_plans
CROSS APPLY 
        sys.dm_exec_sql_text(plan_handle)
WHERE   text LIKE ‘%SnippetFromYourQuery%’

, , , :

alter database YourDb forced

, , , . SQL Profiler .