1

In my database I use stored procedures to insert multiple rows from a temporary table variable. Inserting data into the temporary table variable causes a new adhoc plan to be created prior to each stored procedure execution which causes plan cache pollution. Is there any way to stop saving these adhoc plans and eliminate the pollution in the plan cache?

USE [tempdb];
GO

DROP PROCEDURE [dbo].[uspMyTableInsert];
GO

DROP TABLE [dbo].[MyTable];
GO

DROP TYPE [dbo].MyTable;
GO

CREATE TABLE [MyTable]
    (
     [ID] INT NOT NULL
              IDENTITY(1, 1)
              CONSTRAINT [PK_MyTable_ID] PRIMARY KEY CLUSTERED,
     [FirstName] VARCHAR(50) NOT NULL
    );
GO

CREATE TYPE [MyTable] AS TABLE
(
[FirstName] VARCHAR(50) NOT NULL
);
GO

CREATE PROCEDURE [dbo].[uspMyTableInsert]
    (
     @Table MyTable READONLY
    )
AS
BEGIN
    INSERT  INTO [dbo].[MyTable]
            ([FirstName])
    SELECT
        [t].[FirstName]
    FROM
        @Table AS [t];
END;
GO


DBCC FREEPROCCACHE;
GO


DECLARE @Table MyTable

INSERT  INTO @Table
        ([FirstName])
VALUES
        ('Bugs Bunny #1'),
        ('Daffy Duck #1');

EXEC [dbo].[uspMyTableInsert] @Table = @Table;
GO
  • 1
    You're not getting multiple plans for the procedure call itself, but rather because you are populating the data into your @Table variable manually and that isn't getting properly parameterized (thus each batch with different text will generate its own plan - even if you just change white space or change #1 to #2). You could consider using Optimize for ad hoc workloads in which case you will still have a bunch of entries in your plan cache but they will be small, sub-1kb plan stubs except for those batches that are called more than once. – Aaron Bertrand Jun 27 '16 at 18:26
  • I am using Optimize for Adhoc Workloads and while it certainly does help I was hoping for something to eliminate the bloat. Should I consider emptying the plan cache using a regularly scheduled job? – runamuk0 Jun 28 '16 at 14:26
  • 1
    How have you determined that there is "bloat"? Because the cached_plans DMV has a lot of rows? Are you seeing evictions from cache, and if so, where, how many, what plans are getting evicted, etc.? Is your goal to have an empty plan cache? What actual problem are you trying to solve? – Aaron Bertrand Jun 28 '16 at 14:41
  • I've been watching the procedure cache climb upwards of 3GB and then suddenly drop to around 100MB. I'm trying to stabilize the cache contents, if possible to contain just good reusable query plans and keep it clean. Is this even possible? – runamuk0 Jun 28 '16 at 22:28
  • I don't think a drop to 100MB has anything at all to do with your question topic. It is more likely you have some scheduled or on-demand maintenance that frees the procedure cache (directly or indirectly). – Aaron Bertrand Jun 28 '16 at 23:00

Your Answer

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

Browse other questions tagged or ask your own question.