-1

I am new to SQL - I am trying to find out the transaction loads on my SQL Server databases. Any ideas on how I can do this?

I am using SQL Server 2012 but I also have instances running on SQL Server 2008.

Improve this question

1 Answer 1

Reset to default
2

You can look in the system dmv for os performance counters. With many of the counters, you have to collect the value, wait some time, then collect it again to get the number of transactions that have happened during that time frame. Something like this will work:

declare @tps  int = 0
declare @tps2 int = 0

select @tps = cntr_value from sys.dm_os_performance_counters where counter_name  = 'Transactions/sec' and instance_name like '_Total%'

waitfor delay '00:00:05'
select @tps2 = cntr_value from sys.dm_os_performance_counters where counter_name  = 'Transactions/sec' and instance_name like '_Total%'

select @tps, @tps2, (@tps2-@tps)*1.0/5
Improve this answer
1
  • These counters are also available to collect through PerfMon. Batches/s is probably another useful counter. Note they're per instance, so you may need to get multiple per server. Tracking them over 24 hours on a couple of days a week to get a baseline of max/min periods would be a good idea too.
    – Gareth Lyons
    Mar 7, 2017 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.