High Plan Count in SQL Server 2008 Activity Monitor

Question

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

Here's my MERGE statement

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'm calling this on one object at a time in my .NET code.

I noticed in the Activity Monitor of SQL Express 2008 Activity Monitor that the Plan Count gets up to around 900 due to all the different parameter permutations that the query is being called with. I also notice that if I repeat the same save shortly after with slightly different parameters, it saves much quicker (~2 seconds).

Is this a potential performance issue and the cause of the long save times?

I'm 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

Instead of doing this in a loop 5000 times, better wrap is into a stored procedure that takes a TABLE of values as an input, and perform a bulk update:

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

Also make sure you have an index on TestTable (guid) or it is declared as a PRIMARY KEY.

Answer 2

To verify the source of the cached plans, you can query the dynamic management view that contains all cached plans:

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

And although it looks like you are properly parametrizing your query, you can test it by turning on forced parameterization:

alter database YourDb forced

If this reduces the runtime, you should investigate which part of your query contains hard-coded values as opposed to parameters. SQL Profiler should make that easy.

Answer 3

I've solved the issue I was having. What led me to figure it out was to look at a problem these guys were having: nHibernate Recompiles and Execution Plans

Basically, in the .NET code, it was not defining the DbParameter.Size Property. Since different parameter sizes cause different execution plans to be created, each permutation of all of my parameters were causing different plans to be created and cached.

All I had to do was set the DbParameter.Size to the size of each respective column from my DDL scripts! Wow.