Help Required URGENT ! Out of Memory Error

We are getting the following errror on SQL Server : We have about 4GB
memory and has AWE enabled. Why are we getting these errors? We just
increase the memory from 1 Gig to 4 Gig and we are assuming that now we have
sufficient memory.
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=91967 Free=0 Procedures=726
Inram=292678 Dirty=20958 Kept=0
I/O=0, Latched=1724, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=315360
InternalReservation=525 ExternalReservation=16375 Min Free=512
Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
OS Committed=2333
OS In Use=2304
Query Plan=77132 Optimizer=295
General=11448
Utilities=32 Connection=4067
Global Memory Objects: Resource=9076 Locks=185
SQLCache=375 Replication=35
LockBytes=2 ServerGlobal=47
Xact=1670
Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
Error: 17803, Severity: 20, State: 14
Insufficient memory available..
LazyWriter: warning, no free buffers found.
Buffer Distribution: Stolen=80335 Free=0 Procedures=63
Inram=307141 Dirty=19250 Kept=0
I/O=0, Latched=1264, Other=0
Buffer Counts: Commited=408053 Target=408053 Hashed=327655
InternalReservation=1774 ExternalReservation=0 Min Free=512
Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
OS Committed=2341
OS In Use=2274
Query Plan=67512 Optimizer=17
General=11800
Utilities=29 Connection=2764
Global Memory Objects: Resource=8601 Locks=104
SQLCache=236 Replication=76
LockBytes=2 ServerGlobal=47
Xact=2809
Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356How about turning off AWE and make sure the /3GB switch is set in the
Boot.ini file.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> We are getting the following errror on SQL Server : We have about 4GB
> memory and has AWE enabled. Why are we getting these errors? We just
> increase the memory from 1 Gig to 4 Gig and we are assuming that now we
have
> sufficient memory.
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> Inram=292678 Dirty=20958 Kept=0
> I/O=0, Latched=1724, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> InternalReservation=525 ExternalReservation=16375 Min Free=512
> Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> OS Committed=2333
> OS In Use=2304
> Query Plan=77132 Optimizer=295
> General=11448
> Utilities=32 Connection=4067
> Global Memory Objects: Resource=9076 Locks=185
> SQLCache=375 Replication=35
> LockBytes=2 ServerGlobal=47
> Xact=1670
> Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
> Error: 17803, Severity: 20, State: 14
> Insufficient memory available..
> LazyWriter: warning, no free buffers found.
> Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> Inram=307141 Dirty=19250 Kept=0
> I/O=0, Latched=1264, Other=0
> Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> InternalReservation=1774 ExternalReservation=0 Min Free=512
> Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> OS Committed=2341
> OS In Use=2274
> Query Plan=67512 Optimizer=17
> General=11800
> Utilities=29 Connection=2764
> Global Memory Objects: Resource=8601 Locks=104
> SQLCache=236 Replication=76
> LockBytes=2 ServerGlobal=47
> Xact=2809
> Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356
>|||The /3GB was not set in the Boot.ini file. My fault, I assumed that our
hardware guys had already done it as it was a prerequisite, before they
handed over the server to the DBA's. Its all set now and will be monitoring
it for couple of days to make sure this error does not occur again.
We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
Thank you,
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> How about turning off AWE and make sure the /3GB switch is set in the
> Boot.ini file.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > We are getting the following errror on SQL Server : We have about 4GB
> > memory and has AWE enabled. Why are we getting these errors? We just
> > increase the memory from 1 Gig to 4 Gig and we are assuming that now we
> have
> > sufficient memory.
> >
> > LazyWriter: warning, no free buffers found.
> > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > Inram=292678 Dirty=20958 Kept=0
> > I/O=0, Latched=1724, Other=0
> > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > OS Committed=2333
> > OS In Use=2304
> > Query Plan=77132 Optimizer=295
> > General=11448
> > Utilities=32 Connection=4067
> > Global Memory Objects: Resource=9076 Locks=185
> > SQLCache=375 Replication=35
> > LockBytes=2 ServerGlobal=47
> > Xact=1670
> > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781 Available=68497
> >
> > Error: 17803, Severity: 20, State: 14
> > Insufficient memory available..
> > LazyWriter: warning, no free buffers found.
> > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > Inram=307141 Dirty=19250 Kept=0
> > I/O=0, Latched=1264, Other=0
> > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > OS Committed=2341
> > OS In Use=2274
> > Query Plan=67512 Optimizer=17
> > General=11800
> > Utilities=29 Connection=2764
> > Global Memory Objects: Resource=8601 Locks=104
> > SQLCache=236 Replication=76
> > LockBytes=2 ServerGlobal=47
> > Xact=2809
> > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356 Available=93356
> >
> >
>|||I hope you are using Enterprise Edition of SQL Server otherwise you won't be
able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
said 4GB) and want to set AWE make sure you set the max memory in SQL Server
to less than 9GB. I would advise maybe starting at 7GB and see how that
works out. You were runing with 1GB before. 9GB is a big jump, are you
sure you need that much. If you can't utilize all the memory you actually
make SQL Server do extra work.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> The /3GB was not set in the Boot.ini file. My fault, I assumed that our
> hardware guys had already done it as it was a prerequisite, before they
> handed over the server to the DBA's. Its all set now and will be
monitoring
> it for couple of days to make sure this error does not occur again.
> We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> Thank you,
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > How about turning off AWE and make sure the /3GB switch is set in the
> > Boot.ini file.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nags" <nags@.DontSpamMe.com> wrote in message
> > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > We are getting the following errror on SQL Server : We have about
4GB
> > > memory and has AWE enabled. Why are we getting these errors? We just
> > > increase the memory from 1 Gig to 4 Gig and we are assuming that now
we
> > have
> > > sufficient memory.
> > >
> > > LazyWriter: warning, no free buffers found.
> > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > Inram=292678 Dirty=20958 Kept=0
> > > I/O=0, Latched=1724, Other=0
> > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > OS Committed=2333
> > > OS In Use=2304
> > > Query Plan=77132 Optimizer=295
> > > General=11448
> > > Utilities=32 Connection=4067
> > > Global Memory Objects: Resource=9076 Locks=185
> > > SQLCache=375 Replication=35
> > > LockBytes=2 ServerGlobal=47
> > > Xact=1670
> > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
Available=68497
> > >
> > > Error: 17803, Severity: 20, State: 14
> > > Insufficient memory available..
> > > LazyWriter: warning, no free buffers found.
> > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > Inram=307141 Dirty=19250 Kept=0
> > > I/O=0, Latched=1264, Other=0
> > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > OS Committed=2341
> > > OS In Use=2274
> > > Query Plan=67512 Optimizer=17
> > > General=11800
> > > Utilities=29 Connection=2764
> > > Global Memory Objects: Resource=8601 Locks=104
> > > SQLCache=236 Replication=76
> > > LockBytes=2 ServerGlobal=47
> > > Xact=2809
> > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
Available=93356
> > >
> > >
> >
> >
>|||Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
Windows 2003.
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: )
We had enabled AWE and were restricting SQL Server at 4 GB to see how it
would perform. We were actually running out of memory before when we had
only 1 Gig. When we went for the new server we went to higher memory, as we
never know when we will need it :o)
On one of our production server we have 32 GB to pin objects into memory for
better performance.
Why do you say "If you can't utilize all the memory".. SQL Server will keep
using all the memory available and will cache all the objects and pages that
it will access. The more memory we have the more it will cache into memory,
probably with everything in memory rather than going to disk .. right ?
Over a period the data most frequently accessed will be in memory with
almost no access to disks .. right ?
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> I hope you are using Enterprise Edition of SQL Server otherwise you won't
be
> able to utilize more than 2GB of Ram anyway. If you have 9GB (before you
> said 4GB) and want to set AWE make sure you set the max memory in SQL
Server
> to less than 9GB. I would advise maybe starting at 7GB and see how that
> works out. You were runing with 1GB before. 9GB is a big jump, are you
> sure you need that much. If you can't utilize all the memory you actually
> make SQL Server do extra work.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > The /3GB was not set in the Boot.ini file. My fault, I assumed that our
> > hardware guys had already done it as it was a prerequisite, before they
> > handed over the server to the DBA's. Its all set now and will be
> monitoring
> > it for couple of days to make sure this error does not occur again.
> >
> > We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> >
> > Thank you,
> >
> > -Nags
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > > How about turning off AWE and make sure the /3GB switch is set in the
> > > Boot.ini file.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > > We are getting the following errror on SQL Server : We have about
> 4GB
> > > > memory and has AWE enabled. Why are we getting these errors? We
just
> > > > increase the memory from 1 Gig to 4 Gig and we are assuming that now
> we
> > > have
> > > > sufficient memory.
> > > >
> > > > LazyWriter: warning, no free buffers found.
> > > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > > Inram=292678 Dirty=20958 Kept=0
> > > > I/O=0, Latched=1724, Other=0
> > > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > > OS Committed=2333
> > > > OS In Use=2304
> > > > Query Plan=77132 Optimizer=295
> > > > General=11448
> > > > Utilities=32 Connection=4067
> > > > Global Memory Objects: Resource=9076 Locks=185
> > > > SQLCache=375 Replication=35
> > > > LockBytes=2 ServerGlobal=47
> > > > Xact=1670
> > > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
> Available=68497
> > > >
> > > > Error: 17803, Severity: 20, State: 14
> > > > Insufficient memory available..
> > > > LazyWriter: warning, no free buffers found.
> > > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > > Inram=307141 Dirty=19250 Kept=0
> > > > I/O=0, Latched=1264, Other=0
> > > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > > OS Committed=2341
> > > > OS In Use=2274
> > > > Query Plan=67512 Optimizer=17
> > > > General=11800
> > > > Utilities=29 Connection=2764
> > > > Global Memory Objects: Resource=8601 Locks=104
> > > > SQLCache=236 Replication=76
> > > > LockBytes=2 ServerGlobal=47
> > > > Xact=2809
> > > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
> Available=93356
> > > >
> > > >
> > >
> > >
> >
> >
>|||Two things here. First off pinning tables in memory may hurt performance
more than help. If the tables are accessed frequently then SQL Server has
an internal process to keep those pages in memory over less frequently
accessed data. By you pinning a table you run the risk of using memory for
data that although you accessed it once it may never be accessed again (or
infrequently) and that memory may have been better utilized by some other
tables data. In almost all instances you can not determine better than SQL
Server what should stay in cache and what should not given all the
circumstances the engine has to deal with. By the other statement of can't
utilize all the memory I meant this. Just because you have a 10 GB database
does not mean you will access all 10GB all the time. As a matter of fact in
a typical OLTP app only a fraction of the data is accessed each day. Maybe
less than 5 or 10% on average. But if you have 10GB of cache available SQL
Server will load what ever data you access into cache and it will stay there
forever until one of several things happen.
You restart SQL Server
You run DBCC DROPCLEANBUFFERS
The memroy is needed for something else.
So lets say over time you loaded all 10GB into memory but you only use say
1GB on any given day. SQL Server has to manage all 10GB of memory
regardless of how you use it. There are several processes that deal with it
but lets just take the lazy writer process. On a regular basis it scans all
10GB's of data in cache and does several things. One is it writes dirty
buffers to disk. Another is that it adjusts a counter on each page to help
it determine which page it can free up if needed. So it is doing theses
checks for 9GB of data that really will hardly if ever be touched but the
overhead is still there. If you only had say 1.5GB of memory you can keep
in all the most used data and leave a little for the infrequently used
stuff. If every so often you have to access the hard drive for this
infrequently used data it will overwrite the other infrequently used pages
and not the most accessed ones. This minimal drive access is usually not
that big a deal and you save a lot of overhead in hte engine for little
gain.
Now I am not saying you should only have a few extra bytes over and above
what you regulary use but you can get too excessive with extra memroy. Too
much is better than not enough in most cases but way too much is not good
either.
--
Andrew J. Kelly SQL MVP
"Nags" <nags@.DontSpamMe.com> wrote in message
news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
> Windows 2003.
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.2 (Build 3790: )
> We had enabled AWE and were restricting SQL Server at 4 GB to see how it
> would perform. We were actually running out of memory before when we had
> only 1 Gig. When we went for the new server we went to higher memory, as
we
> never know when we will need it :o)
> On one of our production server we have 32 GB to pin objects into memory
for
> better performance.
> Why do you say "If you can't utilize all the memory".. SQL Server will
keep
> using all the memory available and will cache all the objects and pages
that
> it will access. The more memory we have the more it will cache into
memory,
> probably with everything in memory rather than going to disk .. right ?
> Over a period the data most frequently accessed will be in memory with
> almost no access to disks .. right ?
> -Nags
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> > I hope you are using Enterprise Edition of SQL Server otherwise you
won't
> be
> > able to utilize more than 2GB of Ram anyway. If you have 9GB (before
you
> > said 4GB) and want to set AWE make sure you set the max memory in SQL
> Server
> > to less than 9GB. I would advise maybe starting at 7GB and see how that
> > works out. You were runing with 1GB before. 9GB is a big jump, are
you
> > sure you need that much. If you can't utilize all the memory you
actually
> > make SQL Server do extra work.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Nags" <nags@.DontSpamMe.com> wrote in message
> > news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > The /3GB was not set in the Boot.ini file. My fault, I assumed that
our
> > > hardware guys had already done it as it was a prerequisite, before
they
> > > handed over the server to the DBA's. Its all set now and will be
> > monitoring
> > > it for couple of days to make sure this error does not occur again.
> > >
> > > We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> > >
> > > Thank you,
> > >
> > > -Nags
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > > > How about turning off AWE and make sure the /3GB switch is set in
the
> > > > Boot.ini file.
> > > >
> > > > --
> > > > Andrew J. Kelly SQL MVP
> > > >
> > > >
> > > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > > > We are getting the following errror on SQL Server : We have
about
> > 4GB
> > > > > memory and has AWE enabled. Why are we getting these errors? We
> just
> > > > > increase the memory from 1 Gig to 4 Gig and we are assuming that
now
> > we
> > > > have
> > > > > sufficient memory.
> > > > >
> > > > > LazyWriter: warning, no free buffers found.
> > > > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > > > Inram=292678 Dirty=20958 Kept=0
> > > > > I/O=0, Latched=1724, Other=0
> > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > > > OS Committed=2333
> > > > > OS In Use=2304
> > > > > Query Plan=77132 Optimizer=295
> > > > > General=11448
> > > > > Utilities=32 Connection=4067
> > > > > Global Memory Objects: Resource=9076 Locks=185
> > > > > SQLCache=375 Replication=35
> > > > > LockBytes=2 ServerGlobal=47
> > > > > Xact=1670
> > > > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
> > Available=68497
> > > > >
> > > > > Error: 17803, Severity: 20, State: 14
> > > > > Insufficient memory available..
> > > > > LazyWriter: warning, no free buffers found.
> > > > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > > > Inram=307141 Dirty=19250 Kept=0
> > > > > I/O=0, Latched=1264, Other=0
> > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > > > OS Committed=2341
> > > > > OS In Use=2274
> > > > > Query Plan=67512 Optimizer=17
> > > > > General=11800
> > > > > Utilities=29 Connection=2764
> > > > > Global Memory Objects: Resource=8601 Locks=104
> > > > > SQLCache=236 Replication=76
> > > > > LockBytes=2 ServerGlobal=47
> > > > > Xact=2809
> > > > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
> > Available=93356
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||I totally agree, but with AWE enabled, SQL Server preallocates memory
instead of allocating as required.
And secondly, if we restrict SQL Server to start with low memory and we want
to increase it later, we have to restart the server. Sometimes, we cannot
restart for months.
We do have huge processing power, so the slight overhead for scanning the
memory should be ok.
-Nags
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:echyFUMeEHA.3476@.tk2msftngp13.phx.gbl...
> Two things here. First off pinning tables in memory may hurt performance
> more than help. If the tables are accessed frequently then SQL Server has
> an internal process to keep those pages in memory over less frequently
> accessed data. By you pinning a table you run the risk of using memory
for
> data that although you accessed it once it may never be accessed again (or
> infrequently) and that memory may have been better utilized by some other
> tables data. In almost all instances you can not determine better than
SQL
> Server what should stay in cache and what should not given all the
> circumstances the engine has to deal with. By the other statement of
can't
> utilize all the memory I meant this. Just because you have a 10 GB
database
> does not mean you will access all 10GB all the time. As a matter of fact
in
> a typical OLTP app only a fraction of the data is accessed each day. Maybe
> less than 5 or 10% on average. But if you have 10GB of cache available
SQL
> Server will load what ever data you access into cache and it will stay
there
> forever until one of several things happen.
> You restart SQL Server
> You run DBCC DROPCLEANBUFFERS
> The memroy is needed for something else.
> So lets say over time you loaded all 10GB into memory but you only use say
> 1GB on any given day. SQL Server has to manage all 10GB of memory
> regardless of how you use it. There are several processes that deal with
it
> but lets just take the lazy writer process. On a regular basis it scans
all
> 10GB's of data in cache and does several things. One is it writes dirty
> buffers to disk. Another is that it adjusts a counter on each page to
help
> it determine which page it can free up if needed. So it is doing theses
> checks for 9GB of data that really will hardly if ever be touched but the
> overhead is still there. If you only had say 1.5GB of memory you can
keep
> in all the most used data and leave a little for the infrequently used
> stuff. If every so often you have to access the hard drive for this
> infrequently used data it will overwrite the other infrequently used pages
> and not the most accessed ones. This minimal drive access is usually not
> that big a deal and you save a lot of overhead in hte engine for little
> gain.
> Now I am not saying you should only have a few extra bytes over and above
> what you regulary use but you can get too excessive with extra memroy.
Too
> much is better than not enough in most cases but way too much is not good
> either.
> --
> Andrew J. Kelly SQL MVP
>
> "Nags" <nags@.DontSpamMe.com> wrote in message
> news:eEs1R1LeEHA.3132@.TK2MSFTNGP11.phx.gbl...
> > Yes we have Enterprise Edition of SQL Server and Enterprise Edition of
> > Windows 2003.
> >
> > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> > Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
> Windows
> > NT 5.2 (Build 3790: )
> >
> > We had enabled AWE and were restricting SQL Server at 4 GB to see how it
> > would perform. We were actually running out of memory before when we
had
> > only 1 Gig. When we went for the new server we went to higher memory,
as
> we
> > never know when we will need it :o)
> >
> > On one of our production server we have 32 GB to pin objects into memory
> for
> > better performance.
> >
> > Why do you say "If you can't utilize all the memory".. SQL Server will
> keep
> > using all the memory available and will cache all the objects and pages
> that
> > it will access. The more memory we have the more it will cache into
> memory,
> > probably with everything in memory rather than going to disk .. right ?
> > Over a period the data most frequently accessed will be in memory with
> > almost no access to disks .. right ?
> >
> > -Nags
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:OfA5RqLeEHA.3756@.TK2MSFTNGP12.phx.gbl...
> > > I hope you are using Enterprise Edition of SQL Server otherwise you
> won't
> > be
> > > able to utilize more than 2GB of Ram anyway. If you have 9GB (before
> you
> > > said 4GB) and want to set AWE make sure you set the max memory in SQL
> > Server
> > > to less than 9GB. I would advise maybe starting at 7GB and see how
that
> > > works out. You were runing with 1GB before. 9GB is a big jump, are
> you
> > > sure you need that much. If you can't utilize all the memory you
> actually
> > > make SQL Server do extra work.
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > news:OWdxf9KeEHA.2764@.TK2MSFTNGP11.phx.gbl...
> > > > The /3GB was not set in the Boot.ini file. My fault, I assumed that
> our
> > > > hardware guys had already done it as it was a prerequisite, before
> they
> > > > handed over the server to the DBA's. Its all set now and will be
> > > monitoring
> > > > it for couple of days to make sure this error does not occur again.
> > > >
> > > > We put /3GB /PAE in boot.ini as we have 9Gig of RAM.
> > > >
> > > > Thank you,
> > > >
> > > > -Nags
> > > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > > news:eLZmSvKeEHA.3512@.TK2MSFTNGP12.phx.gbl...
> > > > > How about turning off AWE and make sure the /3GB switch is set in
> the
> > > > > Boot.ini file.
> > > > >
> > > > > --
> > > > > Andrew J. Kelly SQL MVP
> > > > >
> > > > >
> > > > > "Nags" <nags@.DontSpamMe.com> wrote in message
> > > > > news:%23Zw7f2JeEHA.1036@.TK2MSFTNGP10.phx.gbl...
> > > > > > We are getting the following errror on SQL Server : We have
> about
> > > 4GB
> > > > > > memory and has AWE enabled. Why are we getting these errors?
We
> > just
> > > > > > increase the memory from 1 Gig to 4 Gig and we are assuming that
> now
> > > we
> > > > > have
> > > > > > sufficient memory.
> > > > > >
> > > > > > LazyWriter: warning, no free buffers found.
> > > > > > Buffer Distribution: Stolen=91967 Free=0 Procedures=726
> > > > > > Inram=292678 Dirty=20958 Kept=0
> > > > > > I/O=0, Latched=1724, Other=0
> > > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=315360
> > > > > > InternalReservation=525 ExternalReservation=16375 Min Free=512
> > > > > > Procedure Cache: TotalProcs=186 TotalPages=726 InUsePages=726
> > > > > > Dynamic Memory Manager: Stolen=91784 OS Reserved=2384
> > > > > > OS Committed=2333
> > > > > > OS In Use=2304
> > > > > > Query Plan=77132 Optimizer=295
> > > > > > General=11448
> > > > > > Utilities=32 Connection=4067
> > > > > > Global Memory Objects: Resource=9076 Locks=185
> > > > > > SQLCache=375 Replication=35
> > > > > > LockBytes=2 ServerGlobal=47
> > > > > > Xact=1670
> > > > > > Query Memory Manager: Grants=2 Waiting=0 Maximum=85781
> > > Available=68497
> > > > > >
> > > > > > Error: 17803, Severity: 20, State: 14
> > > > > > Insufficient memory available..
> > > > > > LazyWriter: warning, no free buffers found.
> > > > > > Buffer Distribution: Stolen=80335 Free=0 Procedures=63
> > > > > > Inram=307141 Dirty=19250 Kept=0
> > > > > > I/O=0, Latched=1264, Other=0
> > > > > > Buffer Counts: Commited=408053 Target=408053 Hashed=327655
> > > > > > InternalReservation=1774 ExternalReservation=0 Min Free=512
> > > > > > Procedure Cache: TotalProcs=22 TotalPages=63 InUsePages=63
> > > > > > Dynamic Memory Manager: Stolen=80398 OS Reserved=2392
> > > > > > OS Committed=2341
> > > > > > OS In Use=2274
> > > > > > Query Plan=67512 Optimizer=17
> > > > > > General=11800
> > > > > > Utilities=29 Connection=2764
> > > > > > Global Memory Objects: Resource=8601 Locks=104
> > > > > > SQLCache=236 Replication=76
> > > > > > LockBytes=2 ServerGlobal=47
> > > > > > Xact=2809
> > > > > > Query Memory Manager: Grants=0 Waiting=0 Maximum=93356
> > > Available=93356
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>