Post reply

what is the underlying base for "Transactions Per Second" in SQL

  • August 28, 2015 at 11:19 am

    #398959

    I'm aware of how to select FROM sys.dm_os_performance_counters, and over a given interval, calculate the the transactions per second. plenty of tools show this as a measure of server health.

    my question is what "counts" as a transaction? I have one server, that is not stressed or anything, that has an average around 2500 per second, with some occasional spikes to 10K or so.

    so what counts as a transaction? an explicit begin tran plus any implicit transactions for a select/insert/update/delete? i have maybe 150 or so connections, so i don't think each connection is doing 10 selects per second? that seems to low a number.

    it can't be a row of data, because the data being returned, i would think, would often peak in the 100K for large queries. pages returned? dividing rows by 8 to get a rough page size doesn't seem to be right, either.

    all my google fu points me to how to measure,and arguments whether the transactions per seconds are "too high".

    on a local instance that is just idling, other than my monitor, i see a regular 50 transactions per second, with a bump to 200 per second every minute or so.

    i cannot seem to find what, specifically, counts as a "transaction" in this measurement.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 31, 2015 at 5:49 am

    #1823334

    bumping this for the morning, hopefully someone may remember what counts as a transaction per second.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 31, 2015 at 6:40 am

    #1823343

    My understanding (frequently wrong) is that it's both explicit and implicit transactions whether called by the user or within the server itself. I generally don't use it since it's something of an ill-defined number. Instead I use batch requests/sec. That's a much more clearly defined number. It's only an indication of load, and there isn't a "too high" or "too low" there's just a "not as high as yesterday" for these types of counters.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 3 posts - 1 through 2 (of 2 total)

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