January 31, 2013 at 5:46 pm
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
February 1, 2013 at 1:23 am
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
Query Shepherd
February 1, 2013 at 6:43 am
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
February 1, 2013 at 11:12 am
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
February 1, 2013 at 11:24 am
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
February 1, 2013 at 12:45 pm
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 :-).
Greetz
Query Shepherd
February 1, 2013 at 12:49 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 1, 2013 at 12:56 pm
Sorry, I mean Total server memory is much lower than target server memory.
Does it indicate a memory pressure?
February 1, 2013 at 12:57 pm
SQL Pizza (2/1/2013)
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.
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?
February 4, 2013 at 5:11 am
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
Query Shepherd
February 4, 2013 at 5:21 am
sqlfriends (2/1/2013)
Sorry, I mean Total server memory is much lower than target server memory.
No, it means that SQL is ramping up its memory usage.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2013 at 5:24 am
SQL Pizza (2/4/2013)
For example : Page Life Expectancy should be well above 300 (which are seconds, so they should last longer than 5 Minutes)
That was a poor threshold when Microsoft published it, about 6 years ago. 300 is absolutely not a sensible threshold today.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2013 at 5:33 am
Thanks Gail for this nice article. Ok, nevertheless : troubleshooting performance should be a long term process and usually you have to concern different factors and can't make a decision based on one simple counter. "You have to read beetween the lines" :-D!
Greetz sql pizza
Greetz
Query Shepherd
February 4, 2013 at 5:36 am
sqlfriends (2/1/2013)
SQL Pizza (2/1/2013)
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.
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?
http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/ Chapters 1 and 4.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 4, 2013 at 10:51 am
Thanks all. I will download the book to read.
Also Gail, when you say:
"No, it means that SQL is ramping up its memory usage.", I am not sure I understand well about it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply