Results 1 to 13 of 13
  1. #1
    Join Date
    May 2009
    Location
    Belize
    Posts
    7

    Unanswered: checking Transactions per Minute

    Greeting;

    I am trying to find or calculate how much transactions per minute my Microsoft SQL 2005 DB is handling.

    Could anyone provide any tip as to how I can do this.

    Thanks
    Pablo

  2. #2
    Join Date
    Mar 2009
    Posts
    349
    Hola Pablo.

    What version of SQL Server?

    sys.dm_tran_database_transactions might suit your needs.

  3. #3
    Join Date
    May 2009
    Location
    Belize
    Posts
    7

    Smile Re:

    I am running SQL Server 2005.

    is this sys.... a stored proedure? where do I find this to run it??

    or do I just run it as a query under the Management Studio??? under which DB??


    Thanks

    Pablo


    ps. I found a link sys.dm_tran_database_transactions (Transact-SQL) with further info.. Will read mean while... Thanks again.

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    You could use a select from the aforementioned DMV (select * from sys.dm_tran_database_transactions) to get the current number of active transactions, and periodically poll that view to find out an aggregate over time.

    Another option is to use performance monitor and add the SQLServer:Transactions\Transactions counter, or the SQLServeratabases\Transactions/sec counter, or the SQLServerQL Statistics\Batch Requests/sec counter.

    The first will give you what the DMV gives you, i.e. currently open transaction count - and you can monitor the movement on that to get a moving average. However you cannot limit this to a single database, so if you are only concerned with one of numerous databases on the system this counter will be irrelevant.

    The second will give you a throughput figure of transactions per second, which you can limit to one or more databases of your choosing.

    The final option will tell you how many batches (statements) are being executed per second - of course if you execute 5 statements within a transaction the count doesn't give you transactional information, but will still give you an idea of the activity level on your database.

  5. #5
    Join Date
    May 2009
    Location
    Belize
    Posts
    7

    Lightbulb Re:

    Thanks for the info....

    Maybe you could help me with the approach I am taking or maybe you can tell meif I am barking up the wrong tree..

    I plan to select form the trans_ table send the results to a table (temp in this case) and re-run the query every second for 1 minute. I then count the distinct records in the temporary table which I believe would be all transaction which were processed by the DB. Does this make sense to you??

    --Select and assignment Query
    select t.transaction_id, t.database_id into #tmp_trans_id from sys.dm_tran_database_transactions AS t where t.transaction_id > '1000' order by t.transaction_id desc

    --count distinct records
    select distinct count (*) from #tmp_trans_id

    --drop table
    drop table #tmp_trans_id


    I am having an issue with this as after I run the initial insert into... it tells me that the table already exists so I cannot run the query more than once... Is there a way I can do an append??? I was thinking that as a work around.. I could use a while loop using present time and let the query rerun for 1 minute. Would this hold the table in "open" mode so i would stop getting the table already exists error??

    Thanks again..
    Pablo Cawich

  6. #6
    Join Date
    Mar 2009
    Posts
    349
    The short answer is that you SELECT INTO should be an INSERT INTO without knowing more about what you are after. You may want a permanent table too and you might want to truncate it every time or add a time dimension to track things over time.

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Are you specifically after transactions (i.e. updates, inserts, and deletes), or do you want to know how many queries per second/minute it is handling? There is a perfmon counter for Batch Requests/sec. There is probably one for transactions, as well, but that may not include implicit transactions. I would have to read up on the definitions of those counters again to be sure.

  8. #8
    Join Date
    May 2009
    Location
    Belize
    Posts
    7
    OK. I was asked to find the transactions per minute that our system handles.

    We have about 55 DB's which are inter-related. which is why I was trying to find a way how to find this out..

    I hope this answers your questions on what I am trying to get..

    I ran the perfmon with the trans/sec, batch req/sec and trans count.

    Please see the results below.


    avge. 1,000 transactions/sec - 60,000 trans/minute
    avge. 22 batch requests/sec - 1320 batch requests/sec
    stays at 20 transactions

    I am not sure which one I should use.. While I was doing the checks through the sys.dm_trans table, I estimated abut 75 trans/min.

    Am I going the right way??

    Thanks
    Pablo

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Measuring transactions depends on knowing what constitutes a transaction for you. Are you tring to get something like TCP transactions, a raw count of SQL statements, the number of round trips to your database server, or something I haven't listed. You need to answer that question before we can really start to help you get the appropriate tools in place to get the data that you need.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    May 2009
    Location
    Belize
    Posts
    7
    What I think is needed in this case is any Insert, Updates, Deletes and Queries to any Database of the 55 DB's. I do not care which DB is being hit...

    Just that a DB is performing one of the above.

    Does this answer what you are asking me??

    Thanks again.
    Pablo

  11. #11
    Join Date
    May 2009
    Location
    Belize
    Posts
    7
    Pat Phelan;

    Does my statement above on what I am trying to get answer the question you were asking me?

    Thanks
    Pablo

  12. #12
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    The batch requests/sec counter will give you information on how many statements are hitting the database each second.

    You could also use SQL server profiler and choose an event such as statement completed, monitor for a period of time (10 minutes for example) and add up how many statements hit the databases during that time and that could give you a rough figure for a per minute average.

  13. #13
    Join Date
    May 2009
    Location
    Belize
    Posts
    7
    Thanks very much for your assistance guys!!!!



    Thanks
    Pablo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •