Solved

sys.dm_io_virtual_file_stats on SQL Server

Posted on 2014-01-17
2
660 Views
Last Modified: 2014-01-17
Base on the attach result for IO query, how can it tell whether my server has I/O issue or not ? Tks

SELECT
cast(DB_Name(a.database_id) as varchar) as Database_name,
b.physical_name, *
FROM  
sys.dm_io_virtual_file_stats(null, null) a
INNER JOIN sys.master_files b ON a.database_id = b.database_id and a.file_id = b.file_id
ORDER BY Database_Name
SQL-IO-View.png
0
Comment
Question by:AXISHK
2 Comments
 
LVL 10

Accepted Solution

by:
PadawanDBA earned 500 total points
ID: 39789055
You're going to need to create a baseline and monitor the growth of the different pieces (the stall times as you have noted).  These are cumulative since the last server restart, so if you had a huge IO issue that caused all of those waits, you're still going to see them and they're going to skew your opinion.  Monitoring the growth of these stats on a periodic basis is going to give you a much better of: if you have an issue and when you have an issue.  With the knowledge of when you are having an issue (if you are), you are going to have a much better idea of what is going on from a sql process/storage processes perspective.  Another thing to keep in mind, is those stall stats are per spid, so if you have a process locking the file and there are 10 or so SPIDs that are waiting, you're going to have a 10x multiplier on the stall stats.

Edit:

Take a look at this article: http://blogs.msdn.com/b/kronos/archive/2010/04/13/introducing-sys-dm-io-virtual-file-stats.aspx?Redirected=true
0
 

Author Closing Comment

by:AXISHK
ID: 39790224
Tks
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 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