SQL Server - Database Read Rate MB/sec and Database Wite Rate MB/sec

Posted on 2012-09-07
Last Modified: 2012-09-16

I have a specific requirement where we are upgrading our hardware storage and vendor of new hardware storage has requested few performance counters specifically and these are;

Database read rate MB/sec
Database write rate MB/sec

Vendor is specifically interested in database read and write only other than whole disk read and write operations.

Initially i thoughts counters given below can be used but not sure wether they cover what we need;

PhysicalDisk->Disk Read Rate/Bytes
PhysicalDisk->Disk Write Rate/Bytes

I am recording these counters on SQL Server boxes over the period of 24 hours and then gtting the maximum(and converting from Bytes to Kilo Bytes to MB) value out of it to establish database read and write rate in MB/sec.

Please advise if this is not correct and there are some other couners i need to look at and how to get there answer in MB/sec.

Your help and suggestios are appreciated.

Question by:ezkhan
    LVL 35

    Accepted Solution


    Take a look at this:

        , mf.physical_name
        , io_stall_write_ms / ( 1.0 + num_of_writes ) as average_time_write_ms
        , io_stall_read_ms / ( 1.0 + num_of_reads ) as average_time_reads_ms
    FROM    sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
    INNER JOIN sys.master_files AS mf 
        ON fs.database_id = mf.database_id
        AND fs.[file_id] = mf.[file_id]

    Open in new window

    I think it will return the numbers you want for your sql data files, which will ignore other disk access.

    LVL 38

    Expert Comment

    by:Jim P.
    Disk output is always a bottleneck.  Now the way to look at disk from SQL is whether you can control what is written to what disks.

    If the vendor is saying the disk is an "aggregate" of "JBOD" (Just a Bunch Of Disks) it doesn't matter if you split the temp from the mdf from the ldf. You have no control on how the data is written and what goes where on the disk channels.

    Now if the solution has control on the channels to disk and can assign disks to the certain drives, then that is the better (usually more expensive) solution.

    The in-depth is complicated.

    But in general you are on the right path.

    Author Comment

    Thanks David. The code you sent does it return read and write BYTES / millisecon.?
    LVL 35

    Expert Comment

    by:David Todd
    Hi ezkhan,

    The figure is in milliseconds / (read or write)

    There might be other columns where you can find the size of the read/write.


    Author Closing Comment

    Thanks alot. This solution worked great.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    This article is an update and follow-up of my previous article:   Storage 101: common concepts in the IT enterprise storage This time, I expand on more frequently used storage concepts.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now