Sql-server – Redundant plans in cache because of missing type length

plan-cachesql serversql-server-2008-r2

Today I realized that our sql server's plan cach is stuffed with hundreads and thousands of nearly identical Compiled Plans.

The total number of plans is around 30.000 on a live system consuming around 4500 MB.
Lookin at them there are thousands that are nearly same.

Some samples:

(@ID uniqueidentifier,@GSS nvarchar(663))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(664))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(665))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(666))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID
(@ID uniqueidentifier,@GSS nvarchar(669))UPDATE [TAB1] SET [GSS]=@GSS WHERE [ID]=@ID

(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(10))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(11))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(12))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID
(@ID uniqueidentifier,@FR ntext,@uiStamp datetime,@uiUser varchar(13))UPDATE [TTR] SET [FR]=@FR, [uiStamp]=@uiStamp, [uiUser]=@uiUser WHERE [ID]=@ID

The application uses sp_executesql almost everywhere and passes values as parameters, that is why I was sure the plans are getting reused.

But now it looks like the app does not care at all for string type's length and the length is added automatically depending on the in-fact length of the values causing a different plan for each combination of string length for each passed string value. So mostly it looks like the UPDATE and INSERT statements are the issue here. I guess some developers have saved some time at this point simply leaving the size away.

The use counts of these redundant UPDATE/INSERT kinds of plans are relatively low (ofthen 1, some up to 10, only few of then up to 40 usages).

Their size ranges between 0,05 MB and 2 MB. It is hard to tell exact values and amounts for possible savings because the statements can't be sorted properly because of the changing length constants in between. However I think an average plan size here is 0,17 MB in around 15000 Update/Insert plans where I guess a minimum of 80% could be saved, roughly that might be a potential saving of 2040 MB of the 2550 MB actually in use for those 15000 plans.
This would reduce the total plan cache usage from 4500 MB to sth. around 2500 MB.

Looks like we could easily save some memory here, right?

We are talking about sql servers with not a lot of memory (32 GB) that could really benefit from some additional free memory, however, I guess this is also a question of genreal system health.

So my question is, what can I do about it? Do I have ANY options on database side to get control about this? Are there any adhoc query optimization options I do not know yet to let's say ignore this?

Or is it really required that the developer correctly adds the type and typelength to the input parameters?

In case of (n)varchar types, the leading length is already defined in the table definition, so are there any downsides to expect from using exactly those?

Additional Info:

I meanwhile found K.Tripps post about plan cache getting wild… and her query shows me that I have 1500 MB & 20300 plans with use count of 1 in cache…

Is "optimize for ad hoc workloads" a silver bullet in my particular case?

Some more Info:
I fould out that the queries are created by SqlDataAdapter and SqlCommandBuilder. The Parameter Size for all the created parameters are not derived anyhow from the origin table by the commandbuilder. If they are left unset, the size of the actual values passed in us used as size value. This can be found here: "If not explicitly set, the size is inferred from the actual size of the specified parameter value."

Best Answer

Take a look at Plan cache, adhoc workloads and clearing the single-use plan cache bloat.

To summarize:

If you're running SQL Server 2008 and you have cache being wasted by single-use plans, be sure to use the new "optimize for adhoc workloads."
If you still find that you're wasting 100s of MB or GB of cache, consider creating a job that programmatically checks cache and then clears the 'SQL Plans' from cache based on one of the options/code below.

OPTION 1 - Clearing JUST the 'SQL Plans' based on just the amount of
Adhoc/Prepared single-use plans (2005/2008):

DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE 
                    WHEN usecounts = 1
                        AND objtype IN (
                            'Adhoc'
                            ,'Prepared'
                            )
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE 
            WHEN usecounts = 1
                AND objtype IN (
                    'Adhoc'
                    ,'Prepared'
                    )
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 10
BEGIN
    DBCC FREESYSTEMCACHE ('SQL Plans')

    RAISERROR (
            '%s MB was allocated to single-use plan cache. Single-use plans have been cleared.'
            ,10
            ,1
            ,@StrMB
            )
