transactions per second multiple databases

Question

transactions per second multiple databases

sqlguy80

SSCrazy

Points: 2765
More actions
October 31, 2017 at 8:56 am

#409738

Hi,
We have a server with 300 databases and need to know correctly the tranactions /second of each database for the network Team.
Any T-SQL code from gurus?
I found online and it gave wrong output.
SELECT * from sys.dm_os_performance_counters where object_name='SQLServer:Databases' and counter_name='Transsactions/sec'

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

that's the right query, but you truncated the trailing spaces in counter_name, they are needed for the query to work:
SELECT * from sys.dm_os_performance_counters where object_name='SQLServer:Databases' and counter_name='Transactions/sec '
also note that they are incremental counters, so to find transactions for a duration such as per second or per minute, you need to capture the data twice and subtract.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 2

sqlguy80 - Tuesday, October 31, 2017 8:56 AM
Hi,
We have a server with 300 databases and need to know correctly the tranactions /second of each database for the network Team.
Any T-SQL code from gurus?
I found online and it gave wrong output.
SELECT * from sys.dm_os_performance_counters where object_name='SQLServer:Databases' and counter_name='Transsactions/sec'

I'm slightly confused, what have tcp's to do with the network team???
😎
Network interfaces (NICs) are just like any other IO interfaces on SQL Server, the control is down to the OS, you will only get the SQL Server perception of what is happening, not the bigger picture.

sqlguy80 SSCrazy Points: 2765 More actions · Answer 3

Thanks SSC Guru. they wanted to see some numbers to justify increase of bandwidth, to move about 300 databases from one location to another geographical location.
What counters can justify an increased bandwidth ? except size of 3 TB total data?

sqlguy80 SSCrazy Points: 2765 More actions · Answer 4

Hi,
OK I used this query and then waitfor delay 00:01:00 and re run the same and got values.Subtracted 1st value from 2nd to get the transactions / second.

What is the value

1121057112 ? The long number

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

sqlguy80 - Tuesday, October 31, 2017 6:39 PM
Hi,
OK I used this query and then waitfor delay 00:01:00 and re run the same and got values.Subtracted 1st value from 2nd to get the transactions / second.
What is the value
1121057112 ? The long number

Considering that, I think you may be looking at the wrong transactions. I believe they're looking for user transactions and not system transactions. It's not likely that you have 1.12 Billion user transactions per minute.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jasona.work SSC Guru Points: 50117 More actions · Answer 6

sqlguy80 - Tuesday, October 31, 2017 2:41 PM
Thanks SSC Guru. they wanted to see some numbers to justify increase of bandwidth, to move about 300 databases from one location to another geographical location.
What counters can justify an increased bandwidth ? except size of 3 TB total data?

I would think if the network team is looking to justify an increase in network bandwidth, they'd be more interested in the overall network traffic to / from the server. Transactions/Sec, I believe, will give you a general idea as to how *busy* a particular database might be, but there could still be very little (comparatively) network traffic.

As for collecting information on the network usage, that would be done through the OS, not SQL, using Performance Monitor counters, most likely network bytes sent / received (don't have the actual counter names handy.)

OK, just re-read your message above. If you're looking to move 300 databases / ~3TB of data to a new location, you're going to either want as much bandwidth as possible, or do something like backup the data to an external drive and overnight it to the new location to be restored.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

jasona.work - Wednesday, November 1, 2017 6:42 AM
sqlguy80 - Tuesday, October 31, 2017 2:41 PM
Thanks SSC Guru. they wanted to see some numbers to justify increase of bandwidth, to move about 300 databases from one location to another geographical location.
What counters can justify an increased bandwidth ? except size of 3 TB total data?
I would think if the network team is looking to justify an increase in network bandwidth, they'd be more interested in the overall network traffic to / from the server. Transactions/Sec, I believe, will give you a general idea as to how *busy* a particular database might be, but there could still be very little (comparatively) network traffic.
As for collecting information on the network usage, that would be done through the OS, not SQL, using Performance Monitor counters, most likely network bytes sent / received (don't have the actual counter names handy.)
OK, just re-read your message above. If you're looking to move 300 databases / ~3TB of data to a new location, you're going to either want as much bandwidth as possible, or do something like backup the data to an external drive and overnight it to the new location to be restored.

Heh... that last part is exactly what I had them do for one company I used to work at. They kept whining that they couldn't copy the data fast enough to be able to do it over a weekend. They wasted hundreds of FTE and Consultant hours trying to get it done. The plane ticket and disk did the trick but it took them two tries because the dummies recorded the wrong backups the first time because they didn't actually read the plan that I made for them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqldbm12 SSC Rookie Points: 28 More actions · Answer 8

You can use the "Batch Requests/Sec." PMON counter for an overall measure of database requests processed per second. That may or may not be a transaction from a perspective of a business transaction since a transaction may consist of multiple database requests and there may be different types of transactions.You can use the "Batch Requests/Sec." PMON counter for an overall measure of database requests processed per second. That may or may not be a transaction from a perspective of a business transaction since a transaction may consist of multiple database requests and there may be different types of transactions.