3G ram for SQL server: ram

Showing posts with label ram. Show all posts

Monday, March 19, 2012

8GB RAM in SQL Server

Hi,
I'm just designing a server... What can I do with 8GB RAM in the Box?
Analysis Services is limited to 8GB. The Enterprise Edition of SQL Server
can use max.8GB RAM. Windows 2003 Enterprise Edition can use 8GB, too.
So I can use 3GB for Analysis Services and the remaining 5GB are used for
SQL Server and the OS? Is this right?
Thanks
ThomasActually, there is no direct answer for this. It depends
on how much memory the analysis server requires for cubes
and memory required for SQL Server.
It also depends on any other applications running on the
same box.
In General, You have to allocate for OS,SQLServer,analysis
Server and then for other applications.
Please read this to get some idea on how SQL Server memory
works. http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_config_9zfy.asp
Also , read "memory architecture" from BOL.
To allocate memory for Analysis Server, stop and start the
analysis services and open the task manager and look for
memory used by the processess "msmdsrv.exe"
and "msmdvldm.exe". The sum of these memory is minimum
required for analysis services to start.
SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>Hi,
>I'm just designing a server... What can I do with 8GB RAM
in the Box?
>Analysis Services is limited to 8GB. The Enterprise
Edition of SQL Server
>can use max.8GB RAM. Windows 2003 Enterprise Edition can
use 8GB, too.
>So I can use 3GB for Analysis Services and the remaining
5GB are used for
>SQL Server and the OS? Is this right?
>
>Thanks
>
>Thomas
>
>.
>|||Hi,
thanks, that pointed me in the right direction...
Thomas
"SQLVarad" <SQLVarad@.hotmail.com> schrieb im Newsbeitrag
news:010c01c3a3d4$6cc8f790$a601280a@.phx.gbl...
> Actually, there is no direct answer for this. It depends
> on how much memory the analysis server requires for cubes
> and memory required for SQL Server.
> It also depends on any other applications running on the
> same box.
> In General, You have to allocate for OS,SQLServer,analysis
> Server and then for other applications.
> Please read this to get some idea on how SQL Server memory
> works. http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/adminsql/ad_config_9zfy.asp
> Also , read "memory architecture" from BOL.
> To allocate memory for Analysis Server, stop and start the
> analysis services and open the task manager and look for
> memory used by the processess "msmdsrv.exe"
> and "msmdvldm.exe". The sum of these memory is minimum
> required for analysis services to start.
> SQLVarad (MCDBA-1999,MCSE-1999)
>
> >--Original Message--
> >Hi,
> >
> >I'm just designing a server... What can I do with 8GB RAM
> in the Box?
> >Analysis Services is limited to 8GB. The Enterprise
> Edition of SQL Server
> >can use max.8GB RAM. Windows 2003 Enterprise Edition can
> use 8GB, too.
> >So I can use 3GB for Analysis Services and the remaining
> 5GB are used for
> >SQL Server and the OS? Is this right?
> >
> >
> >Thanks
> >
> >
> >Thomas
> >
> >
> >.
> >

8Gb RAM for SQL Server 2000 on Win 2003

What the setting should be (/3Gb, PAE, AWE) for the best utilization of
8GB RAM for SQL Server 2000 running on Win 2003?
ThanksHi,
After this you will have to do this:-
Probably you could configure 6 GB for SQL Server: Login as SA using query
analyzer and do:-
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE
GO
Thanks
Hari
SQL Server MVP
"vlad" <vesmusor@.yahoo.com> wrote in message
news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
> What the setting should be (/3Gb, PAE, AWE) for the best utilization of
> 8GB RAM for SQL Server 2000 running on Win 2003?
> Thanks
>|||Thanks for your reply, Hari. Does it mean I need to use both /3GB and
/PAE switches for this configuration?
Hari Prasad wrote:
> Hi,
> After this you will have to do this:-
> Probably you could configure 6 GB for SQL Server: Login as SA using query
> analyzer and do:-
> sp_configure 'show advanced options', 1
> RECONFIGURE
> GO
> sp_configure 'awe enabled', 1
> RECONFIGURE
> GO
> sp_configure 'max server memory', 6144
> RECONFIGURE
> GO
> Thanks
> Hari
> SQL Server MVP
> "vlad" <vesmusor@.yahoo.com> wrote in message
> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
> > What the setting should be (/3Gb, PAE, AWE) for the best utilization of
> > 8GB RAM for SQL Server 2000 running on Win 2003?
> >
> > Thanks
> >|||You need to use PAE (and SQL Server use AWE) to have 32 but app on 32 bit OS access > 2GB memory.
Whether you use 3GB or not is up to you. For a system with 8BG memory, it is probably a good idea to
use 3GB. Google for "Slava Oks" and read the Blogs, this will give you more information so you
understand the ins and outs of setting 3GB, PAE and also AWE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"vlad" <vesmusor@.yahoo.com> wrote in message
news:1158848235.068934.133350@.i42g2000cwa.googlegroups.com...
> Thanks for your reply, Hari. Does it mean I need to use both /3GB and
> /PAE switches for this configuration?
> Hari Prasad wrote:
>> Hi,
>> After this you will have to do this:-
>> Probably you could configure 6 GB for SQL Server: Login as SA using query
>> analyzer and do:-
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>> Thanks
>> Hari
>> SQL Server MVP
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
>> > What the setting should be (/3Gb, PAE, AWE) for the best utilization of
>> > 8GB RAM for SQL Server 2000 running on Win 2003?
>> >
>> > Thanks
>> >
>|||Doesn't this all assume he's running Enterprise Edition of SQL Server?
I thought non enterprise version didn't address this much RAM?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eHS1C9a3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> You need to use PAE (and SQL Server use AWE) to have 32 but app on 32 bit
> OS access > 2GB memory.
> Whether you use 3GB or not is up to you. For a system with 8BG memory, it
> is probably a good idea to use 3GB. Google for "Slava Oks" and read the
> Blogs, this will give you more information so you understand the ins and
> outs of setting 3GB, PAE and also AWE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "vlad" <vesmusor@.yahoo.com> wrote in message
> news:1158848235.068934.133350@.i42g2000cwa.googlegroups.com...
>> Thanks for your reply, Hari. Does it mean I need to use both /3GB and
>> /PAE switches for this configuration?
>> Hari Prasad wrote:
>> Hi,
>> After this you will have to do this:-
>> Probably you could configure 6 GB for SQL Server: Login as SA using
>> query
>> analyzer and do:-
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>> Thanks
>> Hari
>> SQL Server MVP
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
>> > What the setting should be (/3Gb, PAE, AWE) for the best utilization
>> > of
>> > 8GB RAM for SQL Server 2000 running on Win 2003?
>> >
>> > Thanks
>> >
>|||Correct. I assumed EE as no edition was mentioned in the original post...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Nick" <no.thanks@.nowhere.com> wrote in message news:enNh7uk3GHA.1796@.TK2MSFTNGP06.phx.gbl...
> Doesn't this all assume he's running Enterprise Edition of SQL Server?
> I thought non enterprise version didn't address this much RAM?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eHS1C9a3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>> You need to use PAE (and SQL Server use AWE) to have 32 but app on 32 bit
>> OS access > 2GB memory.
>> Whether you use 3GB or not is up to you. For a system with 8BG memory, it
>> is probably a good idea to use 3GB. Google for "Slava Oks" and read the
>> Blogs, this will give you more information so you understand the ins and
>> outs of setting 3GB, PAE and also AWE.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158848235.068934.133350@.i42g2000cwa.googlegroups.com...
>> Thanks for your reply, Hari. Does it mean I need to use both /3GB and
>> /PAE switches for this configuration?
>> Hari Prasad wrote:
>> Hi,
>> After this you will have to do this:-
>> Probably you could configure 6 GB for SQL Server: Login as SA using
>> query
>> analyzer and do:-
>> sp_configure 'show advanced options', 1
>> RECONFIGURE
>> GO
>> sp_configure 'awe enabled', 1
>> RECONFIGURE
>> GO
>> sp_configure 'max server memory', 6144
>> RECONFIGURE
>> GO
>> Thanks
>> Hari
>> SQL Server MVP
>> "vlad" <vesmusor@.yahoo.com> wrote in message
>> news:1158846464.619887.179240@.h48g2000cwc.googlegroups.com...
>> > What the setting should be (/3Gb, PAE, AWE) for the best utilization
>> > of
>> > 8GB RAM for SQL Server 2000 running on Win 2003?
>> >
>> > Thanks
>> >
>>
>

