SQL Server Performance Forum – Threads Archive
Memory Allocation
Hi, i have a "little" problem with Memory Allocation in SQLSRV 2000 Ent. Ed. My server has 4 GB of ram (2.5 GB free).. i have set up SQLServer to use a fixed memory size (3710 MB)but SQLServer does not use more than 1 GB. I also check the "Reserve phisical memory for SQL Server" and the minimum query memory is set to 8192 KB.
My DB is large approximately 7.5 GB and often i see more and more disk read operation but no increment of used ram! I believe that SQLSRV would have to use more ram !!!!! You have suggestions for me? My hardware:
– DELL 4650 Dual Xeon 2.8 GHz with 4GB of RAM
– 2 distinct array with RAID 1 in SCSI Hot Swap HD S.O.:
– Win 2000 Server SP4 SQL:
SQL Server 2000 Enterprise Edition SP3
Thanks!
(and sorry for my poor english) —
Stefano
Are you using the /3gb switch in the boot.ini file? What is your current buffer cache hit ratio? —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
are you running anything else on the server other than SQL? leave SQL to use memory dynamically if it is the only thing on the server
otherwise configure SQL to use no more than 2GB, leaving the rest for the OS your database is not that big, especially compared to the RAM in the box. You should not need to use the /3gb switch, since your database isn’t that large. You may find that it is not SQL that is causing the disk activity (unless there is a lot of write activity in the database) I’d start with putting everything back to their defaults, and then running performance monitor and profiler to work out a) the resources used in the server and b) any poor performing queries. Cheers
Twan
I’d definately agree with Twan, set things back to their defaults (dynamic memory,no reservations etc) and then recycle the service and start monitoring again – it sounds like you some misconfiguration. BTW you need Win2000 Advanced Server to use the /3GB switch so even though you have the Enterprise Edition of SQL Server, you won’t get it to user more than 2GB if on Win2000 Standard so there’s no point setting the max server memory. HTH Jasper Smith
Now I say what we have made:<br /><br />As you will have understood, every modification on the memory settings did not have effect.<br />For curiosity they have gone to see the virtual memory settings…<br />the dimension of the paging file was set up to 2 GB.. I have increased it to 4 GB and magically SQLSRV has begun to allocate memory up to 1.7 GB!<br /><br />Finally something moves! <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Not satisfied of this, I have enabled the AWE Memory use.. and now.. SQL Server use 3 GB of ram!<br /><br /><br />P.S.:<br />the buffer cache hit ratio, is before that after, it was to the 99.5%<br />I do not use the /3GB<br />not there were particular problems with not performant queries<br /><br /><br />In any case thanks for your suggestions<br /><br />–<br />Stefano
Still Twan question is valid. Is there others appications running?.
SQLServer practically don’t use swap file, execpt if tuning is necessary or other application are running. Luis Martin
Moderator
SQL-Server-Performance.com
Hi Luis, My server is dedicated to SQL.
The High Disk activity is when I run some unfrequent queries. It is strange that, having 700MB of free ram, SQL decides to read on HD and not to use the memory in the case that query comes still executed… and it is also strange that SQL has begun to allot RAM after to have modified the virtual memory settings. My problem was not on the performances, but on the missed allocation of ram. —
Stefano
Hello, I think your problem isn’t memory configuration. It’s DataBase configuration.
I need more information to solve your problem, but you should start your troubleshooting trying to rebuilding your indices in your tables. It’s the first think to help you. To be more exactly, you need to trace everything before, but you should criate view instead of SQL Queries. If you need to know more Bye
quote:Originally posted by pinosoft
Hi Luis,
My server is dedicated to SQL.
The High Disk activity is when I run some unfrequent queries. It is strange that, having 700MB of free ram, SQL decides to read on HD and not to use the memory in the case that query comes still executed… and it is also strange that SQL has begun to allot RAM after to have modified the virtual memory settings. My problem was not on the performances, but on the missed allocation of ram. —
Stefano
The High Disk activity is when I run some unfrequent queries. It is strange that, having 700MB of free ram, SQL decides to read on HD and not to use the memory in the case that query comes still executed… and it is also strange that SQL has begun to allot RAM after to have modified the virtual memory settings. My problem was not on the performances, but on the missed allocation of ram. —
Stefano
Jhnegrao, Welcome to the forum.
One suggestion: take a look of last post date from Stefano. I’m sure you can help with other posts more close in time. Thanks,
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS†with no warranties for accuracy.
Have you captured the information using PERFORMANCE MONITOR during resource intensive queries and free time of SQL server. http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10495 fyi.
http://www.microsoft.com/downloads/…5B-84E4-4F24-8D65-CB53442D9E19&displaylang=en benchmark tool for your experimentation. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>