SQL 2005 memory usage 1

[ptheriault]

I have a SQL 2005 Enterprise install on x64 server. Both SQL and OS are 64 bit. I have 8GB of physical memory onboard. I have set the min mem to 3 gb and max mem to 7 gb. I have conifmed this with sp_configure. However, When I run

SELECT * FROM sys.dm_os_performance_counters
WHERE object_name like 'SQLServer:Mem%'

I get a value of 6393960 KB for Total Server memory.
also The SQL instance is not using more than 6.09 GB it thinks is installed.
Because this is a 64 bit I don't need to enable AWE.
Is there something else I'm missing? Does SQL take reserve 1 GB for other process with x64?



- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

It might be that 6 GB of memory is all that SQL needs right now or thinks it needs right now. After all, you did set it to dynamically allocate.

Run Perf Mon (A windows tool) and TaskManager to see what memory the server currently has in use. This will let you know if you have a leak somewhere or bad memory or if it's just sitting out there unpaged and unused.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Total mem usage on the server is 7.4 gb. SQL just won't go beyond the 6 gb limit. I've got my page file set at 12 gb. I'm not seeing any performance issues yet, but we will be adding more users to this server soon and I need to make sure all is well before that happens. I 've got more memory on the way for this server too. I am going to put 32gb in it. Since this is my first 2005 x86 server I wasn't sure it there is some setting I'm missing. Or if x86 will grab a bit of memory for itself. I had a dell server in the past that would grab a gig of mem to use for it's tools.

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

AHA! Just found the following link which may explain your problem:

http://www.dell.com/downloads/global/power/ps3q06-20060270-Sankaran.pdf

AWE is not necessary on the 64-bit versions of SQL Server 2005 because virtual address space is not limited to 4 GB. However, the Windows account under which the database engine is running should still have "Lock Pages in Memory" permission.

Have you checked this permission yet?

Also, this link mentions Full Text Indexing taking up or reserving some memory:

http://sqljunkies.com/WebLog/simons...ing_with_SQL_2005___Memory_configuration.aspx

Does this help you out any?





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Cat,
Thanks for the help. It wasn't the lock pages in memory, but when I opened the Lock pages in memory info in BOL. I found this.

Maximize Data Throughput for Network Application
If the Maximize data throughput for network application option is selected in Network Connection, the operating system gives priority to applications that perform buffered input/output (I/O) operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation.

That option is checked on my server. I'll to investigate it further. But at least I know what is going on now.

Thanks for your help

- Paul
- Database performance looks fine, it must be the Network!

 

[Catadmin]

NP. Glad you were able to find something.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"