Amount of RAM required ?

We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
SQL Server standard edition can only access 2GB of ram. However, I would
make sure the operating system and other programs have enough Ram. So
depending on other software buy three or four GB of ram and set SQL Servers
min and max memory to 2GB so it takes it up front.
"Jason" <anonymous@.discussions.microsoft.com> wrote in message
news:0a2601c59bb6$38a863c0$a601280a@.phx.gbl...
> We are going to replace the old SQL Server 2000 to a new
> Hardware running Windows 2003 Server.
> We are planning how much memory should be used. At the
> present monent, we are using 2GB RAM and the performance
> is OK. We find that the Average RAM and Peak RAM used is
> around 2000 MB. According to Network Administrator, it is
> because SQL uses all memory available for caching /
> performance optimisation.
> Is it necessary for us to purchase more RAM for the new
> server ? Is it possible for SQL Server 2000 Standard to
> access more than 2 GB RAM ?
> Thanks
|||did you hear "Danny" <someone@.nowhere.com> say in
news:wtyJe.1283$1S.438@.trnddc04:

> SQL Server standard edition can only access 2GB of ram. However, I
> would make sure the operating system and other programs have enough
> Ram. So depending on other software buy three or four GB of ram and
> set SQL Servers min and max memory to 2GB so it takes it up front.
ditto. I use Standard in the office exclusively (about 18 instances) and
I only put more than 4Gb in the box if I am installing a second instance
of SQL on the same box. If you have extra money to spend, you may want to
consider additional processors. SQL makes very good use of the additional
processors.
Neil MacMurchy
http://spaces.msn.com/members/neilmacmurchy
http://spaces.msn.com/members/mctblogs
|||It's impossible to say whether you need more memory or not without a few
metrics. A couple perfmon counters you should look at are the buffer
cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and
the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server
address space fills up with stuff and will remain filled with stuff
until it needs to free up the memory for another app, the OS or more
recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests
are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to
remain in RAM before needing to be swapped out to disk to free up the
memory for other apps or data. It's measured in seconds and I like to
make sure my servers have pages staying in RAM for at least 5 minutes on
average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the
memory pressure on a SQL server, will give you a fairly good idea of
whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than
2G. With different editions, SQL Server 2000 can handle up to 64G
(http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Jason wrote:

>We are going to replace the old SQL Server 2000 to a new
>Hardware running Windows 2003 Server.
>We are planning how much memory should be used. At the
>present monent, we are using 2GB RAM and the performance
>is OK. We find that the Average RAM and Peak RAM used is
>around 2000 MB. According to Network Administrator, it is
>because SQL uses all memory available for caching /
>performance optimisation.
>Is it necessary for us to purchase more RAM for the new
>server ? Is it possible for SQL Server 2000 Standard to
>access more than 2 GB RAM ?
>Thanks
>
|||Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
|||Jason,
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
SQL Server Standard edition can utilize 2 GB memory for each instance maximum. So 2 GB only will be utilized for each instance. Incase
1 instance is down then 2 GB allocated for that instance will be free.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
Looks like you have kept the memory paramter static (Fixed). In this case 2 GB will be reserved to sql server and will not be shared across for other applications.
USE the Perfmon -- Memory -- availableMBytes counter to get the exact value during peak time.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
If your application is less processor intensive then you could go for 2 CPU servers. Again run the Perfmon and identify the CPU usage. If the CPU usage is <50% with
2 CPU duing peak then you do not have any CPU issue.
Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
Thanks
Hari
SQL Server MVP
"Jason" <anonymous@.discussions.microsoft.com> wrote in message news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
|||Dear Hari,
Thank you for your advice.
1) I haven't installed more than 1 instance on a SQL Server. It is just for my interest only. I want to confirm I have followed what you mean = Each instance can be allocated 2 GB and if that instance is down, that 2GB will be of no use to other instances ?
2) NO. All of the SQL Servers RAM is dynamically allocated. I am wondering when we get the new Server, maybe we will assign 2.5GB for each Virtual Server. And I can assign 2GB to each SQL Server static. I would like to know if we statically allocate RAM, should I choose Minimum = 2GB and Maximum = 2GB ?
3) My boss just wonders why in the past for each SQL Server, at least 2 CPU are required, now for 3 Virtual Servers, only 2 processors are required. Anyhow, we have to look into the Virtual Server requirement and it is recommended by them. On the other hand, we are now using 20 CALs for each SQL Server, if we reduce the number of CPU of 1 SQL Server from 4 to 2, can we get any saving in licensing ?
Thanks
Jason
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:OyF9IpAnFHA.2852@.TK2MSFTNGP15.phx.gbl...
Jason,
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
SQL Server Standard edition can utilize 2 GB memory for each instance maximum. So 2 GB only will be utilized for each instance. Incase
1 instance is down then 2 GB allocated for that instance will be free.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
Looks like you have kept the memory paramter static (Fixed). In this case 2 GB will be reserved to sql server and will not be shared across for other applications.
USE the Perfmon -- Memory -- availableMBytes counter to get the exact value during peak time.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
If your application is less processor intensive then you could go for 2 CPU servers. Again run the Perfmon and identify the CPU usage. If the CPU usage is <50% with
2 CPU duing peak then you do not have any CPU issue.
Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
Thanks
Hari
SQL Server MVP
"Jason" <anonymous@.discussions.microsoft.com> wrote in message news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks
|||Jason wrote:

> Dear Hari,
> Thank you for your advice.
> 1) I haven't installed more than 1 instance on a SQL Server. It is
> just for my interest only. I want to confirm I have followed what you
> mean = Each instance can be allocated 2 GB and if that instance is
> down, that 2GB will be of no use to other instances ?
No. If that 2GB is freed up it can be used by any application
(including other SQL instances). Configuring a SQL instance to only use
a maximum of 2GB of RAM doesn't reserve that physical memory for the exe
(unless it is running and you've turned off the standard dynamic memory
management). I think what Hari is trying to point out here is that if
the SQL instance is only using a max of 2GB then that is how much memory
will be available to other apps if that SQL instance shuts down.

> 2) NO. All of the SQL Servers RAM is dynamically allocated. I am
> wondering when we get the new Server, maybe we will assign 2.5GB for
> each Virtual Server. And I can assign 2GB to each SQL Server static.
> I would like to know if we statically allocate RAM, should I choose
> Minimum = 2GB and Maximum = 2GB ?
Personally, if you're talking about < 4GB of RAM then I would leave all
the instances set to allocate memory dynamically. Unless, one SQL
instance is particularly important and is having to fight for its memory
(eg. a production instance fighting with UAT & development instances for
the memory it needs), in which case I would still let it allocate memory
dynamically but would set a minimum server memory for that important
server of, say, 2GB. If you enable the /3GB switch in boot.ini then
each instance can grab up to 3GB of RAM if needed, but with the dynamic
memory management they will release RAM, if necessary, when other apps
make requests for it and there's not enough free.

