Measuring SQL Server Cumulative Disk IO

At some point you will have to troubleshoot a SQL Server issue that is caused by excessive disk input/output (IO). SQL Server's sensitivity to disk bottlenecks makes it crucial that one know how to identify which specific SQL process (SPID) is consuming IO resources.

To show instantaneous but cumulative measurement by active SPID:


--SQL Script begin
if exists (select * from tempdb.dbo.sysobjects where name = '##who2')
drop table tempdb.dbo.##who2
GO
create table tempdb.dbo.##who2
(
[Spid] int not null,
[Status] nvarchar(130) not null,
[Login] nvarchar(130) not null,
[Host Name] nvarchar(130) null,
[BlkBy] nvarchar(130) null,
[DB Name] nvarchar(130) null,
[Command] nvarchar(130) null,
[CPU] int not null,
[IO] int not null,
[Last Batch] nvarchar(130) null,
[Program Name] nvarchar(130) null,
[Spid2] int not null
)
insert into tempdb.dbo.##who2 exec('sp_who2')
select * from tempdb.dbo.##who2
order by [IO] desc
--SQL Script end


or


--SQL Script begin
if exists (select * from tempdb.dbo.sysobjects where name = '##threads')
drop table tempdb.dbo.##threads
create table tempdb.dbo.##threads
(
[Spid] smallint not null,
[Thread ID] int null,
[Status] nvarchar(10) not null,
[Login Name] nvarchar(10) null,
[IO] bigint not null,
[CPU] int not null,
[MemUsage] int not null
)
insert into tempdb.dbo.##threads exec('dbcc sqlperf(threads) with tableresults, no_infomsgs')
select * from tempdb.dbo.##threads order by [IO] desc
--SQL Script end


The first script shows application name and other information generated by SP_WHO2. The second script is generally quicker and provides more concise IO information. Both will provide you with results that include the amount of IO accumulated by a process. Also, both scripts return results sorted by amount of IO in descending order.

Once you have used the above scripts to identify SPIDs for processes that have used large amounts of IO you can use the SPID in SP_WHO2 or SP_LOCK to gather more information about the process:

Example:

sp_who2 432
sp_lock 432



Script file for queries used in this article is here:
http://blogfiles.karamazovgroup.com/Home/sql-server-scripts/MeasuringcumulativeIO.sql?attredirects=0

All SQL script files are here:
http://blogfiles.karamazovgroup.com/Home/sql-server-scripts