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