October 2, 2009 at 7:52 am
we have a sql server that is set to 6.5GB min and max memory (so fixed) but the sqlservr.exe process only shows 95MB. why is this? task manager shows about 4.5GB free, so if it were to expand to the allocated 6.5GB, what is going to happen?
October 2, 2009 at 8:01 am
OLDCHAPPY (10/2/2009)
we have a sql server that is set to 6.5GB min and max memory (so fixed) but the sqlservr.exe process only shows 95MB. why is this? task manager shows about 4.5GB free, so if it were to expand to the allocated 6.5GB, what is going to happen?
task manager isnt really the best tool for seeing sql server memory usage, if you did allow sql server to use what you have suggested. you would not see that reflected in task manager
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 5, 2009 at 2:02 am
so what can be used to see an accurate view of how much memory SQL is using?
if i add up all the processes on the server, with SQL showing 95MB, there is only 4GB free memory leftover. so where is the other 6.4GB for SQL? i don't get it.
October 5, 2009 at 5:34 pm
Use Target Server Memory and Total Server Memory counters under Memory manager object in performance monitor to check the sql server memory usage.
http://sqlserverpedia.com/wiki/Memory_-_Performance_Counters
MJ
October 6, 2009 at 12:07 am
thanks. i'll check.
can someone explain why task manager is wrong?
October 6, 2009 at 1:45 am
I cannot find an article atm to validate my reply, but from my understanding task manager can only see some of the memory allocated to sql server, and the more memory that you have the greater the difference looks.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 6, 2009 at 3:21 am
If you have AWE turned on, then any memory allocated by the AWE API is termed a 'private memory space' by Windows, and is not shown in Task Manager.
In SQL 2000, AWE was only used to get at memory above the 4GB line, but in SQL 2005 and above it enables a number of improvements in memory management, and should be turned on for all instances (apart from Express and Workgroup where AWE is not supported). AWE is always active on 64-bit SQL and the 'AWE Enabled' setting is ignored for 64-bit.
IMHO the fact that Task Manager does not show private memory areas is a weakness in that tool, but MS are content to leave it as it is.
The Process Explorer tool from sysinternals (now part of MS) does give you a better view of what memory is allocated.
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
October 6, 2009 at 3:28 am
thanks for the info, ed. btw, we're running SQL 2005 x64 on Win 2003 x64 server.
October 6, 2009 at 3:55 am
Running x64 means that Task Manager will always mis-represent the amount of memory used by SQL.
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
October 6, 2009 at 5:25 am
i checked Process Explorer and i don't seen anything to reconcile the memory allocated in SQL (6.5GB fixed). i see the working set corresponds to what is show in Window's Task Manager (102MB) but where is the rest? if you can't see memory accurately, how do you know if you've overallocated SQL beyond the available physical memory?
attached is a screen cap of the process explorer info on sql process.
October 7, 2009 at 3:29 pm
Allocating memory doesn't necessarily mean that it will use memory. It simply means that it can. As the data and proc cache fills you will see the amount of memory used by SQL Server go up.
"Beliefs" get in the way of learning.
October 7, 2009 at 5:49 pm
SQL 2000 allocates all memory on startup, but newer version dont.
October 7, 2009 at 6:49 pm
Hi,
We have 16 GB of RAM on the server.Max memory is set to 12 GB , Min memory is set to default value and 4 GB is left for OS.
I'm noticing that Target server memory & Total Server memory are always same and it is 12 GB. Is that means, SQL Server has memory pressure???
We have SQL Server 2005 EE x64 on Windows 2003 EE R2
please advice..
October 8, 2009 at 2:37 am
please check other counters: page life expectansy and buffer cache hit ratio
October 8, 2009 at 3:12 am
See Brad McGehee's post at http://www.sql-server-performance.com/tips/performance_monitor_memory_counter_p1.aspx this has some good background material.
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
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply