Transactions Per Second

Question

Transactions Per Second

growl

Hall of Fame

Points: 3068
More actions
December 10, 2003 at 3:32 pm

#358770

Hi I'm doing some performance monitoring and was wondering if someone could shed some light on this counter? In particular what is considered low and what is considered high?
Or what is the maximum sql server can handle?
Thanks

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

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

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 1

low and high depend on your system. I've had 11k/sec for some systems that maxed the server and 100/sec for other systems.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/

http://www.dkranch.net

growl Hall of Fame Points: 3068 More actions · Answer 2

Does the transaction per second refer to kb or number of transactions?

If it's number then what counter do use to see the size in kb?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 3

it's number of transactions .. to see throughput you'd really need to measure pages/sec or throughput on the disk system in read and write bytes/sec

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

doc_sewell SSC Eights! Points: 850 More actions · Answer 4

Yes, it is relative. Steve, do you know of any TSQL commands to get the TPS?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

Yep.

SELECT cntr_value, *

FROM sys.dm_os_performance_counters

WHERE counter_name = 'transactions/sec'

AND OBJECT_NAME = 'SQLServer:Databases'

AND instance_name = 'AdventureWorks'

http://msdn.microsoft.com/en-us/library/ms187743%28v=SQL.100%29.aspx

doc_sewell SSC Eights! Points: 850 More actions · Answer 6

I see, you still have to run counters. But our clients often refuse to do that. Is there any other query that can yield TPS without running counters?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 7

Not that I know of. The counters have to be captured somehow. If clients don't want to do that, then I guess there's nothing to be done.

DinaDBA Valued Member Points: 73 More actions · Answer 8

Hi Steve,

Do you know how to measure the max of a given server - (instead of finding out the hard way)? So that the TPS is less relative and more interpretive?

I just measured various databases for a test server I set up and they range between TPS of 1 million and zero. Oddly msdb has 1,160,005 which seems incredibly large. I am trying to figure out what to make of these numbers. Thank you!

Edited:

I believe I found my own answer: The entire script is here: http://stackoverflow.com/questions/5019593/sql-server-2008-transaction-per-second-for-specific-database