get SQL server memory usage

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » get SQL server memory usage

16 posts, Page 1 of 2

1 2 »»

get SQL server memory usage

Rate Topic

Display Mode

Topic Options

Author

Message

sqlfriends

Posted Thursday, January 31, 2013 5:46 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 1,610, Visits: 2,794

Is there a simpler way to get what exactly SQL server usage of memory on the server except using perfmon?

I mean the memroy SQL server uses not the other part on the server like OS.
Our SQL servers are all dedicated for SQL server without any other applications.

If I have set up a max server memory, does it mean SQL server can only use to that maximum number, all left is used by operating system?

Thanks

Post #1414410

SQL Pizza

Posted Friday, February 01, 2013 1:23 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:14 AM
Points: 116, Visits: 293

Let me guess. You got "lock pages in memory" enabled?

Use the DMV sys.dm_os_process_memory ( select (physical_memory_in_use_kb / 1024) AS total_mem_usage_MB from sys.dm_os_process_memory ).
For a detailed view use sys.dm_os_memory_clerks ( select [type], name, (awe_allocated_kb / 1024) AS total_mem_usage_MB from sys.dm_os_memory_clerks ).

Greetz SQL mod

Greetz
SQL Pizza

sometimes you dont see the pizza for the toppings...
seek and ya shall find...

Post #1414480

opc.three

Posted Friday, February 01, 2013 6:43 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 7:16 PM
Points: 6,731, Visits: 11,785

sqlfriends (1/31/2013)

Is there a simpler way to get what exactly SQL server usage of memory on the server except using perfmon?

I mean the memroy SQL server uses not the other part on the server like OS.

When using T-SQL I like these:

SELECT  cntr_value AS [amount of memory SQL is using in KB]
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Total Server Memory (KB)';

SELECT  physical_memory_in_bytes / 1024.0 AS [amount of usable physical memory in the server in KB]
FROM    sys.dm_os_sys_info;

Our SQL servers are all dedicated for SQL server without any other applications.

If I have set up a max server memory, does it mean SQL server can only use to that maximum number, all left is used by operating system?

In SQL 2008 (this was changed in SQL 2012) it means the SQL Server buffer pool can only use the amount of memory specified by the server setting. However other things require memory in SQL Server that are not allocated in the buffer pool. Things like Linked Servers, CLR objects, Extended Stored Procs, and various other things. Make sure you account for those too when setting max server memory so you leave enough not only for the OS, but for non-buffer-pool memory as well.

To see how much non-buffer-pool memory your instance is using you can run this query (from Understanding the VAS Reservation (aka MemToLeave) in SQL Server by Jonathan Kehayias

SELECT type, virtual_memory_committed_kb, multi_pages_kb 
FROM sys.dm_os_memory_clerks 
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1414647

sqlfriends

Posted Friday, February 01, 2013 11:12 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 1,610, Visits: 2,794

Thanks,

What is the difference of the two query above mentioned:
1. SELECT physical_memory_in_use_kb/1024 AS totl_mem_usage FROM sys.dm_os_process_memory AS dm

2. SELECT cntr_value/1024 AS [amount of memory SQL is using in MB]
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Total Server Memory (KB)';

I understand the first one shows only how much memory the cache (buffer cache) is using
what about the first one, I got a number the first one is little bigger than the second one.

For non- buffere_pool memory, the query :
SELECT type, virtual_memory_committed_kb, multi_pages_kb
FROM sys.dm_os_memory_clerks
WHERE virtual_memory_committed_kb > 0 OR multi_pages_kb > 0

I got the result but don't know which one is for non-buffer pool like which one is for linked server?

Thanks

Post #1414772

sqlfriends

Posted Friday, February 01, 2013 11:24 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 1,610, Visits: 2,794

Also how to compare total server memory and target server memory?

If target server memory is much less than total server memory, is it under memory pressure?

Thanks

Post #1414774

SQL Pizza

Posted Friday, February 01, 2013 12:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:14 AM
Points: 116, Visits: 293

Hi sqlfriends,

as you've already learned, memory management in sql server is a very complicated topic. Could you please describe your problem more precise, to give us a chance to better understand your intention. Also it would help us if you'd tell us a bit more bout your config and hw.

Greetz SQL Pizza

PS: You don't need to double post if you click on the edit button Smile

.

Greetz
SQL Pizza

sometimes you dont see the pizza for the toppings...
seek and ya shall find...

Post #1414812

GilaMonster

Posted Friday, February 01, 2013 12:49 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:11 PM
Points: 37,741, Visits: 30,020

sqlfriends (2/1/2013)

If target server memory is much less than total server memory, is it under memory pressure?

Not SQL, but likely the OS. Target lower than total means SQL's been told to reduce it's memory usage by the OS. (or that someone has just changed max server memory)

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1414817

sqlfriends

Posted Friday, February 01, 2013 12:56 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 1,610, Visits: 2,794

Sorry, I mean Total server memory is much lower than target server memory.

Does it indicate a memory pressure?

Post #1414823

sqlfriends

Posted Friday, February 01, 2013 12:57 PM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:13 PM
Points: 1,610, Visits: 2,794

SQL Pizza (2/1/2013)

So far I have not seen a performance issue on our server, but just want to figure out how to trouble shoot and the concepts?

Post #1414824

SQL Pizza

Posted Monday, February 04, 2013 5:11 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 5:14 AM
Points: 116, Visits: 293

sqlfriends (2/1/2013)

Sorry, I mean Total server memory is much lower than target server memory.

Does it indicate a memory pressure?

Ok. Now I understand quite a bit more what you want to know. There are different opinions out there about the behaviour of total server memory and target server memory. Some say if total server memory is much lower than target server memory, it can indicate memory trouble others don't.

AFAIK there are many other counters you can take a look at, gigving you a better clue if you got memory troubles.

For example : Page Life Expectancy should be well above 300 (which are seconds, so they should last longer than 5 Minutes)

SELECT cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] = 'MSSQL$YOURINSTANCE:Buffer Manager'
	AND counter_name = 'page life expectancy'

If the value is smaller there is a lot of paging and unnecessary disk i/o goin on.

The next interessting counter is Lazy Writes/sec. It tells you how often the buffer pool flushed dirty pages to disk. It shoul be near 0.

Look at Free Pages showing you the free space in all list. The value should not be under 640 (5 MB) what would indicate you have memory pressure.

Look at Memory Grants Pending which will tell you if you have processes waiting on workspace memory to execute.

I'm sure there are other counters that could be helpful for analyzing memory pressures, but if memory demands are low, then Total Server Memory will remain much lower than Target Server Memory. Memory pressure is not indicated in this case.

Greetz
SQL Pizza

sometimes you dont see the pizza for the toppings...
seek and ya shall find...

Post #1415237

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions