Optimize For Ad-Hoc Workloads

Learn how Optimize For Ad-Hoc Workloads can save some space in your plan cache.

Transcription:

Steve Stedman 0:09
Hi, this is Steve Stedman and I’m here to talk about daily checkup or database health quick scan items that we come across occasionally. These are settings that we optimize or configure on the SQL Server in order to help with performance. Usually, oddest setting is the optimized for ad hoc workloads. And this is a setting on the SQL server where it makes it so that one time use queries don’t have the plan cached until they’ve been used multiple times. So if you’re sitting there working in Management Studio, and you’re writing out a query, and then you change and you run it again, and you change it, it’s not going to cache that plan. If you have optimized Red Rock workloads turned on, until you run it twice, then that makes it so that you don’t flood the plan cache with a bunch of one time use queries. Now it puts a little tiny stub in the plan cache, so it knows that you run it once. But it doesn’t really fill in the plan cache with your query until it’s been run multiple times. The outcome of that is your plan cache does not fill up as quickly as it would if you have a lot of ad hoc queries. And what happens is that those one time use queries are going to take a split second longer, well, they’re not gonna take any longer to run the first time. But if you run them a second time, they may take a year longer to run. But for any query that’s being run multiple times, or frequently, it’s not going to change those, it’s just going to put less of those one time use plans into the plan cache, where you can change all these settings. If you go to Management Studio and right click on the server, you can go to the advanced properties. There’s a couple of settings in here in here we can turn on the optimized for ad hoc workloads right here, we can change the cost threshold for parallelism, and then change the max degree of parallelism down here as well. After changing those, you’ll save them to the server confirm that they’re set, and you’ll be good to go. Alright, hopefully this SQL Server tip will help you out. Have a great day.