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
@Tablevariable 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#1to#2). You could consider usingOptimize for ad hoc workloadsin 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