END
ELSE
BEGIN
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            ) —

    Note: this IS ONLY a warning message
        AND NOT an actual error.
END
GO

OPTION 2 -

Clearing ALL of your cache based on the total amount of wasted by single-use plans (2005/2008):

DECLARE @MB DECIMAL(19, 3)
    ,@Count BIGINT
    ,@StrMB NVARCHAR(20)

SELECT @MB = sum(cast((
                CASE 
                    WHEN usecounts = 1
                        THEN size_in_bytes
                    ELSE 0
                    END
                ) AS DECIMAL(12, 2))) / 1024 / 1024
    ,@Count = sum(CASE 
            WHEN usecounts = 1
                THEN 1
            ELSE 0
            END)
    ,@StrMB = convert(NVARCHAR(20), @MB)
FROM sys.dm_exec_cached_plans

IF @MB > 1000
    DBCC FREEPROCCACHE
ELSE
    RAISERROR (
            'Only %s MB is allocated to single-use plan cache – no need to clear cache now.'
            ,10
            ,1
            ,@StrMB
            )
GO

Related Solutions

Sql-server – Stored procedure compilation blocking due to many parameters to sp_executesql

I suggest a different tack altogether. Instead of naming 18,000 parameters why not make use of table-valued parameters? I'm making some leaps here about what exactly you're using all these parameters for (since you so handily anonymized them for us :-)), but if you create these types:

CREATE TYPE dbo.VarcharParameters AS TABLE
(
  ParamName  SYSNAME,
  ParamValue VARCHAR(100)
);

CREATE TYPE dbo.BitParameters AS TABLE
(
  ParamName  SYSNAME,
  ParamValue BIT
);

Then change the procedure as follows (please note the comments inline about how to deal with stuff in the TVPs):

CREATE PROCEDURE dbo.ObviouslyAnonymizedProcedure2
  @SchemaID      INT                   = NULL,
  @TypeDesc      NVARCHAR(60)          = NULL,
  @VCParams      dbo.VarcharParameters READONLY,
  @BitParams     dbo.BitParameters     READONLY,
  @paramStartRow INT,
  @paramMaxRows  INT
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @From     NVARCHAR(MAX) = N'',
    @Where    NVARCHAR(MAX) = N'',
    @LF       CHAR(2)       = CHAR(13) + CHAR(10),
    @Tab      CHAR(1)       = CHAR(9),
    @FLOuter  NVARCHAR(MAX),
    @FLInner  NVARCHAR(MAX);

  DECLARE @LFTab CHAR(3) = @LF + @Tab;

  SET @FLOuter = @LFTab + '  t1.name' 
        + @LFTab + ', t1.object_id'
        + @LFTab + ', SCHEMA_NAME(t1.schema_id) AS schema_name'
        + @LFTab + ', t1.type_desc' 
        + @LF; 

  SET @FLInner = @LFTab + '  t0.name'
        + @LFTab + ', t0.object_id'
        + @LFTab + ', t0.schema_id'
        + @LFTab + ', t0.type_desc'
        + @LF; 

  SET @From = N' From sys.objects as t0 with(nolock) ' + @LF;

  IF @SchemaId IS NOT NULL
  BEGIN
    SET @Where = @Where + ' AND t0.schema_id = @SchemaId';
  END 

  IF @TypeDesc IS NOT NULL
  BEGIN
    SET @Where = @Where + ' AND t0.type_desc = @TypeDesc'
  END

  -- obviously you need a bunch more of these, and I'm making
  -- a half-educated guess about how the bit params are used:
  IF EXISTS (SELECT 1 FROM @BitParams WHERE ParamName = 'paramIsView' AND ParamValue = 1)
  BEGIN
    SET @Where += ' AND t0.type_desc = ''VIEW'''
  END

  -- and I'm not clear exactly what you're doing with the varchar params,
  -- but if you give some more clues I'm sure we can work that out too.
  -- It may be very simple to build a string from those, without having to
  -- reference every single one of them by name, depending on what they do.

  SET @sql = 'SELECT ' + @FLOuter + ' FROM ( SELECT ROW_NUMBER() OVER 
      (ORDER BY t0.[object_id]) AS rn, ' + @FLInner +
    @From + ' WHERE 1 = 1 ' + @Where + ') AS t1 
      WHERE t1.rn BETWEEN @paramStartRow + 1 
      AND @paramStartRow + @paramMaxRows ORDER BY rn;'

  EXEC sp_executesql @sql,
    N'@SchemaId INT,@TypeDesc NVARCHAR(60),@paramStartRow INT,@paramMaxRows INT',
    @SchemaID, @TypeDesc, @paramStartRow, @paramMaxRows;
