Redundant plans in cache because of missing type length 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 &amp; 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."
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 &gt; 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 &gt; 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