> 3) My boss just wonders why in the past for each SQL Server, at least
> 2 CPU are required, now for 3 Virtual Servers, only 2 processors are
> required. Anyhow, we have to look into the Virtual Server requirement
> and it is recommended by them. On the other hand, we are now using 20
> CALs for each SQL Server, if we reduce the number of CPU of 1 SQL
> Server from 4 to 2, can we get any saving in licensing ?
There is a difference between a virtual server and a named SQL
instance. Virtual servers relate to SQL Server running on a Windows
cluster using Microsoft Clustering Services (MSCS). I think you meant
to say "SQL instance" rather than "virtual server".
It's possible that the CPUs you buy today are faster than the CPUs that
were in your previous boxes, in which case you may be able to get away
with fewer physical CPUs in your new box. Also, hyper-threading (which
may not have been an attribute of your old CPUs) may make a difference
to the maximum SQL load your new CPUs can handle. (All of this is
purely conjecture because I don't know all your details.) You should
keep an eye on the Processor perfmon counters on your new box as you
start to add load to make sure the fewer CPUs are up to the job. As a
general rule of thumb though, SQL Server is typically a CPU bound
application so you shouldn't skimp on the processors if you can afford it.
In terms of licensing, you should talk to you friendly Microsoft
licensing person because it's a rather convoluted area that not many
people are on top of (myself included). But my understand of the client
CALs is that 1 client CAL will allow 1 client computer to connect to an
number of purchased SQL server instances on your network (or should that
be MS Domain?). If you're licensed by client CALs then the number of
CPUs in each server doesn't make any difference - that only comes into
play when you're license by "per processor". I think that's right but
you should check that with Microsoft.
Hope that helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
[vbcol=seagreen]
> Thanks
> Jason
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com
> <mailto:hari_prasad_k@.hotmail.com>> wrote in message
> news:OyF9IpAnFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Jason,
> 1) Does it mean that each instance can access 2GB. In other
> words, if there are 3 instances installed, each of them can access
> 2GB or 6GB in total.
> SQL Server Standard edition can utilize 2 GB memory for each
> instance maximum. So 2 GB only will be utilized for each instance.
> Incase
> 1 instance is down then 2 GB allocated for that instance will be
> free.
> 2) RAM Required - We have asked a consultant for Server
> Virtualization / Consolidation. Their suggestion for RAM usage is
> 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we
> know that two of the SQL Servers are under utilized BUT their
> report tells us that they are using 2GB.
> Looks like you have kept the memory paramter static (Fixed). In
> this case 2 GB will be reserved to sql server and will not be
> shared across for other applications.
> USE the Perfmon -- Memory -- availableMBytes counter to get the
> exact value during peak time.
> 3) CPU Required - They say that we only need a dual CPU server.
> In the past, one of the SQL Server uses 4 processors while the
> other 2 are 2 processor servers.
> If your application is less processor intensive then you could go
> for 2 CPU servers. Again run the Perfmon and identify the CPU
> usage. If the CPU usage is <50% with
> 2 CPU duing peak then you do not have any CPU issue.
> Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Jason" <anonymous@.discussions..microsoft.com
> <mailto:anonymous@.discussions.microsoft.com>> wrote in message
> news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
> Dear Mike,
> Thank you for your advice.
> I would like to ask 3 questions:
> 1) Does it mean that each instance can access 2GB. In other
> words, if there are 3 instances installed, each of them can
> access 2GB or 6GB in total.
> 2) RAM Required - We have asked a consultant for Server
> Virtualization / Consolidation. Their suggestion for RAM
> usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers).
> However, we know that two of the SQL Servers are under
> utilized BUT their report tells us that they are using 2GB.
> 3) CPU Required - They say that we only need a dual CPU
> server. In the past, one of the SQL Server uses 4 processors
> while the other 2 are 2 processor servers.
> Any suggestion is highly appreciated.
>
> "Mike Hodgson" <mike.hodgson@.mallesons.nospam.com
> <mailto:mike.hodgson@.mallesons.nospam.com>> wrote in
> message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
> It's impossible to say whether you need more memory or not
> without a few metrics. A couple perfmon counters you
> should look at are the buffer cache hit ratio
> (SQLServer:Buffer Manager | Buffer cache hit ratio) and
> the page life expectancy (SQLServer:Buffer Manager | Page
> life expectancy).
> SQL Server will cache data pages that are accessed, so the
> SQL Server address space fills up with stuff and will
> remain filled with stuff until it needs to free up the
> memory for another app, the OS or more recently accessed
> data pages.
> The cache hit ratio will tell you what percentage of your
> data requests are satisfied from RAM (should be pretty
> high, like 99+ percent).
> The page life expectancy is how long SQL Server expects a
> data page to remain in RAM before needing to be swapped
> out to disk to free up the memory for other apps or data.
> It's measured in seconds and I like to make sure my
> servers have pages staying in RAM for at least 5 minutes
> on average, preferable an hour or more.
> Those two counters, while they won't give you a complete
> picture of the memory pressure on a SQL server, will give
> you a fairly good idea of whether or not you need to think
> about increasing physical RAM in a box.
> And, yes, SQL Server is capable of managing a good deal
> more memory than 2G. With different editions, SQL Server
> 2000 can handle up to 64G
> (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
> --
> *mike hodgson*
> blog: http://sqlnerd.blogspot.com
>
> Jason wrote:
|||Hi,
from a licensing point of view...
if you have processors licenses for SQL Server and you want to use it in Virtual Server you have to pay ONLY for physical processors on the server.
So if you have 2 processors in the server, you pay 2 proc. licenses for SQL Server and you can use it in all your guest os.
You only have to pay for the OS used in the guest, not for SQL.
if you have CALs, its client access licenses, so the licenses are by user, and again there is no impact in your guests systems. 1 CAL give access to the user to all your SQL Servers installations.
And now, with dual core processors, you can setup a Dual Opteron Dual Core server (so windows see 4 processors) and you pay your SQL Server for only the 2 physical processors.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:%23FlBIsXnFHA.3572@.TK2MSFTNGP09.phx.gbl...
Jason wrote:
Dear Hari,
Thank you for your advice.
1) I haven't installed more than 1 instance on a SQL Server. It is just for my interest only. I want to confirm I have followed what you mean = Each instance can be allocated 2 GB and if that instance is down, that 2GB will be of no use to other instances ?
No. If that 2GB is freed up it can be used by any application (including other SQL instances). Configuring a SQL instance to only use a maximum of 2GB of RAM doesn't reserve that physical memory for the exe (unless it is running and you've turned off the standard dynamic memory management). I think what Hari is trying to point out here is that if the SQL instance is only using a max of 2GB then that is how much memory will be available to other apps if that SQL instance shuts down.
2) NO. All of the SQL Servers RAM is dynamically allocated. I am wondering when we get the new Server, maybe we will assign 2.5GB for each Virtual Server. And I can assign 2GB to each SQL Server static. I would like to know if we statically allocate RAM, should I choose Minimum = 2GB and Maximum = 2GB ?
Personally, if you're talking about < 4GB of RAM then I would leave all the instances set to allocate memory dynamically. Unless, one SQL instance is particularly important and is having to fight for its memory (eg. a production instance fighting with UAT & development instances for the memory it needs), in which case I would still let it allocate memory dynamically but would set a minimum server memory for that important server of, say, 2GB. If you enable the /3GB switch in boot.ini then each instance can grab up to 3GB of RAM if needed, but with the dynamic memory management they will release RAM, if necessary, when other apps make requests for it and there's not enough free.
3) My boss just wonders why in the past for each SQL Server, at least 2 CPU are required, now for 3 Virtual Servers, only 2 processors are required. Anyhow, we have to look into the Virtual Server requirement and it is recommended by them. On the other hand, we are now using 20 CALs for each SQL Server, if we reduce the number of CPU of 1 SQL Server from 4 to 2, can we get any saving in licensing ?
There is a difference between a virtual server and a named SQL instance. Virtual servers relate to SQL Server running on a Windows cluster using Microsoft Clustering Services (MSCS). I think you meant to say "SQL instance" rather than "virtual server".
It's possible that the CPUs you buy today are faster than the CPUs that were in your previous boxes, in which case you may be able to get away with fewer physical CPUs in your new box. Also, hyper-threading (which may not have been an attribute of your old CPUs) may make a difference to the maximum SQL load your new CPUs can handle. (All of this is purely conjecture because I don't know all your details.) You should keep an eye on the Processor perfmon counters on your new box as you start to add load to make sure the fewer CPUs are up to the job. As a general rule of thumb though, SQL Server is typically a CPU bound application so you shouldn't skimp on the processors if you can afford it.
In terms of licensing, you should talk to you friendly Microsoft licensing person because it's a rather convoluted area that not many people are on top of (myself included). But my understand of the client CALs is that 1 client CAL will allow 1 client computer to connect to an number of purchased SQL server instances on your network (or should that be MS Domain?). If you're licensed by client CALs then the number of CPUs in each server doesn't make any difference - that only comes into play when you're license by "per processor". I think that's right but you should check that with Microsoft.
Hope that helps.
mike hodgson
blog: http://sqlnerd.blogspot.com
Thanks
Jason
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:OyF9IpAnFHA.2852@.TK2MSFTNGP15.phx.gbl...
Jason,
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
SQL Server Standard edition can utilize 2 GB memory for each instance maximum. So 2 GB only will be utilized for each instance. Incase
1 instance is down then 2 GB allocated for that instance will be free.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
Looks like you have kept the memory paramter static (Fixed). In this case 2 GB will be reserved to sql server and will not be shared across for other applications.
USE the Perfmon -- Memory -- availableMBytes counter to get the exact value during peak time.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
If your application is less processor intensive then you could go for 2 CPU servers. Again run the Perfmon and identify the CPU usage. If the CPU usage is <50% with
2 CPU duing peak then you do not have any CPU issue.
Measure cpu using the counter -- PROCESSOR -- % PROCESSOR TIME.
Thanks
Hari
SQL Server MVP
"Jason" <anonymous@.discussions..microsoft.com> wrote in message news:%23Qip5aAnFHA.3648@.TK2MSFTNGP10.phx.gbl...
Dear Mike,
Thank you for your advice.
I would like to ask 3 questions:
1) Does it mean that each instance can access 2GB. In other words, if there are 3 instances installed, each of them can access 2GB or 6GB in total.
2) RAM Required - We have asked a consultant for Server Virtualization / Consolidation. Their suggestion for RAM usage is 8GB just 2GB + 2GB + 2GB (As we have 3 SQL Servers). However, we know that two of the SQL Servers are under utilized BUT their report tells us that they are using 2GB.
3) CPU Required - They say that we only need a dual CPU server. In the past, one of the SQL Server uses 4 processors while the other 2 are 2 processor servers.
Any suggestion is highly appreciated.
"Mike Hodgson" <mike.hodgson@.mallesons.nospam.com> wrote in message news:uRBjwE8mFHA.1968@.TK2MSFTNGP14.phx.gbl...
It's impossible to say whether you need more memory or not without a few metrics. A couple perfmon counters you should look at are the buffer cache hit ratio (SQLServer:Buffer Manager | Buffer cache hit ratio) and the page life expectancy (SQLServer:Buffer Manager | Page life expectancy).
SQL Server will cache data pages that are accessed, so the SQL Server address space fills up with stuff and will remain filled with stuff until it needs to free up the memory for another app, the OS or more recently accessed data pages.
The cache hit ratio will tell you what percentage of your data requests are satisfied from RAM (should be pretty high, like 99+ percent).
The page life expectancy is how long SQL Server expects a data page to remain in RAM before needing to be swapped out to disk to free up the memory for other apps or data. It's measured in seconds and I like to make sure my servers have pages staying in RAM for at least 5 minutes on average, preferable an hour or more.
Those two counters, while they won't give you a complete picture of the memory pressure on a SQL server, will give you a fairly good idea of whether or not you need to think about increasing physical RAM in a box.
And, yes, SQL Server is capable of managing a good deal more memory than 2G. With different editions, SQL Server 2000 can handle up to 64G (http://www.microsoft.com/sql/techinf...KChooseEd.mspx).
mike hodgson
blog: http://sqlnerd.blogspot.com
Jason wrote:
We are going to replace the old SQL Server 2000 to a new
Hardware running Windows 2003 Server.
We are planning how much memory should be used. At the
present monent, we are using 2GB RAM and the performance
is OK. We find that the Average RAM and Peak RAM used is
around 2000 MB. According to Network Administrator, it is
because SQL uses all memory available for caching /
performance optimisation.
Is it necessary for us to purchase more RAM for the new
server ? Is it possible for SQL Server 2000 Standard to
access more than 2 GB RAM ?
Thanks