Total Number of Transactions

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lijun Zhang - 27 Dec 2007 22:41 GMT

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

Lijun

Reply to this Message

Ben Nevarez - 27 Dec 2007 23:07 GMT

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

Reply to this Message

Linchi Shea - 28 Dec 2007 16:42 GMT

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
[quoted text clipped - 17 lines]
> >
> > Lijun

Reply to this Message

Lijun Zhang - 28 Dec 2007 17:49 GMT

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
[quoted text clipped - 38 lines]
>> >
>> > Lijun

Reply to this Message

Linchi Shea - 28 Dec 2007 23:19 GMT

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:
>
[quoted text clipped - 47 lines]
> >> >
> >> > Lijun

Reply to this Message

Ben Nevarez - 29 Dec 2007 00:35 GMT

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.
[quoted text clipped - 60 lines]
> > >> >
> > >> > Lijun

Reply to this Message

Linchi Shea - 29 Dec 2007 04:28 GMT

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,
>
[quoted text clipped - 71 lines]
> > > >> >
> > > >> > Lijun

Reply to this Message