Speeding Up SQL Server: Planning for One-Time Queries
As I discussed in an earlier column, SQL Server keeps a plan cached for each query it sees (assuming the query requires planning in the first place, of course). That's great for speeding up processing the next time that query shows up because the plan for the query can simply be pulled from the cache.
However, there are any number of queries in any application that SQL Server may never see again (at least in, for example, the next 24 hours). The plans for these "one-time" queries are taking up space in the cache even though they might never be used again. The cache manager will recover space if necessary by keeping track of plans with low planning costs and discarding those plans as the cache runs out of space. However, that doesn't address the space used by those "one-time" plans.
You can help the cache manager out by turning on (or having your DBA turn on) the option to optimize SQL Server for ad-hoc workloads. With this turned on, the first time a plan is created, the identifiers for storing the plan in the cache are created, along with a stub for the plan. The plan itself, however, isn't added to the cache. It's only upon the second occurrence of the query (or one like it) that the plan is added to the cache. The basic assumption here is that if a query appears twice, it will appear many times. The only cost is that the plan has to be generated twice.
In addition to reducing the amount of memory held by the cache, this option will give you some idea of how many "one-off" queries you have. After this option is turned on, you can check to see how many stubs you have in the cache. If you don't have many, then it indicates that most of your queries are run multiple times and optimizing for ad hoc queries probably isn't doing you much good.
If you have lots of stubs it indicates that you've saved yourself space in your cache -- this was a change worth making.
But it might be telling you something else. It seems to me that an application with many unique queries is an unusual kind of application. I'd wonder if the application was generating queries in such a way that SQL Server didn't recognize that it could reuse its plans. I'd be interested in finding a way to "standardize" those queries to allow SQL Server to reuse their plans.
Posted by Peter Vogel on 10/15/2018