Wednesday 9 November 2016

Performance tweak thoughts...Optimize for Ad Hoc Workloads

So I noticed in my current environment (enterprise insurance company) we aren't really utilising this option as a performance tweak. To be honest I do turn this feature on for most of my builds, so far I haven't seen any major detrimental effects. I would say that this option is 100% for every instance and application profiles but is has done me ok so far...

 An ad hoc workload is essentially a query not put into a stored procedure so the full T-SQL Query is run each time.


 Introduced in MS SQL 2008; with the instance option “Optimize for Ad Hoc Workloads” (bah use of z!) enabled, SQL Server will create a small stub in the plan cache when the query is run for the first time. When that query is run for the 2nd time, then it will create a fully compiled plan, showing that ‘hey this query is valid’ and not a one off.


 This will relieve memory pressure by not allowing the plan cache to become filled up with compiled ‘junk’ plans that are not likely to be reused.

-- clear the cache for fresh results
DBCC FreeProcCacheGO 

 -- run a new query
SELECT [ID],[Procedure_Name],[Description],[Run_Date]
FROM [monitoring].[dbo].[MyLogTable] WHERE [ID] = 5045
GO

-- check to see the plan cache allocations/sizes
SELECT usecounts,cacheobjtype,objtype,size_in_bytes,[text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 









 This shows the Plan Size for this query: 16384 bytes.


 We can turn on the option for Ad-hoc workloads optimisation via the following below T-SQL commands or in SSMS at MS SQL Instance level right Click >Properties > Advanced > Change 'Optimize for ad hoc workloads' to TRUE.

-- show advanced optionssp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

 -- enable optimize ad-hoc workloads
sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO


 With this now turned on we can clear the cache and run this again to show the benefit ofthe reduce memory allocation as a stub in the plan cache.








This shows the plan size this time as 128 bytes, which is a big difference from our previous run! This time only the stub is stored in the cache, not the entire plan, so we don’t waste memory with a query that may never be executed again.

Why is this Good?
Memory allocated for execution plans comes from our buffer pool, so the more plans we have, the smaller the buffer pool will be for data and index pages. With this option enabled, we can save our cache from becoming bloated.


 Sounds Great! Whats the Downside?!
Also it could affect your performance in an environment where you have a huge plan cache, this feature would add to your wait time. Whenever a new query comes in, SQL Server has to check to see if that plan has ever been seen before. This lookup operation isn’t free along with inserting a plan or plan stub into the cache. So if you do enable... TEST, TEST, TEST and er TEST some more!

 So We Wouldn't Enable this when....
... our system runs every single query twice. Then there would be no point to this option being enabled. No benefit/gains to release additional resource to the memory pool. Not aware of many apps/developers with this type of execution profile! The side-effect is that you introduce a small CPU overhead initially, because every execution plan must be compiled 2 times before the plan is finally stored in the Plan Cache.


 Evidence!
We like to show our boss our memory efficiency savings! A good script I came across on the Interweb which works from MS SQL 2008+

 
-- for 2008 and up .. Optimize ad-hoc for workload 
IF EXISTS (
         SELECT 1
         FROM sys.configurations
         WHERE NAME = 'optimize for ad hoc workloads'
        )
 BEGIN
      DECLARE @AdHocSizeInMB DECIMAL(14, 2)
         ,@TotalSizeInMB DECIMAL(14, 2)
          ,@ObjType NVARCHAR(34)
       SELECT @AdHocSizeInMB = SUM(CAST((
                     
   CASE                        
    WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc' THEN size_in_bytes
    ELSE 0
   END
   ) AS DECIMAL(14, 2))) / 1048576
   ,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
  FROM sys.dm_exec_cached_plans
  SELECT 
   ' Total cache plan size (MB): '
   + CAST(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' 
   + CAST(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of   total cache plan occupied by adhoc plans 
     only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) 
     AS VARCHAR(max)) + '%' + ' ' AS   COMMENTS ,' ' + CASE 
  WHEN @AdHocSizeInMB > 200
  OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 20 -- 200MB or > 20%              
  THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference.'
  ELSE 'Setting Optimize for ad hoc workloads will make not a large amount of difference.'
 END + ' ' AS RECOMMENDATIONS
END
So generally I do like to turn this on, but would say test things and don't put it straight onto your prod boxes! Hopefully that does go without saying..... ;)

 See below some additional reading........Happy Memory optimising! 
Posted by at