Continuing my efforts on penning down some notes on SQL Server IO, here is another weapon in your arsenal – identifying workloads that are causing high IO (reads & writes). The DMV to be used here is dm_exec_query_stats. This DMV is overwhelming with just so many attributes to compute. Amongst many metrics, you have a bunch of around logical reads and writes & physical reads and writes & a few variations of them. A straightforward retrieval of these metrics may not be very helpful so as usual, we need to do a lot of mathematics and make the metrics meaningful. For example, if you see the attached image, we are computing the total physical reads caused by multiple executions of queries. Note the descending order to get the highest values first. In other words, who is causing the most physical IO. Hey, not logical IO, but the focus here is physical IO – reading from the disk. Likewise, you can compute physical writes, logical reads, logical writes, and a lot more. Watch the video below.
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.
Happy Learning.
Where are all those scripts for these videos ??????