1

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

1

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
  • Hi Scott, thanks for th helpful advice. Cleaning up all of the created single-use plans would free the memory, but they would still be created, so this would be my 2nd best solution. Since I do not expect we are doing something really wrong here, I am still looking for an answer to the question howto prevent all those plans being created only because of this useless Size setting...? – Magier Feb 14 '17 at 15:10
  • 1
    @Magier You might as well try optimize for ad hoc workloads. I'd also try turning forced parameterization on. – Erik Darling Feb 14 '17 at 19:05
  • Eric, Optimize for adhoc did not do the trick I cleared a plancache to see but the same kind of Single usage queries returned very soon. The force par. Part I'll have to investigate first. – Magier Feb 14 '17 at 22:41
0

We ended changing the application code. It was not possible to avoid the dynamic character lengh in the resulting Sql statements to save the creation and storage for additional execution plans by configurations of sql server. So what we did was to read the schema of the source table from the dataset where the command builder created the statement from.

    SqlCommandBuilder cb = new sqlCommandBuilder(mydataAdapter);
    var schemaDT = new DataTable();
    mydataAdapter.FillSchema(schemaDT, SchemaType.Source);

That way it gets possible to get the correct SqlParameter.Size property for each parameter by matching the SqlParameter.SourceColumn property with schemaDT[SqlParameter.SourceColumn].

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.