Press "Enter" to skip to content

Optimizing for Ad Hoc Workloads

Chad Callihan explains the importance of a feature:

Anytime you run a query, SQL Server needs to build an execution plan to use as directions for best executing that query. These execution plans can be stored in your plan cache to be reused in the future if that same query is ran. Instead of resources going into rebuilding the plan each time, SQL Server can use the same plan as the previous execution. This is great for queries that run over and over. On the other hand, what if you have a large number of queries that will run once but never again? Plans never to be reused are taking up valuable space in plan cache. If this looks like your workload, consider enabling the Optimize for Ad hoc Workloads feature.

Click through to learn more about the feature. I haven’t seen many (any?) cases where Optimize for Ad Hoc Workloads doesn’t help at least a little on net.