SQL SERVER – Find Total Number of Transaction on Interval

In one of my recent Performance Tuning assignment I was asked how do someone know how many transactions are happening on server during certain interval. I had handy script for the same. Following script displays transactions happened on server at the interval of one minute. You can change the WAITFOR DELAY to any other interval and it should work.

-- First PASS
DECLARE @First INT
DECLARE
@Second INT
SELECT
@First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$SQLENT1:Databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
-- Following is the delay
WAITFOR DELAY '00:01:00'
-- Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME = 'MSSQL$SQLENT1:Databases' -- Change name of your server
AND counter_name = 'Transactions/sec'
AND instance_name = '_Total';
SELECT (@Second - @First) 'TotalTransactions'
GO

If you are using any other method to detect transactions per interval, I request you to post it over here.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

16 thoughts on “SQL SERVER – Find Total Number of Transaction on Interval

  1. Thanks ,
    Nice Script,

    I had changed it as follows so it would work on my Sql sever instance.

    — First PASS
    DECLARE @First INT
    DECLARE @Second INT
    SELECT @First = cntr_value
    FROM sys.dm_os_performance_counters
    WHERE OBJECT_NAME LIKE ‘%Databases%’ — Change name of your server
    AND counter_name like ‘%Transactions/sec%’
    AND instance_name like ‘%_Total%’;

    SELECT @First
    — Following is the delay
    WAITFOR DELAY ’00:00:30′
    — Second PASS
    SELECT @Second = cntr_value
    FROM sys.dm_os_performance_counters
    WHERE OBJECT_NAME LIKE ‘%Databases%’ — Change name of your server
    AND counter_name like ‘%Transactions/sec%’
    AND instance_name like ‘%_Total%’;
    SELECT @Second

    SELECT (@Second – @First) ‘TotalTransactions’
    GO

    Like

    • I too modified this slightly, to make it so that I can capture results form multiple instances on multiple servers at the same time.
      I modified @Veepools script to add a breakdown per second.
      I am sure someone else has worked out how to paramaterise the delay so that this is the only field to change but I am not that good yet.

      —-
      — First PASS
      DECLARE @First INT
      DECLARE @Second INT
      SELECT @First = cntr_value
      FROM sys.dm_os_performance_counters
      WHERE OBJECT_NAME LIKE ‘%Databases%’ — Change name of your server
      AND counter_name like ‘%Transactions/sec%’
      AND instance_name like ‘%_Total%’;
      — Following is the delay
      WAITFOR DELAY ’00:01:00′
      –Second PASS
      SELECT @Second = cntr_value
      FROM sys.dm_os_performance_counters
      WHERE OBJECT_NAME LIKE ‘%Databases%’ –Change name of your server
      AND counter_name like ‘%Transactions/sec%’
      AND instance_name like ‘%_Total%’;
      SELECT (@Second – @First) ‘Total Transactions’, ((@Second – @First)/60) ‘Avg Transactions P/S’ –Change the division to the number of seconds in the time frame you chose.
      GO

      Like

      • Oops forgot to add, to execute against many servers in the same window create a Central Management Server Group and run against a group of registered servers.
        User account needs permissions on all servers.

        Like

  2. You can enable Change Tracking, to track the changes to the database-table.
    Benefits here would be:
    1) You can track changes which you are trying to troubleshoot.
    2) You can get the more info on whether you have more INSERTS/DELETES/UPDATES.

    I guess the query you have suggested wont help me much in pinning down the suffering table

    Like

  3. I know that this is very old article… but in order to make query dynamic irrespective of the instance name or default instance, below is the code:

    — First PASS
    DECLARE @First INT
    DECLARE @Second INT
    SELECT @First = cntr_value
    FROM sys.dm_os_performance_counters
    WHERE OBJECT_NAME = CASE
    WHEN SERVERPROPERTY(‘InstanceName’) IS NULL
    THEN ‘SQLServer’
    ELSE ‘MSSQL$’ + CAST(SERVERPROPERTY(‘InstanceName’) AS VARCHAR)
    END + ‘:Databases’ — Change name of your server
    AND counter_name = ‘Transactions/sec’
    AND instance_name = ‘_Total’;
    — Following is the delay
    WAITFOR DELAY ’00:01:00′
    — Second PASS
    SELECT @Second = cntr_value
    FROM sys.dm_os_performance_counters
    WHERE OBJECT_NAME = CASE
    WHEN SERVERPROPERTY(‘InstanceName’) IS NULL
    THEN ‘SQLServer’
    ELSE ‘MSSQL$’ + CAST(SERVERPROPERTY(‘InstanceName’) AS VARCHAR)
    END + ‘:Databases’ — Change name of your server
    AND counter_name = ‘Transactions/sec’
    AND instance_name = ‘_Total’;
    SELECT (@Second – @First) ‘TotalTransactions’
    GO

    Like

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #049 | Journey to SQL Authority with Pinal Dave

  5. Hello Pinal Sir,
    I have an application which needs to perform 700 Database transactions per minute.
    The Transactions are extremely light in nature. (Updating a column value in a table, copying the newly inserted row using the after insert trigger etc)

    300 transactions are on a Database named TWPQueues & the remaining 400 are on another database.

    My problem is that I have to rent a server to host the application but I am unable to decide what capacity of server I shall buy which can perform 700 database transactions in a minute.

    I asked a few experts & they said that the no of database transactions that a server can perform depends on the size of your RAM and the complexity of your database transactions.

    As I am not an expert in this, please help me.
    Thanks in advance.

    Regards,
    Swapnil Khandode

    Like

  6. Hi Guys,

    Can anyone help how to find out the toltal number of transaction in one day (Business hours).
    I want to know how much data is writing on database in single day so that i can manage the Hardware accordingly and w.r.t to performance.

    Like

  7. Naveed, (and anyone else finding this later)

    Functionally if you look at the example, what is going on is a COUNTER value is being recorded, and then we wait 1 minute and record it again. We take each sample (recorded value) and subtract one from the other.

    This works because this particular counter is a cumulative counter since the last time you started the SQL server instance.

    To get what you desire, step up your game and create your own small reporting / logging database. then you can sample the counter when ever you want. Perhaps you create a table with only two columns: DateRecorded, TransactionsCount

    Then insert a single sample when ever you want. Now you can take any two sample and measure the difference between them. If you sample at 1 minute intervals, you could get measurements of transactions in a minute, 5 minutes, 15 minute, an hour, 6 hours, etc..

    Take it up a notch and add another column: DatabaseName. The Instance_Name is the database name. Sample each one separately, (or step it up again and realize you can capture them all at once). Now you have even more granular data.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s