-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.

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
  • 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 '17 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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