SQL Server - Transactions per second

_{Thursday, October 22, 2015 1:37 PM}

Hi,

I have a requirements to collect Transactions per second from a sql server instances level.

Can you please provide me a script to collect TPS in the instance level ?

Thanks in advance.

Thanks,

Vinodh Selvaraj

_{Thursday, October 22, 2015 4:30 PM ✅Answered}

What is the definition of a transaction in this query?

Does each SQL statement (select, insert, update, delete, merge) not explicitly bound in BEGIN and COMMIT TRANSACTION increment by 1?

Do statements bound by BEGIN and COMMIT TRANSACTION increment by 1?

Thanks.

Tom G.

Good call Tom.  The SQLServer:Databases\Transactions/sec counter only shows those that are explicitly run with BEGIN TRAN.

If the user is looking for any query, perhaps Batch Requests/sec is a better choice.

Consider the following:

--BEGIN TRAN
BEGIN TRAN
    SELECT top 1 * FROM Person.Address
    SELECT top 1 * FROM Person.Address
COMMIT
GO 50


--NO BEGIN TRAN
SELECT top 1 * FROM Person.Address
SELECT top 1 * FROM Person.Address
GO 50

Using the code above you will find that when the code with no BEGIN TRAN is run, no transactions/sec are reported. You'll also notice that the Batch Requests/sec line stays the same when the BEGIN TRAN code is run, showing that it reports all batches that were run instead of just batches that were run under the context of an explicit transaction using BEGIN TRAN.

In either case Batch Requests/sec still shows the same amount. So I would think this is the most accurate value to measure, unless you are looking strictly for the number of "transactions" that were started explicitly with BEGIN TRAN.

I hope that helps!!

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

_{Thursday, October 22, 2015 1:49 PM}

For this you can use the Performance Counter SQL Server, Databases Object => Transactions/sec

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Thursday, October 22, 2015 2:27 PM}

Get the counter : "MSSQL:Databases > Transactions/sec" in perfmon.exe, or run this query : 

select Object_name AS instance, instance_name AS DB, cntr_value
from sys.dm_os_performance_counters
WHERE counter_name LIKE 'Transactions/sec%'
AND object_name LIKE '%Databases%'

_{Thursday, October 22, 2015 4:12 PM}

You can also use the system.diagnostics PerformanceCounter Class in .NET

https://msdn.microsoft.com/en-us/library/system.diagnostics.performancecounter(v=vs.110).aspx

http://www.codeproject.com/Articles/8590/An-Introduction-To-Performance-Counters

The key to using .NET for this is that you can get more than just the SQL counters. TPS doesn't really tell you much if it's the only thing you collect and you have a problem. You'll want batch requests/sec, page life expectancy, disk, memory, network, processor, process, and a whole host of other counters to determine a dip in TPS if something goes wrong.

You can also look into monitoring tools like Solar Winds, SQL Sentry, Microsoft System Center Operations Manager (SCOM), or simply just use a data collector in PERFMON.

Ultimately the others have already shown that it's transactions/sec in the SQL counters.

One last tidbit:  The object SQLSERVER:Databases is only valid for a default instance. This name will change to MSSQL$[INSTANCE NAME]:Databases when you have a named instance. So please don't hard code anything and expect it to work in all scenarios.

I hope that helps!!

I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

_{Thursday, October 22, 2015 4:15 PM}

What is the definition of a transaction in this query?

Does each SQL statement (select, insert, update, delete, merge) not explicitly bound in BEGIN and COMMIT TRANSACTION increment by 1?

Do statements bound by BEGIN and COMMIT TRANSACTION increment by 1?

Thanks.

Tom G.