I know what this option does and how to enable it. My question is what things will happen if I were to enable this.
Without giving too much info, our accounting system is a Microsoft Dynamics product and it uses a VM, 32GB RAM (28GB available to SQL server [2008 R2]). They had their vendor come and take a look at our configuration to address certain performance issues that the accounting team has been seeing. They even had another DBA take a look at our configuration. One of his recommendations was to 'look at missing indexes'. We could say that about almost every SQL server instance in existence, each table has a unique non-clustered index on it, not my choice but im told that making any changes to the indexes on tables that the software accesses could potentially cause issues per the vendor. His second was to enable 'optimize for adhoc workloads'. I've looked into this option a few times before and many say it should always be enabled, whereas some say it's not truly necessary and can go either way.
With optimize for adhoc workloads, I know that single use plans are stored as a stub and the entire plan is not actually kept in cache until the plan is run two times. With a system such as this, are we REALLY going to see any sort of performance gains? Per Kimberly Tripp's article, I've run this query:
SELECT objtype AS [CacheType] ,count_big(*) AS [Total Plans] ,sum(cast(size_in_bytes AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs] ,avg(usecounts) AS [Avg Use Count] ,sum(cast(( CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END ) AS DECIMAL(18, 2))) / 1024 / 1024 AS [Total MBs - USE Count 1] ,sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1] FROM sys.dm_exec_cached_plans GROUP BY objtype ORDER BY [Total MBs - USE Count 1] DESC
and here is the result I get:
Now, as we can see, we have approx 1.7GB of single use plans. Probably worth it to turn this option on as the box has 28GB available to SQL server, right? Well, We're talking about freeing maybe 1-1.25GB as the stubs still take space, just not as much. I ran a query to pull all single use plans and get their size, our largest plans are about 1-1.5MB.
So, here are my questions (sorry it took a while to actually get to these):
- Do we really think that we will see a measurable performance gain here? If so, how can we quantify it aside from looking at the space used in the query above?
- Understandably, I hesitate throwing switches like this on production systems. What implications should I be cautious of? Will the procedure cache wipe itself and rebuild? Are there any issues that I should be made aware of when turning this on?