8Gb RAM for SQL Server 2000 on Win 2003

Sunday, March 11, 2012

8 GB of RAM installed

SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced Server. The server has 8 GB of RAM installed but for some reason SQL Server is only utilizing less than 4 GB. What would the likely cause be?
Three thoughts:
You are not using /3GB /PAE within the boot.ini
You have not configured SQL Server to with the "awe enabled" option (exec
sp_configure).
Memory usage is being restricted by min server memory (MB) and max server
memory (MB) (exec sp_configure).
Keith
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:A4DAD1E0-5C6A-49D4-8631-6DE54777B44B@.microsoft.com...
> SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced
Server. The server has 8 GB of RAM installed but for some reason SQL Server
is only utilizing less than 4 GB. What would the likely cause be?
|||Are you using the /3GB in your boot.ini? Look in books online under
"Managing AWE Memory."
-John Oakes
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:A4DAD1E0-5C6A-49D4-8631-6DE54777B44B@.microsoft.com...
> SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced
Server. The server has 8 GB of RAM installed but for some reason SQL Server
is only utilizing less than 4 GB. What would the likely cause be?
|||All those option is set. Still same problem
"John Oakes" wrote:

> Are you using the /3GB in your boot.ini? Look in books online under
> "Managing AWE Memory."
> -John Oakes
> "Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
> news:A4DAD1E0-5C6A-49D4-8631-6DE54777B44B@.microsoft.com...
> Server. The server has 8 GB of RAM installed but for some reason SQL Server
> is only utilizing less than 4 GB. What would the likely cause be?
>
>
|||Did you reboot after changing boot.ini?
-John Oakes
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:188FFB29-DDE5-42B3-A802-DC671FC7B68A@.microsoft.com...[vbcol=seagreen]
> All those option is set. Still same problem
> "John Oakes" wrote:
Advanced[vbcol=seagreen]
Server[vbcol=seagreen]
|||You also need the /PAE set in the Boot.ini.
Andrew J. Kelly SQL MVP
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:188FFB29-DDE5-42B3-A802-DC671FC7B68A@.microsoft.com...[vbcol=seagreen]
> All those option is set. Still same problem
> "John Oakes" wrote:
Advanced[vbcol=seagreen]
Server[vbcol=seagreen]
|||Serverdba wrote:

> SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced Server. The server has 8 GB of RAM installed but for some reason SQL Server is only utilizing less than 4 GB. What would the likely cause be?
Hi,
The Account which starts the SQL Service must have the privilege "lock
pages in memory" - you can set this in the local policy settings

8 GB of RAM installed

SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced Ser
ver. The server has 8 GB of RAM installed but for some reason SQL Server is
only utilizing less than 4 GB. What would the likely cause be?Three thoughts:
You are not using /3GB /PAE within the boot.ini
You have not configured SQL Server to with the "awe enabled" option (exec
sp_configure).
Memory usage is being restricted by min server memory (MB) and max server
memory (MB) (exec sp_configure).
Keith
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:A4DAD1E0-5C6A-49D4-8631-6DE54777B44B@.microsoft.com...
> SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced
Server. The server has 8 GB of RAM installed but for some reason SQL Server
is only utilizing less than 4 GB. What would the likely cause be?|||Are you using the /3GB in your boot.ini? Look in books online under
"Managing AWE Memory."
-John Oakes
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:A4DAD1E0-5C6A-49D4-8631-6DE54777B44B@.microsoft.com...
> SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced
Server. The server has 8 GB of RAM installed but for some reason SQL Server
is only utilizing less than 4 GB. What would the likely cause be?|||All those option is set. Still same problem
"John Oakes" wrote:

> Are you using the /3GB in your boot.ini? Look in books online under
> "Managing AWE Memory."
> -John Oakes
> "Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
> news:A4DAD1E0-5C6A-49D4-8631-6DE54777B44B@.microsoft.com...
> Server. The server has 8 GB of RAM installed but for some reason SQL Serve
r
> is only utilizing less than 4 GB. What would the likely cause be?
>
>|||Did you reboot after changing boot.ini?
-John Oakes
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:188FFB29-DDE5-42B3-A802-DC671FC7B68A@.microsoft.com...[vbcol=seagreen]
> All those option is set. Still same problem
> "John Oakes" wrote:
>
Advanced[vbcol=seagreen]
Server[vbcol=seagreen]|||You also need the /PAE set in the Boot.ini.
Andrew J. Kelly SQL MVP
"Serverdba" <Serverdba@.discussions.microsoft.com> wrote in message
news:188FFB29-DDE5-42B3-A802-DC671FC7B68A@.microsoft.com...[vbcol=seagreen]
> All those option is set. Still same problem
> "John Oakes" wrote:
>
Advanced[vbcol=seagreen]
Server[vbcol=seagreen]|||Serverdba wrote:

> SQL Server 2000 Enterprise Edition machine running Windows 2000 Advanced Server. T
he server has 8 GB of RAM installed but for some reason SQL Server is only utilizing
less than 4 GB. What would the likely cause be?
Hi,
The Account which starts the SQL Service must have the privilege "lock
pages in memory" - you can set this in the local policy settings

8 GB of RAM

