August 13, 2003 at 7:57 am
Hey there,
I just got a new server. It has just 4.512 Gigs of memory in it.
What do I have to do in order to get Sql server to use it all?
I added the following two switches in the boot.ini file:
/3GB and /PAE
When I restarted Sql server service and looked at the task manager, 4 gigs of memory were allocated, but when I look at the individual processes, sqlserver.exe is only using 70Megs. As I run more queries it is climbing, but not very fast at all.
Anyone know of a good way to ensure that sql server is using as much memory as possible?
Edited by - jraha on 08/13/2003 07:58:15 AM
August 13, 2003 at 9:36 am
SQL Server 2000 dynamically acquires and frees memory as needed. It is typically not necessary for an administrator to specify how much memory should be allocated to SQL Server, although the option still exists and is required in some environments.
August 13, 2003 at 9:45 am
You have to enable 'AWE' in SQL Server to use memoery that is beyond 4GB.
August 13, 2003 at 10:34 am
I didn't mention it, but I had already run the following sp_configure options and restarted the sql server service:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_Configure 'awe enable', 1
RECONFIGURE
GO
sp_configure 'max server memory', 4096
RECONFIGURE
GO
I'm using SQL Server 2000 Developer's Edition on a Windows 2000 Advanced Server.
But still I can't seem to get the process to use more than 80 Megs of memory.
Edited by - jraha on 08/13/2003 10:34:23 AM
August 13, 2003 at 11:35 am
How large are your databases in size? As Larry said, SQL Server will allocate the memory when it needs. You may wait to go back to check the usage after certain period of time/days.
Or could it be physical hardware problem in those memory?
August 14, 2003 at 5:13 am
You will need W2K Advanced Server to access over 2GB of memory.
In W2K normal server, it will not object if you use /3GB, etc, but will not give you any extra memory above the 2GB line.
All information provided is a personal opinion that may not match reality.
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
August 14, 2003 at 6:43 am
I had a similar scenario last week. Task Manager seems to get it wrong for individual processes when you get over a certain amount of memory.
I used the undocumented/unsupported/unrecommended DBCC MEMUSAGE to confirm that SQL was indeed using a lot more than 70mb (closer to 3gb in fact).
Cheers,
- Mark
Cheers,
- Mark
August 14, 2003 at 9:00 am
In general, Task Manager's Process view is not a good place to look for the memory that a process uses. Just add all the values and compare that to the total Mem Usage on the Perfomance tab.
For my system, there is a difference of a mere 80 MB. If I'm not mistaken, things like memory allocated by DLL is not (necessarily) taken into account.
August 14, 2003 at 9:08 am
Use performance monitor or select from sysperfinfo for accurate memory usage.
August 15, 2003 at 2:14 pm
Mccork,
What am I looking at when I run "DBCC MEMUSAGE"? dbid and objectid I got, but units of measurement is "Buffers", and what's "Dirty"?
Signature is NULL
August 15, 2003 at 7:34 pm
quote:
What am I looking at when I run "DBCC MEMUSAGE"? dbid and objectid I got, but units of measurement is "Buffers", and what's "Dirty"?
One way I get an ESTIMATE of memory usage by:
a. Getting the "Total Buffer Pool Page Count" figure from DBCC MEMUSAGE. (Don't worry about the "dirty" figure.
b. Getting the final sum figure from:
SELECT cacheobjtype,SUM(pagesused) FROM master.dbo.syscacheobjects
GROUP BY cacheobjtype
COMPUTE SUM(SUM(pagesused))
c. Add results a & b together and multiply by 8192.
Cheers,
- Mark
Cheers,
- Mark
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply