Post reply

I need number of transaction / read / write handled by prod SQL server per second.

  • August 10, 2016 at 5:12 am

    #310384

    I need number of transaction / read / write handled by prod SQL server per second.Is there any scripts which gives me both read/s and write/s ?

    Thanks in advance .

  • August 10, 2016 at 7:49 am

    #1894776

    I don't use SQL Server code (T-SQL) for such a thing. Instead, I use Performance Monitor, which can both save the data in a nice, importable CSV and can display it in a real time chart outside of SQL Server.

    If you don't want to go that route, then you can use the OS counters within one of the system views with the understanding of the following not from Books Online... which certainly makes it a bit more complicated.

    [font="Arial Black"]For per-second counters[/font], this value is cumulative. The rate value must be calculated by sampling the value at discrete time intervals. The difference between any two successive sample values is equal to the rate for the time interval used.

    Here's the over-simplified code for the counters that you say you want. Again, these give you cumulative values and you'll need to write a wad of extra code to not only save the counters on a regular basis, but then compare to your last value and subtract that last value from the returned valued to get the periodic usage, whatever you want to use for the periodic duration. Once per second would actually be pretty annoying and, if your code is suspended while something else is running, can be longer than the period you desire. That's why I use the PerMon stuff, instead.

    SELECT *

    FROM sys.dm_os_performance_counters

    WHERE counter_name in ('Page reads/sec','Page writes/sec')

    OR (counter_name = 'Transactions/sec' AND instance_name = '_Total')

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • August 10, 2016 at 7:55 am

    #1894781

    p.s. All of the character based columns in the return from this system view are NCHAR(128). Most of the values returned are quite a bit less. If you make a table to store this stuff in, be advised that the extra and unwanted spaces in the names may be saved in the table even if you use NVARCHAR(128) as the datatype if you use the straight data. That's a whole lot of extra space and such a table can become quite large very quickly unless you do an RTRIM of the data before dropping it into your table.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply