People use forced parameterization to save plan compilation cost. However, I found things different here.
The query I will run is as following, and under AdventureWorks2012 database:
DECLARE @Var VarChar(250), @SQL nvarChar(MAX)
SET @Var = NEWID()
SET @SQL =
'SELECT SalesOrderHeader.SalesPersonID,
COUNT(DISTINCT SalesOrderHeader.CustomerID),
SUM(SalesOrderDetail.OrderQty)
FROM Sales.SalesOrderHeader
INNER JOIN Sales.SalesOrderDetail
ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product
ON Product.ProductID = SalesOrderDetail.ProductID
WHERE Product.Name = ' + '''' + @Var + '''
GROUP BY SalesOrderHeader.SalesPersonID'
EXEC (@SQL)
I configured the database to use forced parameterization, flushed the cache using dbcc freeproccache
, and then ran the above query 3 times. I used following query to get the plan use stats:
select bucketid, usecounts, memory_object_address, cacheobjtype, objtype, plan_handle, text
from (select * from sys.dm_exec_cached_plans ) as cacheplan
cross apply sys.dm_exec_sql_text(plan_handle)as text
WHERE text.text LIKE'%SalesOrderHeader%.%SalesPersonID%'
And below is the result I got:
Except row 1 are all about the queries I ran. Row 5 I can understand. This means database engine actually parameterized product.name and generate a plan for later use. That's why the usecounts = 3.
What I don't understand is why we still have row 2,3,4? Does that mean forced parameterization couldn't save anytime (or even cost more) when running the same type of queries?
I would really appreciate any suggestions or ideas. Thanks.
Additional Info:
I understand we can turn on "Optimized Ad hoc Workloads" option for the instance to save some space. However, I feel these ad hoc plan stubs are not supposed to be cached at all.
I know using sp_executesql would make solve above problem. But there are some queries in our production database that cannot be easily changed.