March 19, 2009 at 1:21 am
Today I was asked to find the cache for sql server. The reason my manager wants to increase this size is because the SQLServer:Buffer Manager : Buffer cache hit ratio:: is very less around 45 to 58%.
We are using sql server 2005. To find out this value I executed sys.dm_os_sys_info and gave information of bpool_committed, bpool_commit_target and bpool_visible. Is this buffer information is same as cache.
I am not sure how to increase cache size. Can anyone help me out in this case?
-MJ
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
March 19, 2009 at 3:36 am
Hi,
The Buffer Cache, is an area within the Buffer Pool and is in simple terms the amount of your database data that is stored in memory.
So for example, if you have a 500MB database, a server with 4GB and SQL Server is configured to use all available memory, you will have a very good Buffer Cache Hit rachio of 99% or above becuase your entire database can easily fit into memory.
In order to determine you buffer cache hit ratio you can use the Windows Performance Monitor.
1. Just go to Start > Programs > Administrative Tools > Performance
2. Right click the graph and choose Add Counters.
3. Select Performance object, SQLServer:Buffer Manager
4. Add, Buffer Cache Hit Ratio
Make sense?
How big are your databases in total? How much memory do you have on your server?
Cheers,
March 19, 2009 at 7:26 pm
To view buffer pool size you are going on the right track
The DMV sys.dm_os_sys_info can be used to view the amount of buffer memory, bpool_commit_target show "target" memory limit for the buffer pool and bpool_committed displays the amount of memory currently committed to the buffer pool( both number represent the number of 8 KB pages).
MJ
March 19, 2009 at 9:02 pm
Check one other thing please for me - PageLifeExpectancy. It's also in the SQL Server Buffer manager, and is the "Number of seconds a page will stay in the buffer pool without references". If it's less than 750, you need more RAM. 5k range is pretty good, and I've seen servers in the 20-30k range (Very niiiiiiiiiiiiiiiiice). 😀
March 20, 2009 at 12:30 am
I have two questions now:
The server has 32 GB RAM. The sql server memory = 32766 MB. (I checked this in the property of the sql server)
Now when I run the command : SELECT * FROM SYS.DM_OS_SYS_INFO then the following information is observed:
physical_memory_in_bytes virtual_memory_in_bytes bpool_committed bpool_commit_target bpool_visible
-------------------- ----------- -------------------- ----------------- ------------------------ -
17177374720 2147352576 1728512 1728512 182272
How can we relate between 'sql server memory = 32766 MB' and the information from SYS.DM_OS_SYS_INFO
I executed following commands but the value of bpool_committed is not changed. Can anyone tell my why did that happen?
dbcc freeproccache
dbcc freesessioncache
-MJ
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
March 20, 2009 at 12:35 am
Page life expectancy counter value is 1:40. I don't understand how this value would be 750 or 5K. This is the duration when a page can remain in buffer pool without references.
Anyway what is the meaning of references here?
-MJ
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
March 20, 2009 at 3:32 am
Hi Michael,
The DBCC statements you issued will not reduce the size of the buffer pool (the specific area of SQL Server memory that is used to store data pages).
DBCC freeproccache: Releases any cached query execution plans from memory.
DBCC FREESESSIONCACHE: Flushes the distributed query connection cache used by distributed queries against an instance of Microsoft SQL Server.
To answer your second question, the ‘sql server memory = 32766 MB’ is a SQL Server setting that states SQL Server is permitted to use up to 32GB of memory if it requires to do so.
Inspecting the DMV SYS.DM_OS_SYS_INFO shows that your server has:
physical_memory_in_bytes = 17177374720 bytes = 15.9976768 GB
bpool_committed = 1728512 = the number of currently committed pages in the buffer pool not their size.
To get the current size of the Buffer Pool, multiply the number of pages by 8(kb) so your current buffer pool is
1728512 X 8kb = 1.6484375 gigabytes
In order to get a detailed report of SQL Servers memory usage I like to use the DBCC statement:
DBCC MEMORYSTATUS
Hope this helps.
Cheers,
March 20, 2009 at 10:42 am
I say it needs more RAM, but that's not necessarily the case. Use many counters and tests to conclude this, not just one. Sorry.
Ok - so your page life is what? 140??? 1:40? Its an integer value, last time that I checked in seconds.
March 26, 2009 at 12:24 am
Thanks all. The info give by John is really useful.
-MJ
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
March 26, 2009 at 2:45 am
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply