Solved

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

Posted on 2008-06-18
10
204 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 100 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
Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 400 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 400 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 400 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 400 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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

705 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