As you already know there is No general formula to calculate max server memory you can do some quick maths and reach to a value but still you would need help of Perfmon counters at last to monitor memory usage and change accordingly. I know below general formula and I use it as well. I learned this formula from This Link
For SQL Server 2005 to 2008 R2
Please note from SQL Server 2005 to 2008 R2 max server memory only controls buffer pool. So max server memory configuration is bit tedious here and involves few calculations
Leave 2 G memory straight away for Windows OS.
Of course system would have antivirus running. Please leave 1.5G for Antivirus. Please note Mcafee and SQL Server do not go hand in hand so make sure you leave enough for it.
You can also check perfmon counter Perfmon Process-> Private bytes and Working Set
to monitor memory usage by AV and other small applications running on SQL Server box
Consider the memory requirements of the drivers/firmwares.You have to derive it based on memory requirements by drivers installed on the system. RAMMAP tool can help
Consider the NonbPool (aka MTL or MTR) memory requirements by SQL Server.
select sum(multi_pages_kb)/1024 as multi_pages_mb from sys.dm_os_memory_clerks
+ Max worker threads * 2MB
+ Memory for direct Windows allocations approximately 0 to 300 MB in most of the cases but you may have to increase it if there are many 3 party components loaded in SQL Server process (Including linked server dll’s, 3rd party backup dll’s etc.)
+ If you are using CLR extensively add some additional memory for CLR.
Consider the memory requirement by jobs (Including replication agents, Log shipping etc. ) and packages that will run on the server. It can very from MB's to GB's according to number of jobs running. For medium sized server you can take it as 250 MB
Make sure there is good enough free space for operating system.
Approximately (100 MB for each GB till 4G) + (50 MB for each additional GB till 12GB) + (25 MB for each additional GB till your RAM size)
Other memory requirements.
If you have any other memory requirement specific to your environment.
Max server memory= Total physical memory – (1+2+3+4+5+6+7)
I have not included memory configuration for SSIS.SSRS,SSAS you would also need to subtract memory required by these services from total physical server memory.
After you have configured above you need to monitor following counters
SQLServer:Buffer Manager--Page Life Expectancy(PLE):
SQLServer:Buffer Manager--CheckpointPages/sec:
SQLServer:Memory Manager--Memory Grants Pending:
SQLServer:memory Manager--Target Server Memory:
SQLServer:memory Manager--Total Server memory
For SQL Server 2012/2014.
From SQL Server 2012 onwards
setting up max server memory has become easy. Because now max server memory almost accounts for all memory consumption. Max server memory controls SQL Server memory allocation, including the buffer pool, compile memory, all caches, qe memory grants, lock manager memory, and CLR memory (basically any “clerk” as found in dm_os_memory_clerks). Memory for thread stacks, heaps, linked server providers other than SQL Server, or any memory allocated by a “non SQL Server” DLL is not controlled by max server memory.
You can allocate 75-80% to SQL Server and then use perfmon counters to monitor memory usage. In SQL Server 2012 few perfmon counters have been deprecated. Buffer manager counter is deprecated you must use memory manager counter
SQL Server: Memory Manager-- Target Server Memory (KB)
SQL Server: Memory Manager--Total Server Memory (KB)
SQL Server: Memory Manager- Free Memory (KB)
SQL Server: Memory Manager--Database Cache Memory (KB)
On value of PLE, I have used formula by Joanthan and fortunately it has worked for me.
The reason that the size_in_bytes
field of the sys.dm_exec_cached_plans
DMV, at least in terms of "Compiled Plans", is larger than the CachedPlanSize
attribute of the QueryPlan
node in the XML plan is because a Compiled Plan is not the same thing as a Query Plan. A Compiled Plan is comprised of multiple Memory Objects, the combined size of which equates to the size_in_bytes
field. So, the description of "Number of bytes consumed by the cache object" that you found in the documentation is accurate; it's just that it is easy to misinterpret what is meant by "cache object" given the name of the DMV and that the term "plan" has multiple meanings.
A Compiled Plan is a container that holds various pieces of information related to the query batch (i.e. not just a single statement), one (or more) of those pieces being the query plan(s). Compiled Plans have a top-level Memory Object of MEMOBJ_COMPILE_ADHOC which is the row in sys.dm_os_memory_objects
that is linked via the memory_object_address
field in both DMVs. This Memory Object contains the symbol table, parameter collection, links to related objects, accessor cache, TDS metadata cache, and possibly some other items. Compiled Plans are shared amongst Sessions/Users that are executing the same batch with the same Session settings. However, some related objects are not shared between Sessions/Users.
Compiled Plans also have one or more dependent objects that can be found by passing the plan_handle
(in sys.dm_exec_cached_plans
) into the sys.dm_exec_cached_plan_dependent_objects
DMF. There are two types of dependent objects: Executable Plan (Memory Object = MEMOBJ_EXECUTE) and Cursor (Memory Object = MEMOBJ_CURSOREXEC). There will be 0 or more Cursor objects, one per each cursor. There will also be one or more Executable Plan objects, one per each User executing that same batch, hence Executable Plans are not shared between Users. Executable Plans contain run-time parameter and local variable info, run-time state such as the currently executing statement, object ids for objects created at run-time (I assume this refers to Table Variables, Temporary Tables, Temporary Stored Procedures, etc), and possibly other items.
Each statement within a multi-statement batch is contained within a Compiled Statement (Memory Object = MEMOBJ_STATEMENT). The size of each Compiled Statement (i.e. pages_in_bytes
) divided by 1024 should match the CachedPlanSize="xx"
values of the <QueryPlan>
nodes in the XML plan. Compiled Statements will often have one (possibly more?) associated runtime Query Plans (Memory Object = MEMOBJ_XSTMT). Finally, for each runtime Query Plan that is a query, there should be an associated Query Execution Context (Memory Object = MEMOBJ_QUERYEXECCNTXTFORSE).
With respect to Compiled Statements, single-statement batches do not have separate Compiled Statement (i.e. MEMOBJ_STATEMENT) or separate runtime Query Plan (i.e. MEMOBJ_XSTMT) objects. The value for each of those objects will be stored in the main Compiled Plan object (i.e. MEMOBJ_COMPILE_ADHOC), and in that case, the pages_in_bytes
value for that main object divided by 1024 should match the CachedPlanSize
size in the <QueryPlan>
node of the XML plan. Those values will not equate, however, in multi-statement batches.
The size_in_bytes
value can be derived by summing the entries in the sys.dm_os_memory_objects
DMV (the items noted above in bold), all related by dm_os_memory_objects.page_allocator_address
for that Compiled Plan. The trick to getting the correct value is to first get the memory_object_address
from sys.dm_exec_cached_plans
for a particular Compiled Plan, then use that to get the corresponding MEMOBJ_COMPILE_ADHOC row from sys.dm_os_memory_objects
based on its memory_object_address
field. Then, grab the page_allocator_address
value from sys.dm_os_memory_objects
for that row, and use it to grab all rows from sys.dm_os_memory_objects
that have the same page_allocator_address
value. (Please note that this technique does not work for the other Cached Object types: Parse Tree, Extended Proc, CLR Compiled Proc, and CLR Compiled Func.)
Using the memory_object_address
value obtained from sys.dm_exec_cached_plans
, you can see all of the components of the Compiled Plan via the following query:
DECLARE @CompiledPlanAddress VARBINARY(8) = 0x00000001DC4A4060;
SELECT obj.memory_object_address, obj.pages_in_bytes, obj.type
FROM sys.dm_os_memory_objects obj
WHERE obj.page_allocator_address = (
SELECT planobj.page_allocator_address
FROM sys.dm_os_memory_objects planobj
WHERE planobj.memory_object_address = @CompiledPlanAddress
)
ORDER BY obj.[type], obj.pages_in_bytes;
The query below lists all of the Compiled Plans in sys.dm_exec_cached_plans
along with the Query Plan and statements for each batch. The query directly above is incorporated into the query below via XML as the MemoryObjects
field:
SELECT cplan.bucketid,
cplan.pool_id,
cplan.refcounts,
cplan.usecounts,
cplan.size_in_bytes,
cplan.memory_object_address,
cplan.cacheobjtype,
cplan.objtype,
cplan.plan_handle,
'---' AS [---],
qrypln.[query_plan],
sqltxt.[text],
'---' AS [---],
planobj.pages_in_bytes,
planobj.pages_in_bytes / 1024 AS [BaseSingleStatementPlanKB],
'===' AS [===],
cplan.size_in_bytes AS [TotalPlanBytes],
bytes.AllocatedBytes,
(SELECT CONVERT(VARCHAR(30), obj.memory_object_address, 1)
AS [memory_object_address], obj.pages_in_bytes, obj.[type]
--,obj.page_size_in_bytes
FROM sys.dm_os_memory_objects obj
WHERE obj.page_allocator_address = planobj.page_allocator_address
FOR XML RAW(N'object'), ROOT(N'memory_objects'), TYPE) AS [MemoryObjects]
FROM sys.dm_exec_cached_plans cplan
OUTER APPLY sys.dm_exec_sql_text(cplan.[plan_handle]) sqltxt
OUTER APPLY sys.dm_exec_query_plan(cplan.[plan_handle]) qrypln
INNER JOIN sys.dm_os_memory_objects planobj
ON planobj.memory_object_address = cplan.memory_object_address
OUTER APPLY (SELECT SUM(domo.[pages_in_bytes]) AS [AllocatedBytes]
FROM sys.dm_os_memory_objects domo
WHERE domo.page_allocator_address = planobj.page_allocator_address) bytes
WHERE cplan.parent_plan_handle IS NULL
AND cplan.cacheobjtype IN (N'Compiled Plan', N'Compiled Plan Stub')
--AND cplan.plan_handle = 0x06000D0031CD572910529CE001000000xxxxxxxx
ORDER BY cplan.objtype, cplan.plan_handle;
Please note that:
- the
TotalPlanBytes
field is just a re-statement of the sys.dm_exec_cached_plans.size_in_bytes
field,
- the
AllocatedBytes
field is the SUM of the related memory objects that typically matches TotalPlanBytes
(i.e. size_in_bytes
)
- the
AllocatedBytes
field will occasionally be greater than TotalPlanBytes
(i.e. size_in_bytes
) due to the memory consumption increasing during execution. This seems to happen mostly due to recompilation (which should be evident with the usecounts
field showing 1
)
- the
BaseSingleStatementPlanKB
field should match the CachedPlanSize
attribute of the QueryPlan
node in the XML, but only when using a single query batch.
- for batches with multiple queries, there should be rows marked as
MEMOBJ_STATEMENT
in sys.dm_os_memory_objects
, one for each query. The pages_in_bytes
field for these rows should match the individual <QueryPlan>
nodes of the XML plan.
Resources:
Best Answer
I can see from the question that you have
SQL Server 2012 SP1
. There was a Bug in SQL Server 2012 which forced PLE to plummet but that was fixed inSQL Server 2012 SP1 CU4
. Now since ** SQL Server 2012 SP3** has been released I suggest you apply SP3 and see if the issue subsides.You can't use perfmon counter
Buffer Manager: Database Pages
to check buffer size. As per BOL what it signifies is:So you can see it does not gives information about complete Buffer Pool. When you define SQL Server max server memory the value set in max server memory becomes the buffer pool size. As such from SQL Server 2012 onward the buffer pool has less significance. Previously it was both consumer and the provider of memory. From SQL Server 2012 onward its just the consumer. To see the contents of the buffer pool(to some extent) you have DMV sys.dm_os_buffer_descriptors.
Below query returns cached page count of each database.
If you want to get pages cached for each objects in particular database