Search code examples
sql-servertransactionsload

how to calculate database transaction count per second and database growth

I need to learn about calculating database load of a project . Lets assume that the events below are firing when an insert is completed.

  1. Insert a new record to table1
  2. Get the uniqueid from inserted and insert the uniqueid to another table , with some other parameters
  3. table1 is a cumulative dataware , so select count(1) from table1 where anotherColumn = xxyyx command is fired
  4. After count result received , update another table with this count

I need to calculate how many transactions are opened and what is the cpu load of database when 100 or 1000 times this scenario is fired at the same time.

Thanks...

Solution

  • By your question I understand that you wish to obtain a statistics in regards of the number of transactions per seconds (TPS). This is a standard statistic data about the capacity of your computer infrastructure and your database servers. If I understand correctly, this mathematic formula could perhaps help.

    TPS = Number of transactions / Sum(Each transaction time in seconds)

    This will allow you to know how much transactions per seconds peeked your server in the time elapsed (DateTime interval period)

    On the other hand, if you wish to calculte the very server load considering the number of users and then simulate it, you might want to look for specialized loadTest tools.

    Let me know if I answer well your question, or let me know what I miss within your question.

    EDIT 1 This link might help: Open source performance testing tools

    EDIT 2 Another well known tool: QaLoad de Microfocus
    And for JAVA Applets:Apache JMeter
    But you have to make sure that these tools supports all of the features such Web Services, SSL, Kerberos, SOAP, XML, etc. if you require it.