February 6, 2008 at 3:14 pm
I know I can run DBCC MEMORYSTATUS to view how much memory SQL Server is using on a 64-bit server, but how do I view how much memory the other processes are consuming?
I do not believe the Perfmon counters are accurate when using AWE or 64-bit memory, at least in terms of memory consumption per process so how do we see this information. I have a feeling it is not possible.
Here is the problem. We have a 64-bit server with 32GB of memory. SQL Server is configured to use no more the 20GB. It currently is using only 15GB. The O/S is reporting 28.3GB used out of 32GB, which means some other processes are consuming 28.3GB - 15GB = 13.3GB. How can I determine what is using the 13.3GB of memory?
Thanks, Dave
February 7, 2008 at 5:29 pm
Yo have memory related DMVs now in 2005.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 8, 2008 at 2:39 am
Windows Task Manager shows true results on a 64-bit server. If you want more details than Task Manager gives, download Process Monitor from MS (it is a former Sysinternals tool).
Windows Task Manager on a 32-bit system knows nothing about PAE, so it cannot report on SQL Server use of AWE memory.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 8, 2008 at 9:40 am
I forgot about the DMVs. Thanks.
Good to know Task Manager works with 64bit.
Thanks, Dave
February 8, 2008 at 1:15 pm
With AWE enabled, you will never really know how much memory is "used". You can know how much is allocated if you use max server memory.
Without AWE enabled, yes task manager will report how much memory SQL has allocated, but it is not a true representation to as how much memory SQL Server is currently using. As SQL requires memory, it will continue to consume and consume without ever giving it back up. It will only release memory back to the O/S when another application requires it.
Moral of the story, (to my knowledge) there is no legit way to TRULY know how much memory is being utilized by SQL Server. I've went through many discussions with Microsoft on this and have yet to ever get a real, solid answer. I always get the response "memory in SQL server is a black art." Yeah, thanks, that helps Microsoft. The other answer I get is that DBCC MEMORYSTATUS is about as close as you can to get to knowing how much memory is in use, which is never completely accurate.
I've played around with the few memory DMV's, and never made any progress. If someone has a solid query that can accurately report the memory usage, you will be my best friend.
March 12, 2008 at 7:41 am
Here's a query I use. I just picks up the Windows perf counter values around SQL Server.
select counter_name, cntr_value / 1024.0 / 1024.0 as GB, *
from master..sysperfinfo
where object_name = 'SQLServer:Memory Manager'
and counter_name in ('Target Server Memory (KB)', 'Total Server Memory (KB)')
I haven't looked into the DMVs in 2005 yet, but I'm guessing there is something like this available.
March 13, 2008 at 10:36 am
We have almost the same situation. We have a cluster on x64 with 32 GB memory and I have SQL Server capped at 26 GB. Our server team says that SQL Server is using all but 1 or 2 GB thus starving other applications, however every time I look on the server I see a little over 26 GB. I have a query similar to Jamie's (yours is better though) that shows SQL Server only using 26 GB so I'd like to know what's really going on. Are the counters not working properly?
March 13, 2008 at 11:18 pm
Are you sure task manager is accurate for 64 bit? I have a std edition server using 6 GB of RAM and task manager is reporting 109 meg? Is it possible it is only accurate for EE?
Joe
March 14, 2008 at 12:48 am
there is a nice script overhere:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=65&messageid=107424
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply