0

I have to migrate my SQL Server on a particular public cloud, which is charging/licensing on the basis of number of transactions happening on the SQL Server.

I am aware of the Performance Counters for SQL Server:Databases - Transactions/Sec AND Batches/Sec.

Do I simply convert these transactions per second data to hours, or is there some other method to find out total number of transactions going on in SQL Server.

  • 3
    Are they charging per actual database transaction? This seems odd as a database transaction is not a specific unit of work. It could be 1 ms of CPU time and a few bytes of data or it could be hours of CPU time and GB of data in a single transaction. – HandyD Mar 29 '19 at 5:19
  • That's like having a taxi that charges the same for 1km as for 3000km! BEGIN SELECT 1; COMMIT; is a transaction as could be INSERTing several GB! – Vérace Mar 29 '19 at 5:51
  • I am not sure about the costing part, but what I was asked is to find out is total number of transactions on the server per day basis. I have found this on Microsoft Forum: social.msdn.microsoft.com/Forums/sqlserver/en-US/…. Is this the correct approach? – Shaurya S Mar 29 '19 at 7:02
  • Are you sure that they aren't referring to Storage Transactions? (IO operations) – George.Palacios Mar 29 '19 at 8:48
4

The Perfmon counters for Transactions/Sec and Batch Requests/Sec are cumulative counters, meaning they keep going up after the SQL Server starts up.

To get a count per day since startup, divide the counter output by the number of days that the server has been up. An easy way to do that is to check the creation date of TempDB - here's an example query, but keep in mind that rounding will be a problem if the server's been up for less than a day or two:

SELECT DATEDIFF(dd, create_date, GETDATE()) 
FROM sys.databases 
WHERE database_id = 2;

To get daily numbers going forward, consider saving the contents of that counter to a table. Then you can take a day's metric, subtract the day before, and you'll get the counter delta for that one day. (I use sp_BlitzFirst and the Power BI Dashboard for DBAs to do this with 15-minute granularity. (Disclaimer: those are open source tools that I wrote.))

Unfortunately, if you try to do daily sampling, it can be inaccurate due to:

  • The server restarting or clustered SQL Server service failing over
  • The Availability Group or database mirror transitioning roles (failing over) does not reset those counters, but you'll need to add up the transactions or batches from both replicas
  • If you're combining metrics from multiple replicas, keep in mind that you've also probably got monitoring software, and the monitoring software's queries are also going to be counted

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.