.NET Tips and Tricks

Blog archive

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 at 9:45 AM

comments powered by Disqus


  • VS Code Now Has Apple Silicon Builds for Native Mac Development

    Goodbye Rosetta, hello M1. Visual Studio Code has been updated with new builds that let it run natively on machines with Apple Silicon (M1), the company's own ARM64 chips.

  • Visual Studio 2019 for Mac v8.9 Ships with .NET 6 Preview 1 Support

    During its Ignite 2021 online event for IT pros and developers this week, Microsoft shipped Visual Studio 2019 for Mac v8.9, arriving with out-of-the-box support for .NET 6 Preview 1, which the company also released recently.

  • Analyst: TypeScript Now Firmly in Top 10 Echelon (Ruby, Not So Much)

    RedMonk analyst Stephen O'Grady believes TypeScript has achieved the rare feat of firmly ensconcing itself into the top 10 echelon of his ranking, now questioning how high it might go.

  • Black White Wave IMage

    Neural Regression Using PyTorch: Training

    The goal of a regression problem is to predict a single numeric value, for example, predicting the annual revenue of a new restaurant based on variables such as menu prices, number of tables, location and so on.

Upcoming Events