SQL Server 2008 R2 Memory Pressure

Question

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.

1. att1.txt - The status before server is going to restart.
2. att2.txt - The status that the server has run for two weeks after restarting.
3. 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.

Answer 1

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 > 200
            OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 25 -- 200MB or > 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.

Answer 2

Assuming you are running at least "Cumulative Update 3 for SQL Server 2012 Service Pack 1 (SP1)" which would rule out the known issue I noted in my comment (https://support.microsoft.com/en-us/kb/2803065), then the most likely culprit for this problem is the cumulative effect of some rogue queries over time.

MEMORYCLERK_SQLOPTIMIZER is the place where the SQL Server Optimizer creates the plans for query execution. Costly plans take up a lot more space, especially those using lots of sorts and hash joins. Sometimes these poor queries can be fixed with something as simple as adding an appropriate index or two. With 1700 DBs, however, pinpointing the rogue queries underlying your problems could be a painful endeavor. I think your approach is correct in trying to cross-reference the dbcc memorystatus results against the sp_WhoIsActive results. I'm sorry I can't offer more help than this.