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

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
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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.
motioneyeAuthor Commented:
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)
Guy Hengel [angelIII / a3]Billing EngineerCommented:
without knowing the specs, that would be difficult :)
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

rob_farleyConnect With a Mentor Commented:
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.

motioneyeAuthor Commented:
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?
rob_farleyConnect With a Mentor Commented:
SQL should release the memory it's using. It's good that way.
motioneyeAuthor Commented:
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....
rob_farleyConnect With a Mentor Commented:
SQL Server _will_ release memory when another process needs it.
motioneyeAuthor Commented:
<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???
rob_farleyConnect With a Mentor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.