--create container database in default locations CREATE DATABASE [DiskStallCollector] GO ALTER DATABASE [DiskStallCollector] SET RECOVERY SIMPLE GO USE [CollectorTemp] GO CREATE TABLE [dbo].[DiskStall]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [ServerName] [nvarchar](128) NULL, [InstanceName] [nvarchar](128) NULL, [DatabaseID] [smallint] NOT NULL, [DatabaseName] [sysname] NOT NULL, [PhysicalFileName] [nvarchar](260) NOT NULL, [DatabaseFileID] [int] NOT NULL, [AvgReadStallms] [numeric](10, 1) NULL, [AvgWriteStallms] [numeric](10, 1) NULL, [IOStallReadms] [bigint] NULL, [NumOfReads] [bigint] NULL, [IOStallWritems] [bigint] NULL, [NumOfWrites] [bigint] NULL, [TotalNumOfBytesRead] [bigint] NULL, [TotalNumOfBytesWritten] [bigint] NULL, [CollectionDateTime] [datetime] NOT NULL, CONSTRAINT [PK_DiskStall] PRIMARY KEY CLUSTERED ( [ID] ASC ) ) ON [PRIMARY] GO --create stored procedure, to be executed as a job by the sql server agent --every 30 seconds. --please update the target database and table names as you modified above in this script CREATE PROCEDURE dbo.usp_DiskStallCollection AS SET NOCOUNT ON; BEGIN TRY -- Calculates average stalls per read, per write, and -- per total input/output for each database file. ;WITH PreviousSample_CTE( ServerName, InstanceName, DatabaseID, DatabaseFileID, IOStallReadms, NumOfReads, IOStallWritems, NumOfWrites) AS ( SELECT ServerName, InstanceName, DatabaseID, DatabaseFileID, IOStallReadms, NumOfReads, IOStallWritems, NumOfWrites FROM dbo.DiskStall WHERE CollectionDateTime = (SELECT MAX(CollectionDateTime) FROM dbo.DiskStall) ) INSERT INTO dbo.DiskStall ([ServerName] ,[InstanceName] ,[DatabaseID] ,[DatabaseName] ,[PhysicalFileName] ,[DatabaseFileID] ,[AvgReadStallms] ,[AvgWriteStallms] ,[IOStallReadms] ,[NumOfReads] ,[IOStallWritems] ,[NumOfWrites] ,[TotalNumOfBytesRead] ,[TotalNumOfBytesWritten] ,[CollectionDateTime]) SELECT @@SERVERNAME as ServerName, @@SERVICENAME as InstanceName, fs.database_id as DatabaseID, d.name as DatabaseName, mf.physical_name as PhysicalFileName, mf.file_id as DatabaseFileID, CAST((io_stall_read_ms - (isnull(cte.IOStallReadms,0.0)))/ (1.0 + (num_of_reads - isnull(cte.NumOfReads,0.0))) AS NUMERIC(10,1)) AS [AvgReadStallms], CAST((io_stall_write_ms - (isnull(cte.IOStallWritems,0.0)))/ (1.0 + (num_of_writes-isnull(cte.NumOfWrites,0.0))) AS NUMERIC(10,1)) AS [AvgWriteStallms], io_stall_read_ms as IOStallReadms, num_of_reads as NumOfReads, io_stall_write_ms as IOStallWritems, num_of_writes as NumOfWrites, num_of_bytes_read as TotalNumOfBytesRead, num_of_bytes_written as TotalNumOfBytesWritten, GETDATE() as CollectionDateTime FROM sys.dm_io_virtual_file_stats(null,null) AS fs INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id] inner join sys.databases as d on d.database_id = fs.database_id left join PreviousSample_CTE cte on cte.ServerName = ServerName and cte.InstanceName = InstanceName and cte.DatabaseID = fs.database_id and cte.DatabaseFileID = mf.file_id ORDER BY fs.database_id asc OPTION (RECOMPILE); END TRY BEGIN CATCH THROW END CATCH; GO --create scheduled job. executes every 15 seconds and calculates our I/O differential USE [msdb] GO DECLARE @jobId BINARY(16) EXEC msdb.dbo.sp_add_job @job_name=N'Disk Stall Collection', @enabled=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @category_name=N'Data Collector', @owner_login_name=N'sa', @job_id = @jobId OUTPUT select @jobId GO EXEC msdb.dbo.sp_add_jobserver @job_name=N'Disk Stall Collection', @server_name = N'HFXDKLEE' --Change this! GO USE [msdb] GO EXEC msdb.dbo.sp_add_jobstep @job_name=N'Disk Stall Collection', @step_name=N'Collect Data', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_fail_action=2, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC dbo.usp_DiskStallCollection', @database_name=N'DiskStallCollector', @flags=0 GO USE [msdb] GO EXEC msdb.dbo.sp_update_job @job_name=N'Disk Stall Collection', @enabled=1, @start_step_id=1, @notify_level_eventlog=0, @notify_level_email=2, @notify_level_netsend=2, @notify_level_page=2, @delete_level=0, @description=N'', @category_name=N'Data Collector', @owner_login_name=N'sa', @notify_email_operator_name=N'', @notify_netsend_operator_name=N'', @notify_page_operator_name=N'' GO USE [msdb] GO DECLARE @schedule_id int EXEC msdb.dbo.sp_add_jobschedule @job_name=N'Disk Stall Collection', @name=N'15 sec', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=15, @freq_relative_interval=0, @freq_recurrence_factor=1, @active_start_date=20150402, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_id = @schedule_id OUTPUT select @schedule_id GO