Sql-server – SQL Server 2008 R2 Memory Pressure

optimizationsql serversql-server-2008-r2

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.

Best Answer

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.

Related Solutions

SQL Server 2008 R2 – Understanding Ghost Memory

You won't get a true picture of memory usage from Task Manager if the account the service is running under has the lock pages in memory privilege (edit: as per Mark Rasmussen's comment/link). To determine how much memory is being used you can look at:

SQLServer:Memory Manager\Total Server Memory perfmon counter
DMVs

I can't recall if there is a DMV or combination of that will give you the total memory allocation but the following will show the bulk of it.

SELECT TOP(10) [type] AS [Memory Clerk Type], SUM(single_pages_kb) AS [SPA Mem, Kb] 
FROM sys.dm_os_memory_clerks 
GROUP BY [type]  
ORDER BY SUM(single_pages_kb) DESC OPTION (RECOMPILE);

SELECT DB_NAME(database_id) AS [Database Name],
COUNT(*) * 8/1024.0 AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors
WHERE database_id > 4 -- system databases
AND database_id <> 32767 -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC OPTION (RECOMPILE);

The second is the most interesting usually, buffer pool allocations by database. This is where the lions share will be used and it can be useful to understand which of your databases are the biggest consumers.

Sql-server – SQL Server 2012 Standard using too much memory

I don't think DBCC MEMORYSTATUS (or the other queries) are being that revealing at the moment (don't know what the others think); so I am going to ask you to run just one more:

dbcc traceon(3654, -1)
go
waitfor delay '00:05:00'
go
select top 20 memory_object_address, source_file, sum(size_in_bytes) as total_bytes
from sys.dm_os_memory_allocations
group by memory_object_address, source_file
order by total_bytes desc
go
dbcc traceoff(3654, -1)
go

You can see I've built in a delay there to allow it time to collect some information. Depending on what that reveals, I think you are faced with a choice, continue to investigate or speculate:

continue to drill further into the memory leak using more advanced debugging techniques, eg using sysinternals tool Process Explorer; find process sqlservr.exe and look for evidence of memory leak, or:
use our instincts and what we know about your SQL Server and make and educated guess this is most likely to do with the MySQL linked server. Next time you restart your service, temporarily disable this bit of functionality, eg drop the linked server and/or disable reports which depend upon it. Monitor for your issue. If the issue does not occur, you have a good candidate for your culprit. I think there are newer drivers available (5.3.4?) or you could consider refactoring this part of the application to an SSIS job which creates a local cache once a night for example. If the problem does not occur you at least rule it out.

As this is a live server I think your debugging options are a bit limited so it might be time to try some options, see how the server responds.

Just in reference to using DBCC MEMORYSTATUS, I have had some success with this technique in the past, for example identifying run-away full-text queries or use of OPENXML without the compulsory sp_xml_removedocument.

Best Answer

Related Solutions

SQL Server 2008 R2 – Understanding Ghost Memory

Sql-server – SQL Server 2012 Standard using too much memory

Related Question