Wednesday, January 2, 2019

Query SQL Server transactions - per day, hour and minute

This post will help you query your SQL Server for transaction count at both the server level and for each of the databases.  The query returns transaction statistics per day, hour and minute since the time of the last service restart.


/* SQL Server transactions per day/hour/minute using sys.dm_os_performance_counters */

       -- declarations
       DECLARE      
              @Days SMALLINT,
              @Hours INT,
              @Minutes BIGINT,
              @LastRestart DATETIME;

       -- get last restart date
       SELECT 
              @Days = DATEDIFF(D, create_date, GETDATE()),@LastRestart = create_date
       FROM   
              sys.databases
       WHERE  
              database_id = 2;

       -- collect days/hours since last restart
       SELECT @Days = CASE WHEN @Days = 0 THEN 1 ELSE @Days END;
       SELECT @Hours = @Days * 24;
       SELECT @Minutes = @Hours * 60;


       -- trans since last restart
       SELECT 
              @LastRestart [LastRestart],
              @@servername [Instance],
              cntr_value [TotalTransSinceLastRestart],
              cntr_value / @Days   [AvgTransPerDay],
              cntr_value / @Hours  [AvgTransPerHour],
              cntr_value / @Minutes [AvgTransPerMinute]
       FROM
              sys.dm_os_performance_counters
       WHERE  
              counter_name = 'Transactions/sec'
              AND instance_name = '_Total';


       -- trans since last restart per database
       SELECT 
              @LastRestart [LastRestart],
              @@servername [Instance],
              instance_name [Database_Name],
              cntr_value [TotalTransSinceLastRestart],
              cntr_value / @Days [AvgTransPerDay],
              cntr_value / @Hours  [AvgTransPerHour],
              cntr_value / @Minutes [AvgTransPerMinute]
       FROM  
              sys.dm_os_performance_counters
       WHERE  
              counter_name = 'Transactions/sec'
              AND instance_name <> '_Total'
       ORDER BY
              cntr_value DESC;


Your output will be similar to this:

















Take a look at this for more details on the dm_os_performance_counters DMV:     sys.dm_os_performance_counters

7 comments:

  1. You have provided a nice article, Thank you very much for this one. And I hope this will be useful for many people. And I am waiting for your next post keep on updating these kinds of knowledgeable things
    SEO Training in Chennai
    SEO Course in Chennai
    Cloud Computing Training in Chennai
    Data Science Course in Chennai
    Devops Training in Chennai
    Digital Marketing Course in Chennai
    Selenium Training in Chennai
    SEO Training in Tnagar

    ReplyDelete
  2. Thank you both for reading. I'm glad to have helped!

    ReplyDelete
  3. Prescient examination is changing every industry and offering bits of information that range from aggressive examination to customers' need investigation. Data Analytics Courses

    ReplyDelete