END
GO

Now you can call it like this:

DECLARE @x dbo.VarcharParameters;

INSERT @x VALUES
  ('paramFoo',   'wuzzuh'),
  ('paramGamma', 'foobar');

DECLARE @y dbo.BitParameters;

INSERT @y VALUES
  ('paramIsView',  0),
  ('paramIsTable', 0);

EXEC dbo.ObviouslyAnonymizedProcedure2 
  @SchemaId      = 1, 
  @TypeDesc      = NULL, 
  @VCParams      = @x, 
  @BitParams     = @y, 
  @paramStartRow = 1, 
  @ParamMaxRows  = 20;

I won't show my results, because they'll be different from yours, but I bet the massive reduction in parameters will eliminate the compilation problems you have.

Also this is how you call this procedure from T-SQL; in order to call it from, say, C#, you'll need to use a DataTable or List or something compatible. I have an example here.

This is also much more flexible in terms of adding new parameters - you don't have to change the interface to the stored procedure, just add them to the procedure body (where relevant) and to the code that populates the data table.

Now just fill us in on what all the varchar parameters do and you might be one step closer to a solution. :-)

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.

One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.

Another point of suffering could be high compilations/second - so in an environment with a heavy workload and a lot of activity, there is a cost associated with compiling over and over.

You can see the impact of compilations/sec in perfmon (SQL Server Statistics:Compilations/sec). This can look like CPU pressure. To your performance/applications, this can look like increased query duration waiting for needless compiles each time it runs.

You can see the impact to the plan cache from the memory bloat by this query borrowed from Glenn Berry's Diagnostic scripts. How big is your SQLCP plan cache?

SELECT TOP(10) [type] AS [Memory Clerk Type], 
       SUM(pages_kb)/1024 AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

Also the query that was used in the question to identify the number of plans helps as well.

Is This Ever a Good Thing?

There are some cases where this could be good, but the situation is rare. Basically if you were suffering from parameter sniffing gone bad (nutshell: if the data can vary widely from execution to execution based on parameters, one compilation for one set of parameters ideal may yield an excellent query plan for that one query but poor for others.). My guess is that you likely wouldn't be dealing with that as bad as the implications from poor plan reuse.

What Can You Do About It?

Optimize For Ad Hoc Workloads can certainly help with the memory implications since only a stub of the plan is stored in cache at first execution, and the full plan isn't stored until it is executed a second time with the same plan.
Forced Parameterization could help here also. It can sometimes force parameterization to happen and help solve both the issue of cache bloat and the cost of having to recompile.
Fix The Queries Ideally, you shouldn't have to resort to these options, but instead can be more strict in your database development, encourage plan reuse, consider stored procedures for all of their benefits, and attempt to head off the problem that way. The ways to help fix this through forced parameterization or optimize for ad hoc are good to help, but the best solution is always aimed at the root cause.

There is an excellent resource here that talks about some of the dangers of plan cache pollution and some things you can do. I'd recommend a read here. It is written for SQL Server 2012, but the concepts and solutions apply.

Best Answer

Related Solutions

Sql-server – Stored procedure compilation blocking due to many parameters to sp_executesql

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

Related Question