Storage Slow -Performance issue

 To isolate the disk Latency on FIle level .Use the below method. 

Create Procedure IOPending_Disk

(

@loopexecution int

)

AS begin

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IOpending_Disktable]') AND type in (N'U'))

BEGIN

create table IOpending_Disktable

(

[Database]      nvarchar (256) ,

physical_name  nvarchar (520),

io_pending  int,

io_pending_ms_ticks bigint,

io_type nvarchar(120),

num_of_reads bigint,

num_of_writes bigint,

[date] datetime

)

END

DECLARE @MyDateTime DATETIME

SET @MyDateTime = DATEADD(s,1,GETDATE())

DECLARE @i INT = 1;

--Declare @loopexecution int

--set @loopexecution=2

WHILE (@i <= @loopexecution)

 BEGIN

  WAITFOR delay '00:00:05'

insert IOpending_Disktable ([Database],physical_name,io_pending,io_pending_ms_ticks,io_type,num_of_reads,num_of_writes,[date])

SELECT DB_NAME(mf.database_id) AS [Database], mf.physical_name,

r.io_pending, r.io_pending_ms_ticks, r.io_type, fs.num_of_reads, fs.num_of_writes,GETDATE() as [Date]

FROM sys.dm_io_pending_io_requests AS r

INNER JOIN sys.dm_io_virtual_file_stats(null,null) AS fs

ON r.io_handle = fs.file_handle

INNER JOIN sys.master_files AS mf

ON fs.database_id = mf.database_id

AND fs.file_id = mf.file_id

where r.io_pending_ms_ticks>50

ORDER BY r.io_pending, r.io_pending_ms_ticks DESC;

 SET  @i = @i + 1;

END

End

--To detect the wait type ,SQL Query and averageIOstalls over Disk Latency . Please use the below method.

USE [master]

GO

/****** Object:  StoredProcedure [dbo].[Faizal_Monitoring]    Script Date: 05/03/2015 18:58:43 ******/

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Faizal_Monitoring]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[Faizal_Monitoring]

GO

