Monitor Cache Hit Ratio and Active xactions

Question

Monitor Cache Hit Ratio and Active xactions

Jpotucek

SSCoach

Points: 18583
More actions
January 9, 2006 at 8:48 am

#188838

Hi,
We have a product from Mercury called SiteScope that monitors our Servers.
It monitors all kinds of stats on SQL Server such as.....
Cache Hit Ratio = 75.91%,
Cache Pages = 62605,
Cache Object Counts = 15952,
Cache Use Counts/sec = 0.59/sec,
Active Transactions = 4.294E9
Transactions/sec = 0.00/sec
Log Cache Hit Ratio = 78.77%,
It keeps Throwing alerts because it reads Active Transactions at 4.294E9
(which looks like a scientific expression) - the threshhold for this monitor is set to display an alert if the Active Server transactions is >= to 1000
I'm not sure where to start looking on the SQL Server to see if there are any problems.. I've checked the Server Performance.. it looks OK, SQL Server and event logs don't show any problems.. Does anyone have any ideas????? Please!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

4.294E9 = 4,294,000,000. When you do a check using Perfmon or query sysperfinfo, what do you get back for Active Transactions, the same number?

K. Brian Kelley
@kbriankelley

Jpotucek SSCoach Points: 18583 More actions · Answer 2

When I run Perfmon, I choose SQL Server and Active Transactions and add

_TOTAL to the instances to monitor, it shows active xactions at

4294967295. It also shows master DB red-lining, but I can't it to display any active xactions for master db - weird.

select * from sysperfinfo where counter_name = 'Active Transactions'

shows ALL Databases with 0 xactions, 1 for tempdb and -1 for something called _Total?? Not sure what this _Total refers to. I see this in Perfmon too and it is the one that has active xactions at

4294967295

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

Ok, what you've described fits the scenario described in the following Knowledge Base article:

FIX: You may notice incorrect values for the "Active Transactions" counter when you perform multiple transactions on an instance of SQL Server 2000 that is running on an SMP computer

The problem is fixed in SP4 but if you aren't on that you can probably contact Microsoft for the individual hotfix (although this may have a fee associated with it due to the support call). If you have a Premier contract with Microsoft, you should be able to download the hotfix without issue.

K. Brian Kelley
@kbriankelley

Jpotucek SSCoach Points: 18583 More actions · Answer 4

Thank you so much Brian!!!!!!!!!!!

We are on a W2K Server with SQL Server 2000 standard patched up to 3a.

Would you recommend patching up to sp4 or should I just go for the hotfix for this particular problem? up until now, i have not had a good reason to patch up to SP4 - and to tell you the truth I've been leary... If I do patch up tp sp4, are there any post SP4 patches that I need to be aware of?????

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

I would not apply SP4 (or any SQL Server service pack, for that matter) without testing it first in a non-production environment and making sure it doesn't break anything, however that would be the preferred method if you give Microsoft a call. There is a post SP4 patch for AWE memory, so if you're using AWE, you definitely need to apply that hotfix.

K. Brian Kelley
@kbriankelley

Jpotucek SSCoach Points: 18583 More actions · Answer 6

Thanks again. I would definitely test the service pack first.. and I am running SQL Server Standard so the AWE memory stuff wouldn't apply.