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