I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
Standard.
I have the switches /PAE /3GB in boot.ini
SQL max server memory is = 6656 MB
but with those settings I cannot get SQLServer to get more than 2.7 GB
of RAM
If I use the AWE switch, then SQLServer can only use 128 MB max
What's wrong?
PaulOn Dec 4, 3:42 am, pd...@.clic.net wrote:
> I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> Standard.
> I have the switches /PAE /3GB in boot.ini
> SQL max server memory is = 6656 MB
> but with those settings I cannot get SQLServer to get more than 2.7 GB
> of RAM
> If I use the AWE switch, then SQLServer can only use 128 MB max
> What's wrong?
> Paul
Is it SQL Server 2000 or SQL Server 2005?
If its SQL Server 2000, then AWE is not supported
If its SQL Server 2005, then check the AWE 'Run Value' in
sp_configure... if its 0, then you might want to recycle the SQL
Server service to enable the AWE
In any case i dont believe you need to have /3GB switch in boot.ini.|||Hey, of course you can use AWE on SQL Server 2000 (and also on SQL Server
2005).
You will need to run
sp_configure 'awe enabled', 1
and grant the Lock Page in Memory permission to your service account. For
more details see
How to configure SQL Server to use more than 2 GB of physical memory
http://support.microsoft.com/kb/274750
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"dineshbabu.munugala@.gmail.com" wrote:
> On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > Standard.
> > I have the switches /PAE /3GB in boot.ini
> >
> > SQL max server memory is = 6656 MB
> >
> > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > of RAM
> >
> > If I use the AWE switch, then SQLServer can only use 128 MB max
> >
> > What's wrong?
> >
> > Paul
> Is it SQL Server 2000 or SQL Server 2005?
> If its SQL Server 2000, then AWE is not supported
> If its SQL Server 2005, then check the AWE 'Run Value' in
> sp_configure... if its 0, then you might want to recycle the SQL
> Server service to enable the AWE
> In any case i dont believe you need to have /3GB switch in boot.ini.
>|||Ben
If you are on 64-bit , you do not need AWE anymore. If the OP has SQL Server
2000 along with SP4 , make sure that he has a last hotfix especially if he
are going to enable AWE
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2ADA35D9-C4FA-4852-B20A-3E08F240C626@.microsoft.com...
> Hey, of course you can use AWE on SQL Server 2000 (and also on SQL Server
> 2005).
> You will need to run
> sp_configure 'awe enabled', 1
> and grant the Lock Page in Memory permission to your service account. For
> more details see
> How to configure SQL Server to use more than 2 GB of physical memory
> http://support.microsoft.com/kb/274750
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "dineshbabu.munugala@.gmail.com" wrote:
>> On Dec 4, 3:42 am, pd...@.clic.net wrote:
>> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
>> > Standard.
>> > I have the switches /PAE /3GB in boot.ini
>> >
>> > SQL max server memory is = 6656 MB
>> >
>> > but with those settings I cannot get SQLServer to get more than 2.7 GB
>> > of RAM
>> >
>> > If I use the AWE switch, then SQLServer can only use 128 MB max
>> >
>> > What's wrong?
>> >
>> > Paul
>> Is it SQL Server 2000 or SQL Server 2005?
>> If its SQL Server 2000, then AWE is not supported
>> If its SQL Server 2005, then check the AWE 'Run Value' in
>> sp_configure... if its 0, then you might want to recycle the SQL
>> Server service to enable the AWE
>> In any case i dont believe you need to have /3GB switch in boot.ini.|||On 3 d=E9c, 18:04, dineshbabu.munug...@.gmail.com wrote:
> On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > Standard.
> > I have the switches /PAE /3GB in boot.ini
> > SQL max server memory is =3D 6656 MB
> > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > of RAM
> > If I use the AWE switch, then SQLServer can only use 128 MB max
> > What's wrong?
> > Paul
> Is it SQL Server 2000 or SQL Server 2005?
> If its SQL Server 2000, then AWE is not supported
> If its SQL Server 2005, then check the AWE 'Run Value' in
> sp_configure... if its 0, then you might want to recycle the SQL
> Server service to enable the AWE
> In any case i dont believe you need to have /3GB switch in boot.ini.
SQL2005 Standard.
Running in 32 bits system
As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
(i.e. I run dbcc checkDB so that it starts using rapidly memory and I
see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
Paul|||Hi
Do you have SP4 with hotfix?
Install hotfix from(http://support.microsoft.com/kb/899761)
Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
<pdube@.clic.net> wrote in message
news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
On 3 déc, 18:04, dineshbabu.munug...@.gmail.com wrote:
> On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > Standard.
> > I have the switches /PAE /3GB in boot.ini
> > SQL max server memory is = 6656 MB
> > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > of RAM
> > If I use the AWE switch, then SQLServer can only use 128 MB max
> > What's wrong?
> > Paul
> Is it SQL Server 2000 or SQL Server 2005?
> If its SQL Server 2000, then AWE is not supported
> If its SQL Server 2005, then check the AWE 'Run Value' in
> sp_configure... if its 0, then you might want to recycle the SQL
> Server service to enable the AWE
> In any case i dont believe you need to have /3GB switch in boot.ini.
SQL2005 Standard.
Running in 32 bits system
As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
(i.e. I run dbcc checkDB so that it starts using rapidly memory and I
see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
Paul|||On 4 d=E9c, 10:45, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> Do you have SP4 with hotfix?
> Install hotfix from(http://support.microsoft.com/kb/899761)
> Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
> <pd...@.clic.net> wrote in message
> news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
> On 3 d=E9c, 18:04, dineshbabu.munug...@.gmail.com wrote:
>
>
> > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > > Standard.
> > > I have the switches /PAE /3GB in boot.ini
> > > SQL max server memory is =3D 6656 MB
> > > but with those settings I cannot get SQLServer to get more than 2.7 GB=
> > > of RAM
> > > If I use the AWE switch, then SQLServer can only use 128 MB max
> > > What's wrong?
> > > Paul
> > Is it SQL Server 2000 or SQL Server 2005?
> > If its SQL Server 2000, then AWE is not supported
> > If its SQL Server 2005, then check the AWE 'Run Value' in
> > sp_configure... if its 0, then you might want to recycle the SQL
> > Server service to enable the AWE
> > In any case i dont believe you need to have /3GB switch in boot.ini.
> SQL2005 Standard.
> Running in 32 bits system
> As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> Paul- Masquer le texte des messages pr=E9c=E9dents -
> - Afficher le texte des messages pr=E9c=E9dents -
SQL 2005 (9.00.3161.00)
The hotfix is for SQL 2000...
In PerfMon, I see Commited Bytes around 3.2 GB.
In Task Manager I still see SQLServer.exe using 2.7 GB. I guess then
that's just a problem related with Task Manager not showing the
correct amount of RAM used...?
Paul|||On 4 d=E9c, 14:08, pd...@.clic.net wrote:
> On 4 d=E9c, 10:45, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
> > Hi
> > Do you have SP4 with hotfix?
> > Install hotfix from(http://support.microsoft.com/kb/899761)
> > Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
> > <pd...@.clic.net> wrote in message
> >news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...=
> > On 3 d=E9c, 18:04, dineshbabu.munug...@.gmail.com wrote:
> > > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server=
> > > > Standard.
> > > > I have the switches /PAE /3GB in boot.ini
> > > > SQL max server memory is =3D 6656 MB
> > > > but with those settings I cannot get SQLServer to get more than 2.7 =GB
> > > > of RAM
> > > > If I use the AWE switch, then SQLServer can only use 128 MB max
> > > > What's wrong?
> > > > Paul
> > > Is it SQL Server 2000 or SQL Server 2005?
> > > If its SQL Server 2000, then AWE is not supported
> > > If its SQL Server 2005, then check the AWE 'Run Value' in
> > > sp_configure... if its 0, then you might want to recycle the SQL
> > > Server service to enable the AWE
> > > In any case i dont believe you need to have /3GB switch in boot.ini.
> > SQL2005 Standard.
> > Running in 32 bits system
> > As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> > (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> > see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> > Paul- Masquer le texte des messages pr=E9c=E9dents -
> > - Afficher le texte des messages pr=E9c=E9dents -
> SQL 2005 (9.00.3161.00)
> The hotfix is for SQL 2000...
> In PerfMon, I see Commited Bytes around 3.2 GB.
> In Task Manager I still see SQLServer.exe using 2.7 GB. I guess then
> that's just a problem related with Task Manager not showing the
> correct amount of RAM used...?
> Paul- Masquer le texte des messages pr=E9c=E9dents -
> - Afficher le texte des messages pr=E9c=E9dents -
When I say that I see 128 MB of RAM in SQLServer.exe when using AWE I
mean that's what I see in Task Manager. Am I correct looking at this
value?
Should I check PerfMon instead? Is the memory shown in TaskManager
only kernel memory and would not show all memory used by SQL?
Paul|||SP4 hotfix is not needed here as this is SQL Server 2005.
Try this to see how much memory SQL Server is using. Performance Monitor,
add SQLServer:Memory Manager - Total Server Memory (KB).
Have you granted the Lock Page in Memory permission to your service account?
See your Error log. Must show 'Address Windowing Extensions enabled' or some
AWE error message.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Uri Dimant" wrote:
> Hi
> Do you have SP4 with hotfix?
> Install hotfix from(http://support.microsoft.com/kb/899761)
> Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
>
>
> <pdube@.clic.net> wrote in message
> news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
> On 3 dÃ©c, 18:04, dineshbabu.munug...@.gmail.com wrote:
> > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> >
> > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > > Standard.
> > > I have the switches /PAE /3GB in boot.ini
> >
> > > SQL max server memory is = 6656 MB
> >
> > > but with those settings I cannot get SQLServer to get more than 2.7 GB
> > > of RAM
> >
> > > If I use the AWE switch, then SQLServer can only use 128 MB max
> >
> > > What's wrong?
> >
> > > Paul
> >
> > Is it SQL Server 2000 or SQL Server 2005?
> > If its SQL Server 2000, then AWE is not supported
> > If its SQL Server 2005, then check the AWE 'Run Value' in
> > sp_configure... if its 0, then you might want to recycle the SQL
> > Server service to enable the AWE
> >
> > In any case i dont believe you need to have /3GB switch in boot.ini.
> SQL2005 Standard.
> Running in 32 bits system
> As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> Paul
>
>|||Sorry. I missed that you are on SQL Server 2005.
In 32 -bit you do need AWE and as Ben pointed out gran permission Lock
Page in Memory permission to your service account.
To see if SQL server uses 6GB open PerfMonitor , do not use Task Manager
Visit also http://blogs.msdn.com/slavao/
<pdube@.clic.net> wrote in message
news:f9c9559b-d836-4b7f-92d4-bfc8834aab35@.s36g2000prg.googlegroups.com...
On 4 déc, 14:08, pd...@.clic.net wrote:
> On 4 déc, 10:45, "Uri Dimant" <u...@.iscar.co.il> wrote:
>
>
> > Hi
> > Do you have SP4 with hotfix?
> > Install hotfix from(http://support.microsoft.com/kb/899761)
> > Do you see in PerfMon that SQL Server allocate 6.6GB RAM?
> > <pd...@.clic.net> wrote in message
> >news:e4a9b19a-1b95-4df7-b6d9-e8c374bfb9c0@.a39g2000pre.googlegroups.com...
> > On 3 déc, 18:04, dineshbabu.munug...@.gmail.com wrote:
> > > On Dec 4, 3:42 am, pd...@.clic.net wrote:
> > > > I am running Windows 2003 Enterprise with 8 GB of RAM and SQL Server
> > > > Standard.
> > > > I have the switches /PAE /3GB in boot.ini
> > > > SQL max server memory is = 6656 MB
> > > > but with those settings I cannot get SQLServer to get more than 2.7
> > > > GB
> > > > of RAM
> > > > If I use the AWE switch, then SQLServer can only use 128 MB max
> > > > What's wrong?
> > > > Paul
> > > Is it SQL Server 2000 or SQL Server 2005?
> > > If its SQL Server 2000, then AWE is not supported
> > > If its SQL Server 2005, then check the AWE 'Run Value' in
> > > sp_configure... if its 0, then you might want to recycle the SQL
> > > Server service to enable the AWE
> > > In any case i dont believe you need to have /3GB switch in boot.ini.
> > SQL2005 Standard.
> > Running in 32 bits system
> > As soon as I use AWE, I'm stuck with 128 MB of RAM for SQL server...
> > (i.e. I run dbcc checkDB so that it starts using rapidly memory and I
> > see SQLServer.exe using not more than 128 MB of RAM in Task Manager).
> > Paul- Masquer le texte des messages précédents -
> > - Afficher le texte des messages précédents -
> SQL 2005 (9.00.3161.00)
> The hotfix is for SQL 2000...
> In PerfMon, I see Commited Bytes around 3.2 GB.
> In Task Manager I still see SQLServer.exe using 2.7 GB. I guess then
> that's just a problem related with Task Manager not showing the
> correct amount of RAM used...?
> Paul- Masquer le texte des messages précédents -
> - Afficher le texte des messages précédents -
When I say that I see 128 MB of RAM in SQLServer.exe when using AWE I
mean that's what I see in Task Manager. Am I correct looking at this
value?
Should I check PerfMon instead? Is the memory shown in TaskManager
only kernel memory and would not show all memory used by SQL?
Paul

8 GB of RAM

Thursday, March 8, 2012

6G Ram but SQL doesn't make use of it by default

If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
Thanks
You shouldn't have to do any config on a 64 bit SS on a 64 bit machine for using > 2GB memory.

> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Oks blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks
|||2147483647 is much more than 2GB... 1024 = 1GB
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks
|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

6G Ram but SQL doesn't make use of it by default

If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
ThanksYou shouldn't have to do any config on a 64 bit SS on a 64 bit machine for u
sing > 2GB memory.

> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Ok
s blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, i
n
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||2147483647 is much more than 2GB... 1024 = 1GB
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

6G Ram but SQL doesn't make use of it by default

If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
I have made a new fresh setup (everything is in 64 bit version) on a brand
new machine with 6Gigs of RAM, still when I display server's properties, in
the memory section, in the textbox under Max Server memory
I have 2147483647, which I think is 2 G
ThanksYou shouldn't have to do any config on a 64 bit SS on a 64 bit machine for using > 2GB memory.
> I have 2147483647, which I think is 2 G
No, that number is in MB:
2147483647 MB
= 2097151 GB
= 2047 TB
= approx 2 PB (it is Peta or Exa?)
No discussion on memory and 64 bit is complete without referring to Slava Oks blogs. In short,
consider using lock pages in memory also on 64 bit (if my memory serves me):
http://blogs.msdn.com/slavao/archive/category/9043.aspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties, in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||2147483647 is much more than 2GB... 1024 = 1GB
--
Keith Kratochvil
"SalamElias" <eliassal@.online.nospam> wrote in message
news:60DD00FD-BFA2-4A68-B6B5-142E8B895D45@.microsoft.com...
> If I setup SQL 2005 64 bit on a win2k3 64 bit, do I need to enable/disable
> AWE as for other versions or it is managed dynamically by SQL 2005 64 bit.
> I have made a new fresh setup (everything is in 64 bit version) on a brand
> new machine with 6Gigs of RAM, still when I display server's properties,
> in
> the memory section, in the textbox under Max Server memory
> I have 2147483647, which I think is 2 G
> Thanks|||Hi Salam,
As other members have mentioned, the 2147483647 default value is in MB, so
it is a quite large value( you need to reduce it according to your
machine's physical memory).
As for AWE, it is an operating system feature and is only useful when the
virtual address space(of OS process) is less than the available physical
memory. For SQL server 2005 64 bit on windows 2003 64 bit, there is no
such virtual address space limitation(normally virtual address space is
much larger than available physical memory space). also, it is recommended
that we adjust the SQL Server instance's max server memory to a proper
value so that other applications on the server can also get sufficient
physical memory for paging file switch.
#Enabling AWE Memory for SQL Server
http://msdn2.microsoft.com/en-us/library/ms190673.aspx
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Salam,
Have you got any further idea on this issue? Please feel free to post here
if there is anything else we can help.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Tuesday, March 6, 2012

64-bit Server (clustered) 128 GB ram - 155 GB used

It seems the Server itself shows 155GB used, even though one Instance has 91 GB limit and the other 31 GB limit?

The server only has 128 GB of RAM with a 30+ GB Virtual Swap File.

What do I check to see if there is a problem? What perfmon do I use?

We are testing on this server and the CPUs are only at 5-10% max. Some testing brought the CPUs to 80-90%.

There are no errors.

Thanks,

Thomas

If you have two instances on one server, they will use all the memory up to the Max memory limit that you set for SQL Server in each instance.

You also need to make sure that you have given the "Lock Pages in Memory" right to the SQL Server service account. My blog post has more details:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry

If you are concerned about memory pressure, buffer cache hit ratio and page life expectancy, and memory grants pending are three places to start looking.

|||

Where are you getting the 155GB value from? Can you try DBCC memorystatus on both instances and paste the results?

Thanks, Ron D.

64-bit Server (clustered) 128 GB ram - 155 GB used

It seems the Server itself shows 155GB used, even though one Instance has 91 GB limit and the other 31 GB limit?

The server only has 128 GB of RAM with a 30+ GB Virtual Swap File.

What do I check to see if there is a problem? What perfmon do I use?

We are testing on this server and the CPUs are only at 5-10% max. Some testing brought the CPUs to 80-90%.

There are no errors.

Thanks,

Thomas

If you have two instances on one server, they will use all the memory up to the Max memory limit that you set for SQL Server in each instance.

You also need to make sure that you have given the "Lock Pages in Memory" right to the SQL Server service account. My blog post has more details:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!115.entry

If you are concerned about memory pressure, buffer cache hit ratio and page life expectancy, and memory grants pending are three places to start looking.

|||

Where are you getting the 155GB value from? Can you try DBCC memorystatus on both instances and paste the results?

Thanks, Ron D.

Saturday, February 25, 2012

64 bit vs 32 bit with same RAM.

If I’m setting up a server, and I don’t plan on putting a lot of RAM(4GB
maybe 8GB) in it, will 64bit still be better?
thanks
If I'm to make a choice in the specific situation that you described and my
databases have always been on the 32-bit platform, I would most likely just
leave them on 32-bit until I run tests to determine if there is any benefits
going to 64 bit at all.
But there is always this issue of 'scaling forward' to take advantage of
what's coming in computing, and 64-bit generally speaking would put you in a
better position to scale forward. Of course, for your particular app, scaling
forward may or may not be relevant.
Now, note that 64-bit does carry some overhead in terms of larger data
structures and so on. Not all workloads will automatically benefit
performance wise from moving to 64-bit. For instance, if the data set of your
workloads can fit largely in memory, you may find 32-bit faster than 64-bit.
Linchi
"Jason" wrote:

> If I’m setting up a server, and I don’t plan on putting a lot of RAM(4GB
> maybe 8GB) in it, will 64bit still be better?
> thanks

64 bit vs 32 bit with same RAM.

If Iâ'm setting up a server, and I donâ't plan on putting a lot of RAM(4GB
maybe 8GB) in it, will 64bit still be better?
thanksIf I'm to make a choice in the specific situation that you described and my
databases have always been on the 32-bit platform, I would most likely just
leave them on 32-bit until I run tests to determine if there is any benefits
going to 64 bit at all.
But there is always this issue of 'scaling forward' to take advantage of
what's coming in computing, and 64-bit generally speaking would put you in a
better position to scale forward. Of course, for your particular app, scaling
forward may or may not be relevant.
Now, note that 64-bit does carry some overhead in terms of larger data
structures and so on. Not all workloads will automatically benefit
performance wise from moving to 64-bit. For instance, if the data set of your
workloads can fit largely in memory, you may find 32-bit faster than 64-bit.
Linchi
"Jason" wrote:
> If Iâ'm setting up a server, and I donâ't plan on putting a lot of RAM(4GB
> maybe 8GB) in it, will 64bit still be better?
> thanks

64 BIT SQL Server

Hi,
We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
size is going 57 Gb, but our server performance is very poor, I configured
Min
memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
15.7 Gb, and machine is going slow, some time i saw some transaction is going
Suspended mode,
my Box task manager showing available M Bytes 270 MB,
your answers will be appreciate and help full to me
Jay
Thanks steen for reply,
but every day am doing main tables index and every week end i am doing total
tables rebuilding the index and in the week end i am updating the statistics
also.
but my server very slow. do u know why i allocated min memory to 1 GB and
max memory to 14 gb , remaing 2 GB for os but in Task manager its showing 230
mb Available.
please your reply will be great guidence to me
regards
jay
""Steen Schlüter Persson (DK)"" wrote:

> Jay wrote:
> Hi Jay,
> Performance is not only a question of Memory..:-).
> What about indexes and statistics on you database - Are they being kept
> updated? You could also look at the the performance counters to see what
> it is that is slow.
> Try to take a look at -
> http://www.sql-server-performance.com/sql_server_performance_audit2.asp
> - that should give you an idea which counters to look for.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>
|||Jay -
Did you change the min/max using the GUI tool? If so, you still need to go
into a query window and do a:
reconfigure with override
It sounds like you are still running with the default max memory value
(which is something like 2TB).
|||Hello apf,
thanks for your mail.
yes i did this configuration through t-sql commans only.
dear apf i am waiting for your reply, it will be more help full to me
"apf" wrote:

> Jay -
> Did you change the min/max using the GUI tool? If so, you still need to go
> into a query window and do a:
> reconfigure with override
> It sounds like you are still running with the default max memory value
> (which is something like 2TB).
>
>
|||Jay -
If your output of:
sp_configure, 'max server memory (MB)'
returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
is set correctly and your problem is elsewhere.
|||Dear APF,
its showing right but my server is too much slow, i am sending to you my sys
configuration details.
am waiting for your reply.
my database is hitting every seconds 30000
configuration_idnamevalueminimummaximumvalue_in_usedescriptionis_dynamicis_advanced
16391Ad Hoc Distributed Queries1011Enable or disable Ad Hoc
Distributed Queries11
1550affinity I/O mask0-214748364821474836470affinity I/O mask01
1535affinity mask0-214748364821474836470affinity mask11
1551affinity64 I/O mask0-214748364821474836470affinity64 I/O mask01
1549affinity64 mask0-214748364821474836470affinity64 mask11
16384Agent XPs1011Enable or disable Agent XPs11
102allow updates0010Allow updates to system tables10
1548awe enabled0010AWE enabled in the server01
1569blocked process threshold00864000Blocked process reporting
threshold11
544c2 audit mode0010c2 audit mode01
1562clr enabled0010CLR user code execution enabled in the server10
1538cost threshold for parallelism50327675cost threshold for
parallelism11
400cross db ownership chaining0010Allow cross db ownership chaining10
1531cursor threshold-1-12147483647-1cursor threshold11
16386Database Mail XPs0010Enable or disable Database Mail XPs11
1126default full-text language1033021474836471033default full-text
language11
124default language0099990default language10
1568default trace enabled1011Enable or disable the default trace11
114disallow results from triggers0010Disallow returning results from
triggers11
109fill factor (%)001000Default fill factor percentage01
1567ft crawl bandwidth (max)100032767100Max number of full-text crawl
buffers11
1566ft crawl bandwidth (min)00327670Number of reserved full-text crawl
buffers11
1565ft notify bandwidth (max)100032767100Max number of full-text
notifications buffers11
1564ft notify bandwidth (min)00327670Number of reserved full-text
notifications buffers11
1505index create memory (KB)070421474836470Memory for index create
sorts (kBytes)11
1570in-doubt xact resolution0020Recovery policy for DTC transactions
with unknown outcome11
1546lightweight pooling0010User mode scheduler uses lightweight
pooling01
106locks0500021474836470Number of locks for all users01
1539max degree of parallelism00640maximum degree of parallelism11
1563max full-text crawl range402564Maximum crawl ranges allowed in
full-text indexing11
1544max server memory (MB)1400016214748364714000Maximum size of server
memory (MB)11
1536max text repl size (B)655360214748364765536Maximum size of a text
field in replication.10
503max worker threads0128327670Maximum worker threads01
1537media retention003650Tape retention period in days01
1540min memory per query (KB)102451221474836471024minimum memory per
query (kBytes)11
1543min server memory (MB)1024021474836471024Minimum size of server
memory (MB)11
115nested triggers1011Allow triggers to be invoked within triggers10
505network packet size (B)4096512327674096Network packet size11
16388Ole Automation Procedures0010Enable or disable Ole Automation
Procedures11
107open objects0021474836470Number of open database objects01
1557PH timeout (s)601360060DB connection timeout for full-text
protocol handler (s)11
1556precompute rank0010Use precomputed rank for full-text query11
1517priority boost0010Priority boost01
1545query governor cost limit0021474836470Maximum estimated cost
allowed by query governor11
1541query wait (s)-1-12147483647-1maximum time to wait for query
memory (s)11
101recovery interval (min)00327670Maximum recovery interval in
minutes11
117remote access1011Allow remote access00
1576remote admin connections0010Dedicated Admin Connections are
allowed from remote clients10
1519remote login timeout (s)200214748364720remote login timeout10
542remote proc trans0010Create DTC transaction for remote procedures10
1520remote query timeout (s)0021474836470remote query timeout10
16392Replication XPs0010Enable or disable Replication XPs11
1547scan for startup procs0010scan for startup stored procedures01
116server trigger recursion1011Allow recursion for server level
triggers10
1532set working set size0010set working set size01
518show advanced options1011show advanced options10
16387SMO and DMO XPs1011Enable or disable SMO and DMO XPs11
16385SQL Mail XPs0010Enable or disable SQL Mail XPs11
1555transform noise words0010Transform noise words for full-text
query11
1127two digit year cutoff2049175399992049two digit year cutoff11
103user connections00327670Number of user connections allowed01
1534user options00327670user options10
16389Web Assistant Procedures0010Enable or disable Web Assistant
Procedures11
16390xp_cmdshell1011Enable or disable command shell11
"apf" wrote:

> Jay -
> If your output of:
> sp_configure, 'max server memory (MB)'
> returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
> is set correctly and your problem is elsewhere.
>

64 BIT SQL Server

Hi,
We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
size is going 57 Gb, but our server performance is very poor, I configured
Min
memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
15.7 Gb, and machine is going slow, some time i saw some transaction is going
Suspended mode,
my Box task manager showing available M Bytes 270 MB,
your answers will be appreciate and help full to me
JayJay wrote:
> Hi,
> We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> size is going 57 Gb, but our server performance is very poor, I configured
> Min
> memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> Suspended mode,
> my Box task manager showing available M Bytes 270 MB,
>
> your answers will be appreciate and help full to me
> Jay
>
Hi Jay,
Performance is not only a question of Memory..:-).
What about indexes and statistics on you database - Are they being kept
updated? You could also look at the the performance counters to see what
it is that is slow.
Try to take a look at -
http://www.sql-server-performance.com/sql_server_performance_audit2.asp
- that should give you an idea which counters to look for.
--
Regards
Steen SchlÃ¼ter Persson
Database Administrator / System Administrator|||Thanks steen for reply,
but every day am doing main tables index and every week end i am doing total
tables rebuilding the index and in the week end i am updating the statistics
also.
but my server very slow. do u know why i allocated min memory to 1 GB and
max memory to 14 gb , remaing 2 GB for os but in Task manager its showing 230
mb Available.
please your reply will be great guidence to me
regards
jay
""Steen SchlÃ¼ter Persson (DK)"" wrote:
> Jay wrote:
> > Hi,
> > We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
> > size is going 57 Gb, but our server performance is very poor, I configured
> > Min
> > memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> > 15.7 Gb, and machine is going slow, some time i saw some transaction is going
> > Suspended mode,
> >
> > my Box task manager showing available M Bytes 270 MB,
> >
> >
> > your answers will be appreciate and help full to me
> >
> > Jay
> >
> Hi Jay,
> Performance is not only a question of Memory..:-).
> What about indexes and statistics on you database - Are they being kept
> updated? You could also look at the the performance counters to see what
> it is that is slow.
> Try to take a look at -
> http://www.sql-server-performance.com/sql_server_performance_audit2.asp
> - that should give you an idea which counters to look for.
> --
> Regards
> Steen SchlÃ¼ter Persson
> Database Administrator / System Administrator
>|||Jay -
Did you change the min/max using the GUI tool? If so, you still need to go
into a query window and do a:
reconfigure with override
It sounds like you are still running with the default max memory value
(which is something like 2TB).|||Hello apf,
thanks for your mail.
yes i did this configuration through t-sql commans only.
dear apf i am waiting for your reply, it will be more help full to me
"apf" wrote:
> Jay -
> Did you change the min/max using the GUI tool? If so, you still need to go
> into a query window and do a:
> reconfigure with override
> It sounds like you are still running with the default max memory value
> (which is something like 2TB).
>
>|||Jay -
If your output of:
sp_configure, 'max server memory (MB)'
returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
is set correctly and your problem is elsewhere.|||Dear APF,
its showing right but my server is too much slow, i am sending to you my sys
configuration details.
am waiting for your reply.
my database is hitting every seconds 30000
configuration_id name value minimum maximum value_in_use description is_dynamic is_advanced
16391 Ad Hoc Distributed Queries 1 0 1 1 Enable or disable Ad Hoc
Distributed Queries 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask 0 1
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 0 affinity64 I/O mask 0 1
1549 affinity64 mask 0 -2147483648 2147483647 0 affinity64 mask 1 1
16384 Agent XPs 1 0 1 1 Enable or disable Agent XPs 1 1
102 allow updates 0 0 1 0 Allow updates to system tables 1 0
1548 awe enabled 0 0 1 0 AWE enabled in the server 0 1
1569 blocked process threshold 0 0 86400 0 Blocked process reporting
threshold 1 1
544 c2 audit mode 0 0 1 0 c2 audit mode 0 1
1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server 1 0
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for
parallelism 1 1
400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining 1 0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs 1 1
1126 default full-text language 1033 0 2147483647 1033 default full-text
language 1 1
124 default language 0 0 9999 0 default language 1 0
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1
114 disallow results from triggers 0 0 1 0 Disallow returning results from
triggers 1 1
109 fill factor (%) 0 0 100 0 Default fill factor percentage 0 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl
buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl
buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text
notifications buffers 1 1
1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text
notifications buffers 1 1
1505 index create memory (KB) 0 704 2147483647 0 Memory for index create
sorts (kBytes) 1 1
1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions
with unknown outcome 1 1
1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight
pooling 0 1
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1
1539 max degree of parallelism 0 0 64 0 maximum degree of parallelism 1 1
1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in
full-text indexing 1 1
1544 max server memory (MB) 14000 16 2147483647 14000 Maximum size of server
memory (MB) 1 1
1536 max text repl size (B) 65536 0 2147483647 65536 Maximum size of a text
field in replication. 1 0
503 max worker threads 0 128 32767 0 Maximum worker threads 0 1
1537 media retention 0 0 365 0 Tape retention period in days 0 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per
query (kBytes) 1 1
1543 min server memory (MB) 1024 0 2147483647 1024 Minimum size of server
memory (MB) 1 1
115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers 1 0
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
16388 Ole Automation Procedures 0 0 1 0 Enable or disable Ole Automation
Procedures 1 1
107 open objects 0 0 2147483647 0 Number of open database objects 0 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text
protocol handler (s) 1 1
1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query 1 1
1517 priority boost 0 0 1 0 Priority boost 0 1
1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost
allowed by query governor 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query
memory (s) 1 1
101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in
minutes 1 1
117 remote access 1 0 1 1 Allow remote access 0 0
1576 remote admin connections 0 0 1 0 Dedicated Admin Connections are
allowed from remote clients 1 0
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures 1 0
1520 remote query timeout (s) 0 0 2147483647 0 remote query timeout 1 0
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
1547 scan for startup procs 0 0 1 0 scan for startup stored procedures 0 1
116 server trigger recursion 1 0 1 1 Allow recursion for server level
triggers 1 0
1532 set working set size 0 0 1 0 set working set size 0 1
518 show advanced options 1 0 1 1 show advanced options 1 0
16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs 1 1
16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs 1 1
1555 transform noise words 0 0 1 0 Transform noise words for full-text
query 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
103 user connections 0 0 32767 0 Number of user connections allowed 0 1
1534 user options 0 0 32767 0 user options 1 0
16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant
Procedures 1 1
16390 xp_cmdshell 1 0 1 1 Enable or disable command shell 1 1
"apf" wrote:
> Jay -
> If your output of:
> sp_configure, 'max server memory (MB)'
> returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
> is set correctly and your problem is elsewhere.
>

64 BIT SQL Server

Hi,
We are working with SQL 2005 64 Bit server, with 16GB Ram, now our database
size is going 57 Gb, but our server performance is very poor, I configured
Min
memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
15.7 Gb, and machine is going slow, some time i saw some transaction is goin
g
Suspended mode,
my Box task manager showing available M Bytes 270 MB,
your answers will be appreciate and help full to me
JayJay wrote:
> Hi,
> We are working with SQL 2005 64 Bit server, with 16GB Ram, now our databas
e
> size is going 57 Gb, but our server performance is very poor, I configured
> Min
> memory to 1024 MB and Max memory to 14 GB, now my page File usage showing
> 15.7 Gb, and machine is going slow, some time i saw some transaction is go
ing
> Suspended mode,
> my Box task manager showing available M Bytes 270 MB,
>
> your answers will be appreciate and help full to me
> Jay
>
Hi Jay,
Performance is not only a question of Memory..:-).
What about indexes and statistics on you database - Are they being kept
updated? You could also look at the the performance counters to see what
it is that is slow.
Try to take a look at -
http://www.sql-server-performance.c...ance_audit2.asp
- that should give you an idea which counters to look for.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Thanks steen for reply,
but every day am doing main tables index and every week end i am doing total
tables rebuilding the index and in the week end i am updating the statistics
also.
but my server very slow. do u know why i allocated min memory to 1 GB and
max memory to 14 gb , remaing 2 GB for os but in Task manager its showing 23
0
mb Available.
please your reply will be great guidence to me
regards
jay
""Steen Schlüter Persson (DK)"" wrote:

> Jay wrote:
> Hi Jay,
> Performance is not only a question of Memory..:-).
> What about indexes and statistics on you database - Are they being kept
> updated? You could also look at the the performance counters to see what
> it is that is slow.
> Try to take a look at -
> http://www.sql-server-performance.c...ance_audit2.asp
> - that should give you an idea which counters to look for.
> --
> Regards
> Steen Schlüter Persson
> Database Administrator / System Administrator
>|||Jay -
Did you change the min/max using the GUI tool? If so, you still need to go
into a query window and do a:
reconfigure with override
It sounds like you are still running with the default max memory value
(which is something like 2TB).|||Hello apf,
thanks for your mail.
yes i did this configuration through t-sql commans only.
dear apf i am waiting for your reply, it will be more help full to me
"apf" wrote:

> Jay -
> Did you change the min/max using the GUI tool? If so, you still need to g
o
> into a query window and do a:
> reconfigure with override
> It sounds like you are still running with the default max memory value
> (which is something like 2TB).
>
>|||Jay -
If your output of:
sp_configure, 'max server memory (MB)'
returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
is set correctly and your problem is elsewhere.|||Dear APF,
its showing right but my server is too much slow, i am sending to you my sys
configuration details.
am waiting for your reply.
my database is hitting every seconds 30000
configuration_id name value minimum maxi
mum value_in_use description is_dyna
mic is_advanced
16391 Ad Hoc Distributed Queries 1 0 1 1 Enable or disable Ad Hoc
Distributed Queries 1 1
1550 affinity I/O mask 0 -2147483648 2147483647 0 affinity I/O mask 0 1
1535 affinity mask 0 -2147483648 2147483647 0 affinity mask 1 1
1551 affinity64 I/O mask 0 -2147483648 2147483647 0 affinity64 I/O mask 0 1
1549 affinity64 mask 0 -2147483648 2147483647 0 affinity64 mask 1 1
16384 Agent XPs 1 0 1 1 Enable or disable Agent XPs 1 1
102 allow updates 0 0 1 0 Allow updates to system tables 1 0
1548 awe enabled 0 0 1 0 AWE enabled in the server 0 1
1569 blocked process threshold 0 0 86400 0 Blocked process reporting
threshold 1 1
544 c2 audit mode 0 0 1 0 c2 audit mode 0 1
1562 clr enabled 0 0 1 0 CLR user code execution enabled in the server 1 0
1538 cost threshold for parallelism 5 0 32767 5 cost threshold for
parallelism 1 1
400 cross db ownership chaining 0 0 1 0 Allow cross db ownership chaining 1
0
1531 cursor threshold -1 -1 2147483647 -1 cursor threshold 1 1
16386 Database Mail XPs 0 0 1 0 Enable or disable Database Mail XPs 1 1
1126 default full-text language 1033 0 2147483647 1033 default full-text
language 1 1
124 default language 0 0 9999 0 default language 1 0
1568 default trace enabled 1 0 1 1 Enable or disable the default trace 1 1
114 disallow results from triggers 0 0 1 0 Disallow returning results from
triggers 1 1
109 fill factor (%) 0 0 100 0 Default fill factor percentage 0 1
1567 ft crawl bandwidth (max) 100 0 32767 100 Max number of full-text crawl
buffers 1 1
1566 ft crawl bandwidth (min) 0 0 32767 0 Number of reserved full-text crawl
buffers 1 1
1565 ft notify bandwidth (max) 100 0 32767 100 Max number of full-text
notifications buffers 1 1
1564 ft notify bandwidth (min) 0 0 32767 0 Number of reserved full-text
notifications buffers 1 1
1505 index create memory (KB) 0 704 2147483647 0 Memory for index create
sorts (kBytes) 1 1
1570 in-doubt xact resolution 0 0 2 0 Recovery policy for DTC transactions
with unknown outcome 1 1
1546 lightweight pooling 0 0 1 0 User mode scheduler uses lightweight
pooling 0 1
106 locks 0 5000 2147483647 0 Number of locks for all users 0 1
1539 max degree of parallelism 0 0 64 0 maximum degree of parallelism 1 1
1563 max full-text crawl range 4 0 256 4 Maximum crawl ranges allowed in
full-text indexing 1 1
1544 max server memory (MB) 14000 16 2147483647 14000 Maximum size of server
memory (MB) 1 1
1536 max text repl size (B) 65536 0 2147483647 65536 Maximum size of a text
field in replication. 1 0
503 max worker threads 0 128 32767 0 Maximum worker threads 0 1
1537 media retention 0 0 365 0 Tape retention period in days 0 1
1540 min memory per query (KB) 1024 512 2147483647 1024 minimum memory per
query (kBytes) 1 1
1543 min server memory (MB) 1024 0 2147483647 1024 Minimum size of server
memory (MB) 1 1
115 nested triggers 1 0 1 1 Allow triggers to be invoked within triggers 1 0
505 network packet size (B) 4096 512 32767 4096 Network packet size 1 1
16388 Ole Automation Procedures 0 0 1 0 Enable or disable Ole Automation
Procedures 1 1
107 open objects 0 0 2147483647 0 Number of open database objects 0 1
1557 PH timeout (s) 60 1 3600 60 DB connection timeout for full-text
protocol handler (s) 1 1
1556 precompute rank 0 0 1 0 Use precomputed rank for full-text query 1 1
1517 priority boost 0 0 1 0 Priority boost 0 1
1545 query governor cost limit 0 0 2147483647 0 Maximum estimated cost
allowed by query governor 1 1
1541 query wait (s) -1 -1 2147483647 -1 maximum time to wait for query
memory (s) 1 1
101 recovery interval (min) 0 0 32767 0 Maximum recovery interval in
minutes 1 1
117 remote access 1 0 1 1 Allow remote access 0 0
1576 remote admin connections 0 0 1 0 Dedicated Admin Connections are
allowed from remote clients 1 0
1519 remote login timeout (s) 20 0 2147483647 20 remote login timeout 1 0
542 remote proc trans 0 0 1 0 Create DTC transaction for remote procedures 1
0
1520 remote query timeout (s) 0 0 2147483647 0 remote query timeout 1 0
16392 Replication XPs 0 0 1 0 Enable or disable Replication XPs 1 1
1547 scan for startup procs 0 0 1 0 scan for startup stored procedures 0 1
116 server trigger recursion 1 0 1 1 Allow recursion for server level
triggers 1 0
1532 set working set size 0 0 1 0 set working set size 0 1
518 show advanced options 1 0 1 1 show advanced options 1 0
16387 SMO and DMO XPs 1 0 1 1 Enable or disable SMO and DMO XPs 1 1
16385 SQL Mail XPs 0 0 1 0 Enable or disable SQL Mail XPs 1 1
1555 transform noise words 0 0 1 0 Transform noise words for full-text
query 1 1
1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1
103 user connections 0 0 32767 0 Number of user connections allowed 0 1
1534 user options 0 0 32767 0 user options 1 0
16389 Web Assistant Procedures 0 0 1 0 Enable or disable Web Assistant
Procedures 1 1
16390 xp_cmdshell 1 0 1 1 Enable or disable command shell 1 1
"apf" wrote:

> Jay -
> If your output of:
> sp_configure, 'max server memory (MB)'
> returns with a RUN VALUE of 14336 (i.e. 14GB), then your SQL Server memory
> is set correctly and your problem is elsewhere.
>

Monday, March 19, 2012

8GB RAM in SQL Server

8Gb RAM for SQL Server 2000 on Win 2003

8Gb RAM for SQL Server 2000 on Win 2003

8Gb RAM for SQL Server 2000 on Win 2003

Sunday, March 11, 2012

8 GB of RAM installed

8 GB of RAM installed

8 GB of RAM

8 GB of RAM

8 GB of RAM

Thursday, March 8, 2012

6G Ram but SQL doesn't make use of it by default

6G Ram but SQL doesn't make use of it by default

6G Ram but SQL doesn't make use of it by default

Tuesday, March 6, 2012

64-bit Server (clustered) 128 GB ram - 155 GB used

64-bit Server (clustered) 128 GB ram - 155 GB used

Saturday, February 25, 2012

64 bit vs 32 bit with same RAM.

64 bit vs 32 bit with same RAM.

64 BIT SQL Server

64 BIT SQL Server

64 BIT SQL Server

3G ram for SQL server

Blog Archive

About Me