Single-use plans

The term “ad hoc” means “as needed,” and in the case of SQL Server it refers to a single-use query plan, meaning that a plan is generated for a specific query and never used again. Single-use plans are common in database-backed applications where ORMs are used (Object-Relational Mapping tools, such as Entity Framework, Dapper, Hibernate, NHibernate, LINQ to SQL, and many others), and in cases where T-SQL code is generated using string concatenation.

These single-use plans require memory, but once the query has been run, they continue to sit there inside the plan cache, adding up to what we call plan bloat. Since these plans are never going to be used again, we can safely remove them from the cache. The question becomes — especially on a busy instance — how do we do this efficiently?

Forced parameterization

One of the methods of resolving plan bloat is to enable forced parameterization. This setting forces T-SQL queries to become parameterized. By way of example, it means that this query, containing literal values:

SELECT col1, col2, col3
FROM table
WHERE col1 = 256

becomes the following:

SELECT col1 , col2 , col3 FROM table WHERE col1 = @i

The first thing we see in this contrived example is that the white space in the original query (line breaks and spaces) are condensed so that the query is on a single line, and single spaces are placed around everything including the commas. Finally, the WHERE condition is parameterized. What this does is aggressively force the queries into a consistent format so that there is a better chance of reusing an existing plan.

The major issue with forced parameterization is a feature called parameter sniffing. If our data is skewed in any way and SQL Server comes up with a specific query plan according to how that data is distributed (let’s say it performs a nested loop join instead of a hash match), that plan will be reused even for queries where a different join type would be better. Erik Darling goes into some detail in this post.

No real-world data is evenly distributed, so forced parameterization is not recommended as a default setting, however (and this is an important note) that doesn’t mean it isn’t useful. If you find that forced parameterization is useful, please use it.

Additionally, if your database is in compatibility mode 150 or higher (available on SQL Server 2019 and Azure SQL Database), this parameter sniffing problem is mitigated to a large extent with adaptive joins. On SQL Server 2017, adaptive joins can be forced using a filtered columnstore index hack, which Kendra Little writes about.

Enter ad-hoc caching

Enabling the optimize for ad hoc workloads configuration setting will reduce the amount of memory used by all query plans the first time they are executed. Instead of storing the full plan, a stub is stored in the plan cache. Once that plan executes again, only then is the full plan stored in memory. What this means is that there is a small overhead for all plans that are run more than once, on the second execution.

If the number of single-use plans take a significant portion of SQL Server Database Engine memory in an OLTP server, and these plans are Ad-hoc plans, use this server option to decrease memory usage with these objects.

Single-use plans consume much less memory with this setting enabled, and in the vast majority of database-based applications that use an ORM or string-concatenation to generate T-SQL queries (which is most real-world SQL Server instances I’ve seen), this is a set-and-forget configuration option that will make more memory available where it matters: the buffer pool.

Summary

Forced parameterization can be great, especially on SQL Server 2019, but not everyone has access to that just yet. In the meantime, enabling optimize for ad hoc workloads will reduce the memory footprint of single-use plans in the plan cache and keep your system running cleaner.

Randolph West is a Microsoft Data Platform MVP, and has worked with SQL Server since the late 1990s. When not consulting, you can see them acting on the stage and screen, or doing voices for independent video games. Also available for mentoring, talks on SQL Server and technology in general, Randolph offers training for junior DBAs. Connect with Randolph on Twitter. Buy their book on Amazon.