Solved

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

Posted on 2008-06-18
10
196 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
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

828 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