SQL Server – How to Find Number of Transactions Per Day

sql serversql-server-2008-r2sql-server-2012sql-server-2016windows-server

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.

Best Answer

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

Related Solutions

Sql-server – What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself

SQL Server does not expose performance counters for any user defined object.

In fact PerfMon architecture is not a suitable platform for highly dynamical changes. If SQL Server would provide such feature it has to create counters every time a SP created. Think about SP renames, restores etc.

A couple of suggestions for monitoring problem of yours would be :

Creating a wrapper stored procedure for Adam Machanic's sp_WhoIsActive to filter out sessions you are not concerned about and apply practices at How to grant permissions for non-dbas section of sp_AskBrent from Brent Ozar to that stored procedure.
You can log activities to a table using an agent job like every 15 second execute sp_WhoIsActive filter out unwanted records from resultset log them to a table then let GUI generate reports from that table. With a quick search while i'm writing this answer i have come across a blog post from Brent Ozar. I didn't read the whole post but the idea is same.

How to Get Table-wise Number of Deadlocks in SQL Server

This is pretty easy to do using the xml_deadlock_report in the system_health Extended Event session.

IF OBJECT_ID('tempdb..[#DeadlockData]') IS NOT NULL
    DROP TABLE #DeadlockData;

WITH xml
AS ( SELECT CAST(event_data AS XML) AS XMLDATA
     FROM   sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL) )
SELECT xml.XMLDATA
INTO   #DeadlockData
FROM   xml
WHERE  xml.XMLDATA.value('(/event/@name)[1]', 'varchar(255)') = 'xml_deadlock_report';


WITH death_indeed
AS ( SELECT ca.x.value('@objectname', 'VARCHAR(256)') AS object_name
     FROM   #DeadlockData AS dd
     CROSS APPLY dd.XMLDATA.nodes('//resource-list/*') AS ca(x) )
SELECT   death_indeed.object_name, COUNT_BIG(*) AS deaths
FROM     death_indeed
GROUP BY death_indeed.object_name

After creating some different deadlocks, I get this back:

object_name                deaths
StackOverflow.dbo.Users    9
StackOverflow.dbo.Posts    9

Best Answer

Related Solutions

Sql-server – What query specific counters / metric if any are exposed for monitoring outside of SQL Server itself

How to Get Table-wise Number of Deadlocks in SQL Server

Related Question