mardi 24 février 2015

Plans created several times under Force Parameterization


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 turned the database to use forced parameterization, flushed the cache using dbcc freeproccache, and then run the above query 3 times. I use 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: enter image description here


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.







Aucun commentaire:

Enregistrer un commentaire