SQL Server Performance Forum – Threads Archive

log cache hit ratio is less..help me

Hi, I am a Oracle DBA..recently started supporting SQL Server Databases..
Started to learn the basics of sql server.
I have few performance problems..
I notice that the log cache hit ratio Counter is only 50%.. 1) What has to be done for this? 2) IF i have to increase the log cache, how can i do that? 3) Is it possible to increase the log cache alone or it is internally allocated from the entire memory.
Answers, comments and suggestions are most welcome Thanks,
sai.

---

Where you do see this counter? I never heard of log cache counter… Check the following…
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
http://www.sql-server-performance.com/performance_monitor_counters_sql_server.asp
http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Log cache hit?. May be: Buffer cache hit ratio.
Procedure cache hit ratio. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

Hi, The counter is log cache hit rato only.
give below for your reference. Log Cache Hit Ratio – Percentage of log cache reads satisfied from the log cache. You can find it in MSSQL Server online books.
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a7f9e7d4-fff4-4c72-8b3e-3f18dffc8919.htm
Cheers,
sai.

---

Ok, I found it. My system show 80%. What % do you have?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

Hi, My system shows only 50.12%.. Thanks,
sai.

---

Try to follow the following instructions to get more information about performance: http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=19594
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

the whole idea of approaching performance counters as having "acceptable" ranges is totally worthless and amount to an excercise is chasing meaningless specs kind of like thd distortion in stereo equipment,
its possible to make this really really low,
but your stereo does not sound any better
think about it
what should log cache read hits be? 1. if you do not backup your log or rollback transactions,
who cares? because you never read from log,
the hit ratio will be based on one or 2 reads, 2. if you do backup your logs,
then its quite likely your log write was written to disk long ago,
so of course your read will miss cache,
the most likely case of a high hit rate i can guess at is that
you have tx rollbacks,
in which case the log write is still in cache,
and now you have to roll back, ie, read from cache
just focus on counters that actually useful meaning

---

Hi, Thanks for the link, IF you could let me know the specific solution to improve the log cache hit ratio..that would be really helpful. Thanks,
Sai.

---

I suppose you post same time than Joe’s post.
Did you read it?
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

Hi, Yes, I read it, I understand that completely..,
Log cache hit ratio is depicting whether the rollback transactions can find the data in cache or log file.. In that case this counter is not at all useful in terms of performance tuning. Pls correct me if i am wrong. cheers,
sai.

---

That is right.
Most important is other counter to take care. You can find those counters in the link I already post.
Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

Hi, Could anyone explain me the cycle of Transaction Log file. I came to know that it takes care of redo log as well as Transaction Rollback in SQL Server. Once you fire a Transaction and commit it enters into the log cache and after it is flushed it goes into transaction log., Pls explain me the process of truncations, archival, growth, shrink . Thanks,
Sai.

---

http://www.akadia.com/services/sqlsrv_programming.html
http://www.awprofessional.com/articles/article.asp?p=26657
Also refer to the books online in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Good to know about Log Cache Hit Ratio
I never used it… I don’t think this counter is useful in any way to troubleshoot sql server issues… I don’t think there is any way to control the "Log Cache Hit Ratio"….
What is the "buffer cache hit ratio" ? MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Buffer cache hit ratio: Percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since an instance of SQL Server was started. After a long period of time, the ratio moves very little. Because reading from the cache is much less expensive than reading from disk, you want this ratio to be high. Generally, you can increase the buffer cache hit ratio by increasing the amount of memory available to SQL Server. Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

Thanks Luis…. I was asking the question to saikrishna about what is the "Buffer cache hit ratio" value not the defination… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

[:I][:I][:I] Luis Martin
Moderator
SQL-Server-Performance.com All in Love is Fair
Stevie Wonder
All postings are provided “AS IS” with no warranties for accuracy.

---

]]>