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
database_id db object_id objname index_id buffer_count
----------- --------- ----------- --------------------------
6 Falcon 134291538 OS_TEMP 1 3024111
and this table has 155million of rows
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
without knowing the specs, that would be difficult :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
<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???
Ok if the OS need more memory, will it steal it from sql server???
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)