June 29, 2012 at 6:56 am
Hi
Here i have one doubt.
We are having 28gb ram in that after installing the sql server it is occupying 10 gb Ram.
here i want to set this to 2gb.
How can we do and may i know the procedure please.
June 29, 2012 at 7:19 am
Please go to Properties of the Instance and change the Max Memory settings to 2048 MB. Then it will consume only 2 GB.
Tell me if you need any more assistance.
June 29, 2012 at 8:01 am
Thank you very much
June 29, 2012 at 8:17 am
Okasari instance restart cheyali.. So please restart you change the Max Memory settings
June 29, 2012 at 8:46 am
No, no, no, no!!!!
SQL is designed to use as much memory as it can. It's an optimisation. It makes the query processing more efficient and reduces IO load. 2GB of memory for SQL will cripple if unless there's a very small number of users and very small databases.
Please read chapter 4 of this: http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
p.s. There's no need to restart after changing max server memorry.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 29, 2012 at 9:58 am
Letting SQL Server use as much memory as possible for the buffer pool (max memory setting only controls buffer pool size) while allowing for the SQL Server processes themselves, the OS and other programs to have enough memory to do their work without paging is good advice. If SQL Server was using 10GB reducing the max memory setting to 2GB will certainly cause a performance degradation for your SQL Server, and maybe even your server in general. If you can spare the extra 8GB then I would leave things alone. That said, it is critical that you set max memory explicitly so SQL Server will not take all available memory on the server...just read the chapter (or better yet the whole book) Gail posted a link to.
If you do decide to set max memory one way or the other, just be careful when doing it on a live system during peak times. While changing the setting does not require a server or service restart, you have to run RECONFIGURE to get the change to take effect and that will cause a flush of your proc cache which can cause all kinds of other issues.
This article applies to SQL 2005 but I just tested on a SQL 2008 R2 instance and the behavior is the same. From http://support.microsoft.com/kb/917828:
The whole procedure cache is cleared if one of the following server options is changed by the RECONFIGURE statement:
- cross db ownership chaining
- index create memory (KB)
- remote query timeout (s)
- user options
- max text repl size (B)
- cost threshold for parallelism
- max degree of parallelism
- min memory per query (KB)
- query wait (s)
- min server memory (MB)
- max server memory (MB)
- query governor cost limit
edit: spelling
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2012 at 10:37 am
Related question: Is leaving the default memory settings in place sometimes better, or is it always better to manually configure the memory settings?
If a server hosts a single instance of SQL Server and is used for nothing else, is leaving the default settings and letting SQL decide for itself how much RAM to use fine?
Is manually setting the memory more for when there are multiple instances present, or other applications/services running on the server, to prevent paging when other applications need more RAM?
June 29, 2012 at 10:44 am
dan-572483 (6/29/2012)
Related question: Is leaving the default memory settings in place sometimes better, or is it always better to manually configure the memory settings?
It depends somewhat on your installation, e.g. 32 or 64 bit and how much RAM you have. In general though it is good practice to set the max memory as one of the first things you do to a new installation.
If a server hosts a single instance of SQL Server and is used for nothing else, is leaving the default settings and letting SQL decide for itself how much RAM to use fine?
Again, it depends on the installation, but on a 64 bit server it is very dangerous to leave max memory at the default setting, which say to allow SQL Server to use as much memory as it can up to the amount installed in the server. Leaving SQL Server unchecked in this area can lead to serious server instability should SQL Server begin to encroach on memory the OS and other programs need to function smoothly, i.e. without the OS having to page memory to disk.
Is manually setting the memory more for when there are multiple instances present, or other applications/services running on the server, to prevent paging when other applications need more RAM?
It can be important whether there is one instance, or many installed on the server.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2012 at 11:24 am
dan-572483 (6/29/2012)
Related question: Is leaving the default memory settings in place sometimes better, or is it always better to manually configure the memory settings?
In most cases better to configure them. Otherwise SQL and Windows sit in a 'low memory/release memory - high memory/allocate memory' loop (you can watch that in the ring buffers)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 30, 2012 at 3:44 am
For suppose we are having 3 instances.
Here i want to change the memory settings for all the instances or we need to change in one instance.
June 30, 2012 at 4:18 am
gantavasu (6/30/2012)
For suppose we are having 3 instances.Here i want to change the memory settings for all the instances or we need to change in one instance.
Hard to say without know the usage patterns of the three instances. You could start by setting each to 8GB (total 24GB for all three SQL Server instances) and then momitor performance on the instances adjusting each up or down as appropriate for the work loads on each instance.
June 30, 2012 at 4:19 am
All three. Instance's memory settings are independent, so if you want to change 3 instance's memory settings, you have to do it individually in each one.
Please, read the book chapter I recommended before you make any changes. Reducing SQL's memory to 2GB is very likely to hinder the performance of any apps that use those instances.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 1, 2012 at 2:15 am
gantavasu (6/29/2012)
We are having 28gb ram in that after installing the sql server it is occupying 10 gb Ram.here i want to set this to 2gb.
Wow, what other application do you have on the server apart from SQL server that will use that remaining RAM? :w00t:
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 2, 2012 at 1:15 pm
Convert that big phat server to VM, run each seperate instance as a individual guest.
July 2, 2012 at 2:38 pm
How much of a factor is database size? If instance1 has 1 user database less than 1GB and instance2 has multiple DBs totalling 100 GB I can't imagine that instance1 would need nearly as much as instance2, although you'd also have to consider the number of users and how the DBs are used.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply