![code on time ms sql server code on time ms sql server](https://i.ytimg.com/vi/FxKrfMsb_JA/maxresdefault.jpg)
I haven't used it, but I'll refer you to an excellent Microsoft blog post that provides a lot of details on usage and analysis: Analyzing Storage Performance using the Windows Performance Analysis ToolKit (WPT) Maybe It's Not the Disk I've heard that xperf is also a good tool for investigating this. Disk sec/Write, which are both part of the Physical disk object. Windows Performance Monitor (PerfMon) is a good way to see some of these stats in "real time." You can see them aggregated across the whole machine, or by disk.
![code on time ms sql server code on time ms sql server](https://i.pinimg.com/originals/3a/fd/f8/3afdf8dfece72ac8cac7a46b12ba8f4a.jpg)
To get truly useful information about about disk I/O, you'll really want to go outside of SQL Server.
![code on time ms sql server code on time ms sql server](https://i3.filecombo.com/uploads/2020/8/11-23/5c06a73a-8eab-487d-a211-2884119954d4.png)
Make sure to treat the patient, not the monitor. What matters is that performance is good enough, not whether a number has a certain value. Take those values with a grain of salt though. If you do a Bing search, you will find articles from Microsoft making the recommendations similar to: If you ask SQLskills, we will tell you something along the lines of: You'll see varying rules of thumb online, here's a common one ( from ): To the direct point of your question, SQL Server is not a great source of information about disk-level I/O issues. If you're looking for a pattern of slow response times, or a way to get latency, at the disk / LUN level, you'll have to be careful to consider which disk each of these files is on. The sys.dm_io_virtual_file_stats dynamic management function returns these stalls (AKA latency or disk response time) on a per-file basis. If your goal is to see the same numbers from Activity Monitor via T-SQL, I would go with one of these options. sp_BlitzFirst (which conveniently has parameters that will log the results to a table for your automatically): Logging sp_BlitzFirst to Tables.Paul Randal's script: Capturing IO latencies for a period of time.It can be useful to view these diffs, or log them to a table on a schedule, in order to help you know what's "normal" and what's "slow" for your server. The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started.Īctivity Monitor is just taking a snapshot of the results, waiting 10 seconds, taking another snapshot, and then showing you the difference. You're right, those values from sys.dm_io_virtual_file_stats accumulate the entire time the SQL Server service is running: