Dynamic pivot and its effect on the plan cache

Question

Dynamic pivot and its effect on the plan cache

scott.laird

Mr or Mrs. 500

Points: 521
More actions
November 3, 2016 at 10:47 am

#322541

We have a heavily used GUI that displays specific business data in a pivot.
A stored procedure is used to create the pivot, using dynamic SQL.
The SP is using EXEC sp_executesql with parameters in the following way:
SET @SQL = 'select * from (select '+@Cols+' from #X) StdP PIVOT (COUNT(DateStamp) FOR DateStamp IN ('+@Pivot+')) as PVT order by '+@Cols2+''
EXEC sp_executesql @SQL,
N'@_Cols varchar(100), @_Cols2 varchar(100), @_Pivot varchar(5000)',
@_Cols = @Cols, @_Cols2 = @Cols2, @_Pivot = @Pivot
****@Pivot can and most of the time is, will be different on each execution.
The plan cache is overflowing with many prepared, single use execution plans.
How can this be avoided?
Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

scott.laird (11/3/2016)
We have a heavily used GUI that displays specific business data in a pivot.
A stored procedure is used to create the pivot, using dynamic SQL.
The SP is using EXEC sp_executesql with parameters in the following way:
SET @SQL = 'select * from (select '+@Cols+' from #X) StdP PIVOT (COUNT(DateStamp) FOR DateStamp IN ('+@Pivot+')) as PVT order by '+@Cols2+''
EXEC sp_executesql @SQL,
N'@_Cols varchar(100), @_Cols2 varchar(100), @_Pivot varchar(5000)',
@_Cols = @Cols, @_Cols2 = @Cols2, @_Pivot = @Pivot
****@Pivot can and most of the time is, will be different on each execution.
The plan cache is overflowing with many prepared, single use execution plans.
How can this be avoided?
Thanks in advance.

Do you have "Optimize for Ad Hoc Queries" enabled?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

scott.laird Mr or Mrs. 500 Points: 521 More actions · Answer 2

scott.laird

Mr or Mrs. 500

Points: 521

November 4, 2016 at 4:05 am

#1910538

yes....optimize for adhoc is on

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

From Glenn Berry's diagnostic queries series:

-- Find single-use, ad-hoc and prepared queries that are bloating the plan cache (Query 44) (Ad hoc Queries)

SELECT TOP(50) [text] AS [QueryText], cp.cacheobjtype, cp.objtype, cp.size_in_bytes/1024 AS [Plan Size in KB]

FROM sys.dm_exec_cached_plans AS cp WITH (NOLOCK)

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

WHERE cp.cacheobjtype = N'Compiled Plan'

AND cp.objtype IN (N'Adhoc', N'Prepared')

AND cp.usecounts = 1

ORDER BY cp.size_in_bytes DESC OPTION (RECOMPILE);

-- Gives you the text, type and size of single-use ad-hoc and prepared queries that waste space in the plan cache

-- Enabling 'optimize for ad hoc workloads' for the instance can help (SQL Server 2008 and above only)

-- Running DBCC FREESYSTEMCACHE ('SQL Plans') periodically may be required to better control this.

-- Enabling forced parameterization for the database can help, but test first!

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden