May 6, 2013 at 6:59 pm
I noticed that one query was always having a high compilation time and this led me to what I believed to be a lot of adhoc plans in the plan cache.
The queries are sent through from an external app.
After some research and testing I have turned on "optimize for ad hoc workloads" and added some Plan Guides for the offending queries.
This has stopped the compilation time being excessive but now I notice:
1. The space usage of the adhoc plans has gone down from about 1400mb to a few MB
Prepared
Total Plans 2338
Total MBs 205
Total MBs - USE Count 1100
Total Plans - USE Count 1 1370
Adhoc
Total Plans 5633
Total MBs 40
Total MBs - USE Count 113
Total Plans - USE Count 1 5156
2.
I notice the Adhoc plan count is going up quite fast now -- roughly one line per query -- the size of each plan is 200 bytes - 300 bytes (rather than 24K).
So is this normal? and will some rows be removed or will it now just keep on adding rows until the memory fills up and the server crashes??
Maybe a bit paranoid as I am not sure if this is normal.
Extra Note: I have removed my Plan Guide and the records are still being inserted quite fast into the Plan Cache (though the query times have now increased to the previous times as you would expect).
It looks like the Plan Cache will keep on growing until it reaches a system limit and then entries will be removed??? Not 100% sure though about that.
thanks
May 6, 2013 at 7:54 pm
The optimize for adhoc is storing a plan stub instead of a full plan. You may have more stubs getting stored because you have more space. The stub will be converted to a full plan on the next use (if it is used again).
So, the behavior you are seeing should be expected. It should not overload memory and cause a server crash. But you still have the max memory that could be used - much the same as before you enabled the setting.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 6, 2013 at 8:19 pm
Thanks for that.
I was thinking that it would only store a new plan for a new query when the Plan Guide with N'OPTION(PARAMETERIZATION FORCED)' was set.
but it still seems to store a record for every query sent to SQL -- albeit a lot less space is used when 'optimize for ad hoc workloads' is set to "1"
Got a bit of a fright when new records kept on pouring into the Plan Cache.
So as might be obvious adding the Plan Guides back in will not have any influence on the current behaviour (which is normal).
thanks for your help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply