cancel
Showing results for 
Search instead for 
Did you mean: 

Best way to get number of transactions

Former Member
0 Kudos

We have a customer running SA 12.0.1 asking for the number of database transactions that are occurring.

What would be the best way to get transaction statistics?

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

former_member188493
Contributor
0 Kudos

The phrase "number of database transactions that are occurring" is open to interpretation.

A simplistic interpretation might be "the number of database transactions that are in progress at one instant in time". By definition this number must be less than or equal to the number of connections that exist, since a single connection can only have one transaction in progress at any one time. The CONNECTION_PROPERTY ( 'TransactionStartTime' ) string will contain a timestamp value for each connection that has a transaction running. The sa_conn_properties() function can be use to return a count as follows:

SELECT COUNT(*) AS transactions_in_progress

  FROM sa_conn_properties()

WHERE PropName = 'TransactionStartTime'

   AND ISDATE ( Value ) = 1;

transactions_in_progress

------------------------

113                 

However, it's hard to imagine how such a number would be useful. For example, the number 113 was returned for a server with 200 busy connections, and those connections were executing over 6,000 transactions per second... the number "113" gives no clue about the workload since it could just as easily mean there are 113 users who have updated a row and then gone to lunch without executing a commit. In one case the CPU and disks are being pounded to death, in the other case the server is idle.

A more useful interpretation might be "the rate at which transactions are occurring". SQL Anywhere doesn't count transactions, but it does count COMMIT operations with CONNECTION_PROPERTY ( 'Commit' ).

Here's a query you can run repeatedly to return values you can use to calculate the rate at which commits are being executed:

SELECT CURRENT_TIMESTAMP AS timestamp,

       SUM ( Value ) AS total_commits

  FROM sa_conn_properties()

WHERE PropName = 'Commit'

   AND ISNUMERIC ( Value ) = 1;

timestamp               total_commits               

----------------------- --------------------------------

2014-08-04 17:01:26.528 8883221.000000

timestamp               total_commits               

----------------------- --------------------------------

2014-08-04 17:01:41.787 8958829.000000          

The Foxhound Monitor for SQL Anywhere displays the commit rate as one of the measurements of throughput, as shown in this screen shot of a busy 12.0.1 database:

Former Member
0 Kudos

Breck, you are correct and I should have been clearer in my question.  They are looking for a metric along the line of transactions per some time period whether that is second, minute or hour.

Thank you for the suggestions.

Answers (0)