Solved

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

Posted on 2008-06-18
10
184 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
Comment Utility
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
Comment Utility
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
without knowing the specs, that would be difficult :)
0
 
LVL 14

Assisted Solution

by:rob_farley
rob_farley earned 400 total points
Comment Utility
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
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 14

Assisted Solution

by:rob_farley
rob_farley earned 400 total points
Comment Utility
SQL should release the memory it's using. It's good that way.
0
 

Author Comment

by:motioneye
Comment Utility
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 14

Assisted Solution

by:rob_farley
rob_farley earned 400 total points
Comment Utility
SQL Server _will_ release memory when another process needs it.
0
 

Author Comment

by:motioneye
Comment Utility
<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 14

Assisted Solution

by:rob_farley
rob_farley earned 400 total points
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now