February 10, 2009 at 6:37 am
I always try to find answer for this but no idea what makes this. Most of the time my sql server box memory reaches 100% utilization. I dont have any other processes running on my boxes except SQL Server. Today when i checked one of the server memory utilization was 98.8% though there was no process running on that machine.
Here is the configuration of my Box.
Inter (R) Xeon(R) CPU
E5450 @ 3.00GHz
3.00 GHz, 20.0 GB RAM
Windows Server 2003, Enterprise x64 Edition. SP2
Also SQL Server has Enterprise Edition.
I really want to know the best configuration and reason why it is doing so and get rid of this.
THanks
February 10, 2009 at 6:53 am
In my experience SQL Server loves memory (especially on servers that are heavily used, since it will do what it can to put pages in memory).
If you feel that it is using more memory of the box than what you think, you do have the ability to set the maximum within SQL Server to something other than the total memory in the server. If you set it to something like 16 GB, that would guarantee that you have 4 GB for other processes and probably would not hurt you in a case like this.
February 10, 2009 at 7:00 am
out of interest, what are your current min and max memory settings?
how many instances of sql?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 10, 2009 at 8:43 am
I have just one instance on each box and the min memory is 0 , max is 18432 and 1024 for each query.
do you thinnk its good?
February 10, 2009 at 8:46 am
You essentially are allowing SQL to take up 18.5 GB out of 20 total and SQL is obviously happy to do so. That is not necessarily bad, but it is possible that the OS may need more than just that 1-1.5 GB of RAM. It obviously varies from situation to situation. Try some different things and see what works best in your world.
February 10, 2009 at 8:59 am
Mike,
Make sure that your SQL Service can Lock Pages in Memory. This will keep SQL from being paged out and causing working set trims from OS memory pressure.
http://technet.microsoft.com/en-us/library/ms190730.aspx
This is highly recommended for 64 bit environments because of how memory scales under the 8TB VAS.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
February 10, 2009 at 9:52 am
The old rules of leaving 1.5-2.5GB for the US definitely applied to 32 bit because that much space was needed for paging out PAE. With 64, I'm not sure what applies, but I'd be curious what the effects were if you tried some differing values, like leaving 2, 3, and 4GB for the OS. Does it run better, less paging, worse SQL performance, etc.
Definitely lock pages in memory
February 10, 2009 at 1:47 pm
Also I have issues with my CPU usage. most of the time in peak hours my CPU usage is 100%, i get frudatrated seeing that every time. Ofcourse there will heavy loads on the database as a result it is 100% usage. Is there a way i can use it effceintly?
I have a question regarding Activity Monitor, When i run single query in single session and check the monitor i see so much of data in there with the same processid i see 8 records for single query. Any one know why it is so? I assume it should be only 1 record for each process.
February 10, 2009 at 10:34 pm
we have sql server 2005 EE x64 on windows 2003 R2 EE. We have 16GB RAM and I set Max memory 12GB and the sql server is taking whole 12GB.We have only one instance on the box.So how can we know if Sql server needs more than 12GB RAM? does it show any errors in the error log? and Iam also getting an alarum free space in virtual memory is down to 10%(In Spotligh).If virtual memory is less where can I check an indication of low virtual memory in Sql server NOT in spotlight?
Regards,
Kotla
February 10, 2009 at 10:44 pm
You can have parallelism, so potentially multiple threads serving one query.
For the memory issues, please start your own thread, but here are a few links:
February 11, 2009 at 1:50 am
kotlas7 (2/10/2009)
we have sql server 2005 EE x64 on windows 2003 R2 EE. We have 16GB RAM and I set Max memory 12GB and the sql server is taking whole 12GB.We have only one instance on the box.So how can we know if Sql server needs more than 12GB RAM? does it show any errors in the error log? and Iam also getting an alarum free space in virtual memory is down to 10%(In Spotligh).If virtual memory is less where can I check an indication of low virtual memory in Sql server NOT in spotlight?Regards,
Kotla
what size is your pagefile
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 11, 2009 at 10:59 am
page file size is default value 2046MB
February 12, 2009 at 11:41 am
Could give me some more information and guidelines about the following
we have sql server 2005 EE x64 on windows 2003 R2 EE. We have 16GB RAM and I set Max memory 12GB and the sql server is taking whole 12GB.We have only one instance on the box.So how can we know if Sql server needs more than 12GB RAM? does it show any errors in the error log? and Iam also getting an alarum free space in virtual memory is down to 10%(In Spotligh).If virtual memory is less where can I check an indication of low virtual memory in Sql server NOT in spotlight?
page file size is default value 2046MB
February 12, 2009 at 1:28 pm
kotlas
have you read the links Steve supplied?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply