SQL Server 2008 R2 Memory Pressure My SQL Server has a weird problem. It seems it happens with the same matter. It always restarts after running a period of time(about one month long). And I used dbcc memorystatus to check the problem. I found MEMORYCLERK_SQLOPTIMIZER singlepage allocator ocuuping a lot of memory. I guess it could be why my SQL server restarted.

So I used sp_WhoIsActive to find which query script holds memory. After I collect some data, I don't know how to distinguish the relationship between sp_WhoIsActive result and  dbcc memorystatus.

Could you help me and give me some hints to solve the problem?

The following is some information about my SQL Server:


Server: CPU: intel E5645*2, RAM 64GB, HD: 10krpm SAS
SQL Server: 2008r2 with failover cluster feature enabled


max memory:52GB,
instance:1
No of db: 1700(Don't be surprised. There are 1700 dbs in my SQL Server.)



The attach file can be downloaded here.

After unzipping, there are 3 files.


att1.txt - The status before server is going to restart.
att2.txt - The status that the server has run for two weeks after restarting.
whoisactive.bak - The result for sp_WhoIsActive. 


You can restore it in the SQL server2012.

My SQL Version
Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)  Enterprise Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) 



Finally I turned on optimize for ad hoc workloads, but it didn't work. I am not sure what happened. Now I restart service when optimizer memory almost holds up all the memory. And all the memory is released.
Well, you can check how often your plans are reused. After that, you may want to decide to switch to optimize for ad-hoc workloads. Those flag will only save a plan-stub instead of a full plan and will do a fast compile of the stub if there is a query which uses the same stub. Those option isn't enabled by default (due to the fact that it's a newer Serverfeature).

Take a look at this query for example:

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 'SQL Server Configuration' AS GROUP_TYPE
    ,' 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 &gt; 200
            OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) &gt; 25 -- 200MB or &gt; 25%
            THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference. Ref: http://sqlserverperformance.idera.com/memory/optimize-ad-hoc-workloads-option-sql-server-2008/. http://www.sqlskills.com/blogs/kimberly/post/procedure-cache-and-optimizing-for-adhoc-workloads.aspx'
        ELSE 'Setting Optimize for ad hoc workloads will make little difference !!'
        END + ' ' AS RECOMMENDATIONS


It should give you an overview if you would benefit from it or not.

You can enable it using this:

sp_configure N'show advanced options',1
GO
reconfigure
GO
sp_configure N'optimize for ad hoc workloads',1
GO
sp_configure N'show advanced options',1
GO
reconfigure
GO


Hopefully this will help you.