Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Mircea Nita,
I have been faced many times with situations where I needed to quickly identify the processes that are the most I/O intensive on the server in real time, without setting up traces.
If you try to run sp_who2 and base your investigation on the DiskIO listed there, you will find that the processes showing the highest figures, might not be in fact those that are the most IO intensive in real time.
This is because sp_who2 displays the total DiskIO accumulated since the connection was established, therefore sp_who2 can show high DiskIO figures, which do not reflect the current activity on the server.
In order to get an indication on the real time DiskIO for the user processes, I have modified the sp_who2 stored procedure to include a new metric.
The result is called sp_whoio and in addition to the usual columns returned by sp_who2 it contains a new metric that I introduced, called DiskIO_Delta.
The DiskIO_Delta is calculated by sampling the DiskIO over a period of time, and returns accurate figures in milliseconds about the Disk I/O activity per process. I found that setting the sampling period at 3 seconds is a good choice for calculating the DiskIO_Delta.
Like in any sampling process, increasing the sampling period and the number of samples taken would increase the accuracy. However, three seconds is a period that I found suited for this, as it is also the time you have to wait before getting any output from sp_whoio.
The stored procedure sp_whoio orders the output by DiskIO_Delta in descending order, therefore making it easy to rapidly identify the most intensive DiskIO processes on the server.
The DiskIO_Total is the same figure as the one returned by sp_who2 which appears listed there as just DiskIO. The DiskIO_Delta figure is the I/O activity recorded within the 3 seconds sampling period.
From the figure above you can immediately see that the process having the most intensive I/O at the time of the investigation, is not the process that has the highest accumulated disk I/O (DiskIO_Total) . It would be therefore impossible to determine the highest momentary disk I/O only based on what the standard sp_who2 returns.
I hope that sp_whoio is going to prove as useful to you as it is for me in investigating performance issues.
High CPU/DiskIO for process running "SET TRANSACTION ISOLATION LEVEL READ COMMITTED"
CURSOR READ SP_WHO2
One of my process is running very slow. It was working very fine few hours before.
What is meaning of cputime and diskio in sp_who2?
Do they tell me processing time? DiskIO is number...