Is it possible to get the same value as in the activity monitor (SSMS) for the "Database IO" as a result of a SQL request?
Get IO Stats Request SQL Like Activity Monitor – SQL Server
sql serverssmsstatistics
Related Question
- SQL Server – Activity Monitor % Processor Time Grayed Out
- SQL Server – Discrepancy in Batch Requests/sec Between DMV and Activity Monitor
- SQL Server Activity Monitor Timeout Error – How to Resolve
- SQL Server – How to Monitor Activity in SSMS Using Only T-SQL
- Sql-server – Sizing the SQL Agent Job Activity Monitor screen
- Sql-server – SQL Server 2014 View any Live execution plan in Activity Monitor
- Sql-server – Activity Monitor Blocked by Firewall. Set Static Port
- SQL Server – Troubleshooting Paused Activity Monitor
Best Answer
You can use a query like this to calculate read/write rates and latency (though due to my laziness these figures are not in units that match Activity Monitor).
See these resources for more info:
If you want to simulate the graph and capture spikes in real time, well, you'll need to build scaffolding to collect snapshots of this (or some other) query, and compare the deltas to generate metrics. This is not something that is trivial to write for you on a Q & A site - people pay big money for high-quality monitoring tools that make this kind of thing easy. :-)
If you want to get an idea of how Activity Monitor does this kind of thing, you can get at the definition for the procedure
#am_generate_waitstats
with a little effort. I did the heavy lifting for you and here it is in all its unformatted glory - this is how it calculates various wait statistics:For file I/O specifically, it is not so cut and tried. With a trace you can uncover code snippets like these, but I have yet to find what ties them together, or even where the #temp tables are defined - I suspect some of the math and other work is done in the application and/or in other queries I'm not capturing in my trace. Needless to say, this is not just a simple SQL query.
Batch #1:
Batch #2:
So, if you'd like to work from there, that's probably a good starting point. Personally, since I know there are already plenty of tools that do what I'm trying to do, my time is better spent elsewhere, and I prefer to not re-invent the wheel. YMMV.