?
Solved

buffer pool for this table is very high why?? and how to release it

Posted on 2008-06-18
10
Medium Priority
?
210 Views
Last Modified: 2010-03-19
as u can see a results from my memory query in sql2005 this table resulted me 3024111 of buffer count which is equivalent of 23GB of physical memory, i wonder why so big memory usage here? and how can I trace and reduce the emmory usage in this table, I check index ID =1 in this table and its in perfect conditions

database_id db        object_id   objname                          index_id    buffer_count
----------- --------- ----------- -------------------------------- ----------- ------------
6           Falcon     134291538   OS_TEMP                     1           3024111

and this table has 155million of rows
0
Comment
Question by:motioneye
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 400 total points
ID: 21819065
155M rows for 23GB makes, if I am not mistaken, some 160bytes average row length...
so, the full table (clustered index I presume) is loaded in memory.

this could happen if just before you run the memory query a full table scan was issued, or if really all the time, the queries perform full table scans (in case of clustered index presence a full index scan...).
check out and tune the queries for this table, as needed.
0
 

Author Comment

by:motioneye
ID: 21819179
Ok from my dbcc memory status here, is there any chance for me to tune memory properly??

Buffer Distribution            Buffers    
------------------------------ -----------
Stolen                         -29407
Free                           230830
Cached                         681696
Database (clean)               2697349
Database (dirty)               83463
I/O                            0
Latched                        0

(7 row(s) affected)

Buffer Counts                  Buffers              
------------------------------ --------------------
Committed                      3663931
Target                         3721932
Hashed                         2780812
Stolen Potential               3322406
External Reservation           0
Min Free                       256
Visible                        3721932
Available Paging File          617461

(8 row(s) affected)

Procedure Cache                Value      
------------------------------ -----------
TotalProcs                     48553
TotalPages                     578562
InUsePages                     9569

(3 row(s) affected)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 21819193
without knowing the specs, that would be difficult :)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 1600 total points
ID: 21820212
It shouldn't matter that SQL is using that much memory - it will give it back if something else needs it. SQL gets data off the disk and stores it in RAM for as long as possible. It's normal SQL Server behaviour. If you try to restrict how much memory it can use, you will only force it to go to the disk more often - slowing your system down a lot.

Rob
0
 

Author Comment

by:motioneye
ID: 21827889
Now the problem is we have some issue while trying to extract data through reporting tool, its always complaining not enough memory to run the operation, I understands this memory may more related with OS use, but since free memory leave only 500MB I guess that is not enough for this operation, so we can simply release the memory which being used by sql server ( buffer pool ) and I think of sql services restarted in order to clear off memory to OS. What do u think?
0
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 1600 total points
ID: 21827935
SQL should release the memory it's using. It's good that way.
0
 

Author Comment

by:motioneye
ID: 21830785
what I know and what I understands is sql server will never release memory once it grabbed it, it greedy in that sense, as we know that for faster query, should data read from memory compare to disk. That is why it never release memory back to OS. But I still thinking of any other way which is most proper way to handle the memory insteads of restart....
0
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 1600 total points
ID: 21837115
SQL Server _will_ release memory when another process needs it.
0
 

Author Comment

by:motioneye
ID: 21843490
<SQL Server _will_ release memory when another process needs it.> if this is always the case, why we need put more memory??? yes  I believe it works as is this but when the server are busy and sql server require more buffer pool,  the free memory that it release will not adequate to managing the query.
Ok if the OS need more memory, will it steal it from sql server???
0
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 1600 total points
ID: 21846256
More memory because whenever the database system is trying to service queries that use lots of data, it will want as much memory as it can. And it will prefer to leave that data in RAM, but will happily return it to the OS if the OS needs it (for another process).

My point is that you shouldn't try to LIMIT the amount of memory that SQL is able to use. If anything, you should get some more RAM for the server, so that SQL can use as much as it likes.

Rob
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question