SQL Server Performance Forum – Threads Archive

Memory counters

Hi ,
i have 2 instances in my sql server on same machine and i need to get following report,so many counters are available but not able to get the correct counters
1)Currently memory being consumed by my each instances of SQL Server at a given time.
2)total memory configured for my each instances.
3)How much memory required if there is memory shortage by outcome of above counters. Thanks

1. SQLServer:Memory Manager: Total Server Memory (KB) and SQLServer:Memory Manager: Target Server Memory (KB). The first counter, SQLServer:Memory Manager: Total Server Memory (KB), tells you how much the mssqlserver service is currently using. This includes the total of the buffers committed to the SQL Server BPool and the OS buffers of the type "OS in Use". The second counter, SQLServer:Memory Manager: Target Server Memory (KB), tells you how much memory SQL Server would like to have in order to operate efficiently. This is based on the number of buffers reserved by SQL Server when it is first started up. http://www.sql-server-performance.com/performance_monitor_counters_memory.asp 2. Run sp_configure and see the ‘MAX SERVER MEMORY ‘ counter for configured memory… 3. Based on the above info you can’t conclude your server has memory pressure… Check the Page Life Expectency counter in perfmon, it is below 300 seconds then your may need more memory…
http://www.sqlservercentral.com/columnists/aBanerjee/analyzingmemoryrequirementsforsqlserver.asp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Also refer tohttp://www.sql-server-performance.com/awe_memory.asp if you have enabled the AWE in thsi case. Another piece of advice is if this is not a dedicated SQL Server then make sure you have monitoring MemToLeave information too, as it manage the transaction log requirements. Refer to pages/sec counter too as the high rate for this counter could indicate excessive paging. Monitor the Memory: Page Faults/sec counter to make sure that the disk activity is not caused by paging. To determine whether SQL Server rather than another process is causing excessive paging, monitor the Process: Page Faults/sec counter for the SQL Server process instance. If the target is less then the total that means other process/application is contending for memory hence SQL is dynamically releasing the memory to other process. Finally as you are using SQL 2005 you could take help of DMV’s sys.dm_os_memory_clerks to see the content.http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx is a good one too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

awesome,thanks Mohammed and Satya

]]>