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

21 comments:

  1. Replies
    1. This comment has been removed by a blog administrator.

      Delete
  2. 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
  3. Thank you both for reading. I'm glad to have helped!

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

    ReplyDelete
  5. Cool stuff you have and you keep overhaul every one of us

    data science course

    ReplyDelete
  6. I have to search sites with relevant information on given topic and provide them to teacher our opinion and the article.

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  7. very nice blogs!!! i have to learning for lot of information for this sites...Sharing for wonderful information.Thanks for sharing this valuable information to our vision. You have posted a trust worthy blog keep sharing.

    Web Designing Course Training in Chennai | Web Designing Course Training in annanagar | Web Designing Course Training in omr | Web Designing Course Training in porur | Web Designing Course Training in tambaram | Web Designing Course Training in velachery


    ReplyDelete
  8. Awesome blog. I enjoyed reading your articles. This is truly a great read for me. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work!

    data science interview questions

    ReplyDelete
  9. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  10. I am looking for and I love to post a comment that "The content of your post is awesome" Great work!

    Simple Linear Regression

    Correlation vs Covariance

    ReplyDelete
  11. very well explained .I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  12. very well explained .I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Simple Linear Regression
    Correlation vs covariance
    data science interview questions
    KNN Algorithm
    Logistic Regression explained

    ReplyDelete
  13. I have read your article, it is very informative and helpful for me.I admire the valuable information you offer in your articles. Thanks for posting it.. שרת וירטואלי

    ReplyDelete
  14. This comment has been removed by a blog administrator.

    ReplyDelete
  15. very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! mélybölcsős szállítás Europa-Road Kft

    ReplyDelete