Restore Database: memory

Showing posts with label memory. Show all posts

Wednesday, March 7, 2012

Help understanding the buffer cache and logical reads

I am trying to find out the memory usage for a particular query, and to
understand how data pages can be re-used in the buffer cache. I am using the
Total Server Memory counter (in performance monitor) and dbcc memusage in
analyzer.
I am trying to prove if the following is right. If I run a SELECT * FROM
Contact and cache the data pages. If I run subsequent queries involving the
same table will I incur any more overall memory usage because the necessary
data pages will already be in cache.
I suppose this test is difficult to do because of the inherent memory usage
associated with users connecting and and compiling and executing queries.
However, I am going down this route of restarting SQL Server to clear out
the buffer, and so that it starts off very small. Then, in query analyzer I
run a dbcc memusage (to see the top 20 items in cache and to check that it
hasn't got any tables in it)
dbcc dropcleanbuffers (so the cache is clean)
then I run a query, e.g. SELECT * FROM Contact
the results after running a dbcc memusage is that there are contact table
pages are in fact in the cache because the total buffer pool page count has
increased and you can see it in the table (by it's ID)
However, I've noticed that the logical reads is very high and physical reads
is 0. To me this indicates that the data is already in cache (i.e. the
contact table) because no physical reads had to be done to get them into
cache in the first place? How is this possible?
Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
read-ahead reads 4704.
I would have expected to see that the physical reads would be 5045?
Does anyone know more about this and if there is a better way to understand
the memory usage for a particular query and the effects of caching?
Read ahead is occurring, and when read ahead turns on it does larger reads
( perhaps an extent and maybe larger)...Logical reads is in pages, since
read ahead is reading larger blocks, there are reads.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
> I am trying to prove if the following is right. If I run a SELECT * FROM
> Contact and cache the data pages. If I run subsequent queries involving
the
> same table will I incur any more overall memory usage because the
necessary
> data pages will already be in cache.
> I suppose this test is difficult to do because of the inherent memory
usage
> associated with users connecting and and compiling and executing queries.
>
> However, I am going down this route of restarting SQL Server to clear out
> the buffer, and so that it starts off very small. Then, in query analyzer
I
> run a dbcc memusage (to see the top 20 items in cache and to check that it
> hasn't got any tables in it)
> dbcc dropcleanbuffers (so the cache is clean)
> then I run a query, e.g. SELECT * FROM Contact
> the results after running a dbcc memusage is that there are contact table
> pages are in fact in the cache because the total buffer pool page count
has
> increased and you can see it in the table (by it's ID)
>
> However, I've noticed that the logical reads is very high and physical
reads
> is 0. To me this indicates that the data is already in cache (i.e. the
> contact table) because no physical reads had to be done to get them into
> cache in the first place? How is this possible?
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
> I would have expected to see that the physical reads would be 5045?
> Does anyone know more about this and if there is a better way to
understand
> the memory usage for a particular query and the effects of caching?
|||Robert,
You will most likely confuse yourself by going that route. Look up "free
buffer pages" in BooksOnLine for details on how the cache works in more
detail. But in a nutshell sql server will keep data and indexes in cache as
long as you have enough free memory to do so. At some point when it runs
out of cache it will start to reuse the pages. Each page gets a counter
that determines which ones should be forced out to make room for new ones
over the others. This is determined by several factors including how often
you access it. So it is likely that if you run select * and read in a whole
table you can actually reuse portions of cache for the end of the table that
you just read in for the beginning of the table. Other times it will cache
the whole table. It depends. SQL Server does a really good job of
determining what should stay and what should get overwritten so you
shouldn't concern yourself with it. What you need to look at are the Buffer
Cache Hit Ratio and Page Life Expectancy counters. If the Cache hit ratio
is above say 95% your doing good. If the Page Life expectancy is low you
have a lot of memory pressure and could probably benefit from more ram or
most likely more optimization. Inside SQL Server 2000 also has a great
section covering this topic.
Andrew J. Kelly SQL MVP
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
> I am trying to prove if the following is right. If I run a SELECT * FROM
> Contact and cache the data pages. If I run subsequent queries involving
the
> same table will I incur any more overall memory usage because the
necessary
> data pages will already be in cache.
> I suppose this test is difficult to do because of the inherent memory
usage
> associated with users connecting and and compiling and executing queries.
>
> However, I am going down this route of restarting SQL Server to clear out
> the buffer, and so that it starts off very small. Then, in query analyzer
I
> run a dbcc memusage (to see the top 20 items in cache and to check that it
> hasn't got any tables in it)
> dbcc dropcleanbuffers (so the cache is clean)
> then I run a query, e.g. SELECT * FROM Contact
> the results after running a dbcc memusage is that there are contact table
> pages are in fact in the cache because the total buffer pool page count
has
> increased and you can see it in the table (by it's ID)
>
> However, I've noticed that the logical reads is very high and physical
reads
> is 0. To me this indicates that the data is already in cache (i.e. the
> contact table) because no physical reads had to be done to get them into
> cache in the first place? How is this possible?
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
> I would have expected to see that the physical reads would be 5045?
> Does anyone know more about this and if there is a better way to
understand
> the memory usage for a particular query and the effects of caching?
|||"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
>I am trying to find out the memory usage for a particular query, and to
> understand how data pages can be re-used in the buffer cache. I am using
> the
> Total Server Memory counter (in performance monitor) and dbcc memusage in
> analyzer.
.. . .
> Table 'CONTACT'. Scan count 1, logical reads 5045, physical reads 0,
> read-ahead reads 4704.
>
The thing you're missing is the read-ahead. There is a thread devoted to
read-ahead caching. When SQL Server decides that it may need to tablescan a
table, it sets the read-ahead thread to reading the pages into the cache.
Then when the execution plan of the query needs the pages, they may already
be in the cache.
Point is, the read-ahead reads are physical reads, they are just performed
by another thread.
David
|||I see, great.
So in fact physical reads are occuring, but this is shown by the read-ahead
read value. So if I start off with an empty cache or nearly empty cache, pin
a table and do a select * from table, then it might not appear as physical
reads, but as read-ahead reads (and from a previous reply these aren't shown
in pages, but could be extents, so the value is larger than the logical
reads)...and overall this achieves the same thing by placing the data in
cache for future anticipated reuse.
Next, I was going to find the total buffer page count before and after a
query and multiply this value by 8. Does this sounds feasible? Perhaps, is
there a better way to measure the memory use for a query?
"David Browne" wrote:

> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:E73CC96C-B913-4C1F-A58B-46A39E046838@.microsoft.com...
> .. . .
> The thing you're missing is the read-ahead. There is a thread devoted to
> read-ahead caching. When SQL Server decides that it may need to tablescan a
> table, it sets the read-ahead thread to reading the pages into the cache.
> Then when the execution plan of the query needs the pages, they may already
> be in the cache.
> Point is, the read-ahead reads are physical reads, they are just performed
> by another thread.
> David
>
>

Sunday, February 19, 2012

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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Wednesday, March 7, 2012

Help understanding the buffer cache and logical reads

Sunday, February 19, 2012

Help Required URGENT ! Out of Memory Error

Help Required URGENT ! Out of Memory Error

Help Required URGENT ! Out of Memory Error

Restore Database

Blog Archive

About Me