Ben and Lijun;

You are correct that the counter value as presented in the dmv is
cumulative, not the already calculated value for transactions/sec as
presented in perfmon. The value for the perfmon counter Trans/sec needs to be
further calculated.

Linchi

Hi Linchi,

This value is cumulative. See the note on sys.dm_os_performance_counters on
BOL.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica

I'm not sure what exactly the BOL says. I believe it refers to the raw
counter values before they are cooked into transactions/sec.

But if you have a counter that is transactions/sec, and you take two samples
of this counters, say, you get 500 transactions/sec at T1 and 600
transactions/sec 5 seconds later, how can you conclude that the total number
of transactions in that 5-second period is 100 transactions? What if your
second sample of the counter turns out to be 400 transactions/sec, are you
going to conclude that the total number of transactions taking place in that
5-second period is -100?

Linchi

It does give you the total number. I got this from Books Onling:

For per-second counters, this value is cumulative. The rate value must be
calculated by sampling the value at discrete time intervals. The difference
between any two successive sample values is equal to the rate for the time
interval used.

Lijun

That difference doesn't give you the total number of transactions for the
period, does it? Note that the counter is transactions per second. You may be
better off taking multiple samples of the counter, get the average, and then
multiply the average trans/sec by the total number of seconds in the period
to get an estimate of the total number of transactions.

Ultimately, how you get the total number of transactions depends on what you
want to use the number for, and what you mean by 'a transaction'. If you just
want to get a rough feel, the baove method is fine. But if you are doing some
type of performance benchmarking and want to be precise, you may have to
track your transaction count from the client side.

Linchi

I would run this at the beginning and end of that period and get the
difference

select cntr_value from sys.dm_os_performance_counters
where object_name = 'SQLServer:Databases'
and counter_name = 'Transactions/sec'
and instance_name = '_Total'

For SQL Server 2000 use sysperfinfo instead.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica

Does anybody know how to get the total number of transactions for a period
of time on a SQL Server?
Thanks,

Lijun