SQL Server DBA : Troubleshooting 100% Memory / Memory Leak(s)

                        Troubleshooting 100% Memory / Memory Leak(s):

ü  Verify Task Manager for basic understanding of Memory utilization by which Process.

ü  If memory is consumed by other processes contact respective team to get it fixed.

ü  If memory is consumed high by SQL Server follow below steps

 Verify SQL Server Error logs for any memory related errors

MTL Based Errors:

 i) SQL Server 2000

WARNING: Failed to reserve contiguous memory of Size

 ii) SQL Server 2005

Failed Virtual Allocate Bytes: FAIL_VIRTUAL_RESERVE

 iii)SQL Server 2005

Failed to initialize the Common Language Runtime (CLR)

BPool Based Errors:

 i) BPool::Map: no remappable address found.

ii) BufferPool out of memory condition

iii)LazyWriter: warning, no free buffers found.

BPool (or) MemToLeave errors:

i) Error: 17803 “Insufficient memory available..”

ii) Error: 701, Severity: 17, State: 123.

There is insufficient system memory to run this query.

b) If MTL is the reason for the Memory issue we have to determine  whether it is SQL Server or some non-SQL component that is using the most MemToLeave memory

Query:

select sum(multi_pages_kb)  from sys.dm_os_memory_clerks

In MTL if SQL Server Owned memory is very less ,then determine if there are COM objects, SQL Mail, or 3rd party extended stored procedures being used, and move them out of process if possible(or contact App Team).

c) If MTL is not the reason then we need to focus on BPool portion and who is occupying more in BPool.

To find out who is consuming more in BPool fire below query:

select  *  from sys.dm_os_memory_clerks order by  Single_pages_kb  desc

To calculate the BPool approximate usage size use below command:

select  sum(single_pages_kb) from sys.dm_os_memory_clerks

Extra Counters to Monitor:-

Monitor PLE(Perfmon Counter):-

PLE is the expected time a read page from file is maintained in Buffer Pool.

Monitor BCHR(Perfmon Counter):-

Buffer Cache Hit Ratio is the utilization ratio of Buffer Pool towards I/O operation. Microsoft recommends >95% for OLTP and >90% for OLAP.