Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tenaciousd3041
Starting Member
1 Post |
 Posted - 2007-05-15 : 17:07:04
|
| We have a couple of ad hoc queries in our app that we need to force parameterization. There queries can't be put in sp's because they come from a 3rd party app. The goal is to reduce the amount of cache being accumulated. We are on sql server 2005. The queries in question look like this:select * from TABLE1 where SEQUENCE_NUMBER = '<some literal>'When i query syscacheobjects i see thousands of compiled plans for that single query with different literal values in the WHERE clause. I want to force parameterization and make it reuse one plan. Seems to best way to do this is to turn FORCED PARAMETERIZATION on for the db or to create plan guides. Both don't seem to be working. I turned FORCED PARAMETERIZATION on for the db, then cleared proc cache (DBCC FREEPROCCACHE). I query syscacheobjects and it's empty. I run a test script with 10 queries similiar to the one above, passing a different literal into the where clause. I would expect there to be only one compiled plan for the paramitized version of the query but again there are 10 compiled plans for that query.I then turned SIMPLE PARAMETERIZATION on for the db and created a plan guide for the query:DECLARE @stmt nvarchar(max);DECLARE @params nvarchar(max);EXEC sp_get_query_template N'select * from TABLE1 where SEQUENCE_NUMBER = ''%''', @stmt OUTPUT, @params OUTPUT;EXEC sp_create_plan_guide N'Templat1', @stmt, N'TEMPLATE', NULL, @params, N'OPTION(PARAMETERIZATION FORCED)';I again clear the cache and syscacheobjects is empty. I run my test script. This time i can a row for the plan guide BUT there is still 10 rows for the queries in my test script. I expected to just see the row for the plan guide, indicating that it's using that compiled plan for all the queries but this isn't the case.Has anyone used query plan guides? Is my testing correct, should i expect LESS rows to accumulate in syscacheobjects? I can query sys.plan_guides and see that my plan guide is created and enabled but from my tests it doesn't seem like it's being used when i run my tests. Any advise on how to get this going?thanks,Dave |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-05-16 : 05:45:44
|
| I think you may have to qualify the table with "dbo." to benefit from cache |
 |
|
|
|
|
|
|
|
Query Plan Guides don't seem to work....