/****** Object:  StoredProcedure [dbo].[Faizal_Monitoring]    Script Date: 05/03/2015 18:58:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Faizal_Monitoring]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'CREATE Procedure [dbo].[Faizal_Monitoring]

As begin

/****** Object:  Table [dbo].[Wait_SnapshotStats]    Script Date: 05/03/2015 14:28:23 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Wait_SnapshotStats]'') AND type in (N''U''))

BEGIN

CREATE TABLE [dbo].[Wait_SnapshotStats](

      [wait_time_ms] [bigint] NULL,

      [signal_wait_time_ms] [bigint] NULL,

      [RealWait] [bigint] NULL,

      [wait_type] [nvarchar](60) NOT NULL,

      [updatedDate] datetime

) ON [PRIMARY]

END

/****** Object:  Table [dbo].[Query_SnapshotStats]    Script Date: 05/03/2015 14:28:23 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[Query_SnapshotStats]'') AND type in (N''U''))

BEGIN

CREATE TABLE [dbo].[Query_SnapshotStats](

      [Duration] [bigint] NULL,

      [Time on CPU] [bigint] NULL,

      [Time blocked] [bigint] NULL,

      [Reads] [bigint] NULL,

      [Writes] [bigint] NULL,

      [CLR time] [bigint] NULL,

      [Executions] [bigint] NULL,

      [Individual Query] [nvarchar](max) NULL,

      [Parent Query] [nvarchar](max) NULL,

      [DatabaseName] [nvarchar](128) NULL,

      [updatedDate] datetime

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

END

/****** Object:  Table [dbo].[PerfmonCounters_SnapshotStats]    Script Date: 05/03/2015 14:28:23 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[PerfmonCounters_SnapshotStats]'') AND type in (N''U''))

BEGIN

CREATE TABLE [dbo].[PerfmonCounters_SnapshotStats](

      [object_name] [nchar](128) NOT NULL,

      [counter_name] [nchar](128) NOT NULL,

      [instance_name] [nchar](128) NULL,

      [InitialValue] [bigint] NOT NULL,

      [FinalValue] [bigint] NOT NULL,

      [Change] [bigint] NULL,

      [% Change] [bigint] NULL,

      [updatedDate] datetime

) ON [PRIMARY]

END

/****** Object:  Table [dbo].[DiskIO_SnapshotStats]    Script Date: 05/03/2015 14:28:23 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[DiskIO_SnapshotStats]'') AND type in (N''U''))

BEGIN

CREATE TABLE [dbo].[DiskIO_SnapshotStats](

      [DBName] [nvarchar](128) NULL,

      [LogicalFileName] [sysname] NOT NULL,

      [physical_name] [nvarchar](260) NOT NULL,

      [Bytesread] [bigint] NULL,

      [BytesWritten] [bigint] NULL,

      [IOStallReadms] [bigint] NULL,

      [IOStallWriteMS] [bigint] NULL,

      [Numberofreads] [bigint] NULL,

      [Numberofwrites] [bigint] NULL,

      [AvgReadTransfersMS] [bigint] NULL,

      [AvgWriteTransfersMS] [bigint] NULL,

      [updatedDate] datetime

) ON [PRIMARY]

END

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT

sql_handle, plan_handle, total_elapsed_time, total_worker_time

, total_logical_reads, total_logical_writes, total_clr_time

, execution_count, statement_start_offset, statement_end_offset

INTO #PreWorkQuerySnapShot

FROM sys.dm_exec_query_stats

SELECT

[object_name], [counter_name], [instance_name]

, [cntr_value], [cntr_type]

INTO #PreWorkOSSnapShot

FROM sys.dm_os_performance_counters

SELECT

wait_type, waiting_tasks_count

, wait_time_ms, max_wait_time_ms, signal_wait_time_ms

INTO #PreWorkWaitStats

FROM sys.dm_os_wait_stats

SELECT

 DB_NAME(filestats.database_id) AS DBName

 ,files.name AS LogicalFileName

 ,files.physical_name

 ,num_of_reads AS NumberReads

 ,num_of_writes AS NumberWrites

 ,num_of_bytes_read AS BytesRead

 ,num_of_bytes_written AS BytesWritten

 ,io_stall_read_ms AS IOStallReadMS

 ,io_stall_write_ms AS IOStallWriteMS

 into #prediskIOstats

FROM sys.dm_io_virtual_file_stats(-1,-1) filestats

INNER JOIN sys.master_files files

  ON filestats.file_id = files.file_id

  AND filestats.database_id = files.database_id

ORDER BY num_of_writes DESC

  WAITFOR delay ''00:01:30''

SELECT

wait_type, waiting_tasks_count, wait_time_ms

, max_wait_time_ms, signal_wait_time_ms

INTO #PostWorkWaitStats

FROM sys.dm_os_wait_stats

SELECT

[object_name], [counter_name], [instance_name]

, [cntr_value], [cntr_type]

INTO #PostWorkOSSnapShot

FROM sys.dm_os_performance_counters

SELECT

sql_handle, plan_handle, total_elapsed_time, total_worker_time

, total_logical_reads, total_logical_writes, total_clr_time

, execution_count, statement_start_offset, statement_end_offset

INTO #PostWorkQuerySnapShot

FROM sys.dm_exec_query_stats

SELECT

 DB_NAME(filestats.database_id) AS DBName

 ,files.name AS LogicalFileName

 ,files.physical_name

 ,num_of_reads AS NumberReads

 ,num_of_writes AS NumberWrites

 ,num_of_bytes_read AS BytesRead

 ,num_of_bytes_written AS BytesWritten

 ,io_stall_read_ms AS IOStallReadMS

 ,io_stall_write_ms AS IOStallWriteMS

 into #postdiskIOstats

FROM sys.dm_io_virtual_file_stats(-1,-1) filestats

INNER JOIN sys.master_files files

  ON filestats.file_id = files.file_id

  AND filestats.database_id = files.database_id

ORDER BY num_of_writes DESC

INSERT INTO [Query_SnapshotStats]

           ([Duration]

           ,[Time on CPU]

           ,[Time blocked]

           ,[Reads]

           ,[Writes]

           ,[CLR time]

           ,[Executions]

           ,[Individual Query]

           ,[Parent Query]

           ,[DatabaseName]

           ,[updatedDate])

SELECT

p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0) AS [Duration]

, p2.total_worker_time - ISNULL(p1.total_worker_time, 0) AS [Time on CPU]

, (p2.total_elapsed_time - ISNULL(p1.total_elapsed_time, 0)) -

(p2.total_worker_time - ISNULL(p1.total_worker_time, 0))

AS [Time blocked]

, p2.total_logical_reads - ISNULL(p1.total_logical_reads, 0) AS [Reads]

, p2.total_logical_writes - ISNULL(p1.total_logical_writes, 0)

AS [Writes]

, p2.total_clr_time - ISNULL(p1.total_clr_time, 0) AS [CLR time]

, p2.execution_count - ISNULL(p1.execution_count, 0) AS [Executions]

, SUBSTRING (qt.text,p2.statement_start_offset/2 + 1,

((CASE WHEN p2.statement_end_offset = -1

THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2

ELSE p2.statement_end_offset

END - p2.statement_start_offset)/2) + 1) AS [Individual Query]

, qt.text AS [Parent Query]

, DB_NAME(qt.dbid) AS DatabaseName

,GETDATE() as lastupdated

FROM #PreWorkQuerySnapShot p1

RIGHT OUTER JOIN

#PostWorkQuerySnapShot p2 ON p2.sql_handle =

ISNULL(p1.sql_handle, p2.sql_handle)

AND p2.plan_handle = ISNULL(p1.plan_handle, p2.plan_handle)

AND p2.statement_start_offset =

ISNULL(p1.statement_start_offset, p2.statement_start_offset)

AND p2.statement_end_offset =

ISNULL(p1.statement_end_offset, p2.statement_end_offset)

CROSS APPLY sys.dm_exec_sql_text(p2.sql_handle) as qt

WHERE p2.execution_count != ISNULL(p1.execution_count, 0)

AND qt.text NOT LIKE ''--ThisRoutineIdentifier%''

ORDER BY [Duration] DESC

INSERT INTO [Wait_SnapshotStats]

           ([wait_time_ms]

           ,[signal_wait_time_ms]

           ,[RealWait]

           ,[wait_type]

           ,[updatedDate])

SELECT

p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) AS wait_time_ms

, p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0) AS signal_wait_time_ms

, ((p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0)) - (p2.signal_wait_time_ms - ISNULL(p1.signal_wait_time_ms, 0)))

AS RealWait

, p2.wait_type

,GETDATE() as lastupdated

FROM #PreWorkWaitStats p1

RIGHT OUTER JOIN

#PostWorkWaitStats p2 ON p2.wait_type = ISNULL(p1.wait_type, p2.wait_type)

WHERE p2.wait_time_ms - ISNULL(p1.wait_time_ms, 0) > 0

AND p2.wait_type NOT LIKE ''%SLEEP%''

AND p2.wait_type != ''WAITFOR''

ORDER BY RealWait DESC

INSERT INTO [dbo].[PerfmonCounters_SnapshotStats]

           ([object_name]

           ,[counter_name]

           ,[instance_name]

           ,[InitialValue]

           ,[FinalValue]

           ,[Change]

           ,[% Change]

           ,[updatedDate])

SELECT

p2.object_name, p2.counter_name, p2.instance_name

, ISNULL(p1.cntr_value, 0) AS InitialValue

, p2.cntr_value AS FinalValue

, p2.cntr_value - ISNULL(p1.cntr_value, 0) AS Change

, (p2.cntr_value - ISNULL(p1.cntr_value, 0)) * 100 / p1.cntr_value

AS [% Change]

,GETDATE() as lastupdated

FROM #PreWorkOSSnapShot p1

RIGHT OUTER JOIN

#PostWorkOSSnapShot p2 ON p2.object_name =

ISNULL(p1.object_name, p2.object_name)

AND p2.counter_name = ISNULL(p1.counter_name, p2.counter_name)

AND p2.instance_name = ISNULL(p1.instance_name, p2.instance_name)

WHERE p2.cntr_value - ISNULL(p1.cntr_value, 0) > 0

AND ISNULL(p1.cntr_value, 0) != 0

ORDER BY [% Change] DESC, Change DESC

select po.BytesRead-Pr.BytesRead as Bytesread,po.BytesWritten-pr.BytesWritten as BytesWritten,

po.IOStallReadMS-pr.IOStallReadMS as IOStallReadms,PO.IOStallWriteMS-Pr.IOStallWriteMS as IOStallWriteMS ,Po.NumberReads-pr.NumberReads as Numberofreads,

PO.NumberWrites-pr.NumberWrites as Numberofwrites,Po.DBName,PO.LogicalFileName,PO.physical_name

into #diskIOStates_Changes

 from #prediskIOstats as pr

inner join #postdiskIOstats as po

on pr.DBName=Po.DBName and Pr.LogicalFileName=Po.LogicalFileName

INSERT INTO [dbo].[DiskIO_SnapshotStats]

           ([DBName]

           ,[LogicalFileName]

           ,[physical_name]

           ,[Bytesread]

           ,[BytesWritten]

           ,[IOStallReadms]

           ,[IOStallWriteMS]

           ,[Numberofreads]

           ,[Numberofwrites]

           ,[AvgReadTransfersMS]

           ,[AvgWriteTransfersMS]

           ,[updatedDate])

   

select DBName,LogicalFileName,physical_name,Bytesread,BytesWritten,IOStallReadms,IOStallWriteMS,Numberofreads,Numberofwrites,

CASE WHEN (Numberofreads = 0) THEN 0 ELSE IOStallReadms / Numberofreads END AS AvgReadTransfersMS

,CASE WHEN (Numberofwrites = 0) THEN 0 ELSE IOStallWriteMS /Numberofwrites END AS AvgWriteTransfersMS

,GETDATE() as lastupdated

 from #diskIOStates_Changes

DROP TABLE #PreWorkQuerySnapShot

DROP TABLE #PostWorkQuerySnapShot

DROP TABLE #PostWorkWaitStats

DROP TABLE #PreWorkWaitStats

DROP TABLE #PreWorkOSSnapShot

DROP TABLE #PostWorkOSSnapShot

DROP TABLE #prediskIOstats

DROP TABLE #postdiskIOstats

Drop table #diskIOStates_Changes

End'

END

GO

Test Data

CREATE TABLE #T(A INT IDENTITY PRIMARY KEY, B INT , F CHAR(8000) NULL);

INSERT INTO #T(B)

SELECT TOP (900000)  0 + CAST(NEWID() AS BINARY(4))

FROM master..spt_values v1,

     master..spt_values v2;

Performance Monitor

You can use the following performance counters to identify I/O bottlenecks. Note, these AVG values tend to be skewed (to the low side) if you have an infrequent collection interval. For example, it is hard to tell the nature of an I/O spike with 60-second snapshots. Also, you should not rely on one counter to determine a bottleneck; look for multiple counters to cross check the validity of your findings.

• PhysicalDisk Object: Avg. Disk Queue Length represents the average number of physical read and write requests that were queued on the selected physical disk during the sampling period. If your I/O system is overloaded, more read/write operations will be waiting. If your disk queue length frequently exceeds a value of 2 during peak usage of SQL Server, then you might have an I/O bottleneck.
• Avg. Disk Sec/Read is the average time, in seconds, of a read of data from the disk. Any number

·       Less than 10 ms - very good

·       Between 10 - 20 ms - okay

·       Between 20 - 50 ms - slow, needs attention

·       Greater than 50 ms – Serious I/O bottleneck

• Avg. Disk Sec/Write is the average time, in seconds, of a write of data to the disk. Please refer to the guideline in the previous bullet.
• Physical Disk: %Disk Time is the percentage of elapsed time that the selected disk drive was busy servicing read or write requests. A general guideline is that if this value is greater than 50 percent, it represents an I/O bottleneck.
• Avg. Disk Reads/Sec is the rate of read operations on the disk. You need to make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.
• Avg. Disk Writes/Sec is the rate of write operations on the disk. Make sure that this number is less than 85 percent of the disk capacity. The disk access time increases exponentially beyond 85 percent capacity.

When using above counters, you may need to adjust the values for RAID configurations using the following formulas.

Raid 0 -- I/Os per disk = (reads + writes) / number of disks

Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2

Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks

Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks

For example, you have a RAID-1 system with two physical disks with the following values of the counters.

Disk Reads/sec            80

Disk Writes/sec           70

Avg. Disk Queue Length    5

In that case, you are encountering (80 + (2 * 70))/2 = 110 I/Os per disk and your disk queue length = 5/2 = 2.5 which indicates a border line I/O bottleneck.

Link : 

https://technet.microsoft.com/en-us/library/cc966540.aspx#_I/O_Bottlenecks

PerfMonitor Screenshot 

Import Disk Latency Perfmon counters (CSV comma separated values) in to SQL Databases.

  Create table PerfAnalysis

  (

  Dateupdated varchar(255),

  [% Disk Time] varchar(255),

  [Avg. Disk Queue Length] varchar(255),

  [Avg. Disk sec/Read] varchar(255),

  [Avg. Disk sec/Write] varchar(255),

 [Disk Reads/sec] varchar(255),

  [Disk Writes/sec] nvarchar(255)

    )

    

    

     BULK INSERT PerfAnalysis

   FROM 'K:\DataCollector01.csv'

   WITH

      (

         FIELDTERMINATOR =',',

         ROWTERMINATOR ='\n'

      );

     

 select * from PerfAnalysis