Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-09-07
7
Medium Priority
?
1,348 Views
Last Modified: 2012-09-16
Hi,

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.

Thanks.
0
Comment
Question by:ezkhan
  • 2
  • 2
5 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 2000 total points
ID: 38380279
Hi,

Take a look at this:

SELECT
    mf.name
    , 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.

HTH
  David
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 38380444
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.
0
 

Author Comment

by:ezkhan
ID: 38380477
Thanks David. The code you sent does it return read and write BYTES / millisecon.?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38381221
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.

HTH
  David
0
 

Author Closing Comment

by:ezkhan
ID: 38403702
Thanks alot. This solution worked great.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

578 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