SQL EXPLORER: Isolate Storage Performance

/****** Object: StoredProcedure [dbo].[IOPending_Disk] Script Date: 16/12/2015 10:47:59 ******/

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

DROP PROCEDURE [dbo].[IOPending_Disk]

/****** Object: StoredProcedure [dbo].[IOMonitoring] Script Date: 16/12/2015 10:47:59 ******/

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

DROP PROCEDURE [dbo].[IOMonitoring]

/****** Object: StoredProcedure [dbo].[IOMonitoring] Script Date: 16/12/2015 10:47:59 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

BEGIN

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

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:05:00''

--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

/****** Object: StoredProcedure [dbo].[IOPending_Disk] Script Date: 16/12/2015 10:47:59 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

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

BEGIN

EXEC dbo.sp_executesql @statement = N'

CREATE Procedure [dbo].[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:30''

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

END

USE [msdb]

/****** Object: Job [IO-Monitoring] Script Date: 16/12/2015 10:49:11 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[KE Perf Testing]]] Script Date: 16/12/2015 10:49:11 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[KE Perf Testing]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[KE Perf Testing]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'IO-Monitoring',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[KE Perf Testing]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [IOMonitoring] Script Date: 16/12/2015 10:49:11 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'IOMonitoring',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'Exec IOMonitoring',

@database_name=N'DBA',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'IO-Monitoring',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=4,

@freq_subday_interval=10,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20151216,

@active_end_date=20151217,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N'42a6fec3-4158-426c-80ba-3fb6d2c92dd8'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

USE [msdb]

/****** Object: Job [IO_Task_Pending] Script Date: 16/12/2015 10:49:44 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[KE Perf Testing]]] Script Date: 16/12/2015 10:49:44 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[KE Perf Testing]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[KE Perf Testing]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'IO_Task_Pending',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=0,

@notify_level_netsend=0,

@notify_level_page=0,

@delete_level=0,

@description=N'No description available.',

@category_name=N'[KE Perf Testing]',

@owner_login_name=N'sa', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object: Step [IO_Pending_Task] Script Date: 16/12/2015 10:49:44 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'IO_Pending_Task',

@step_id=1,

@cmdexec_success_code=0,

@on_success_action=1,

@on_success_step_id=0,

@on_fail_action=2,

@on_fail_step_id=0,

@retry_attempts=0,

@retry_interval=0,

@os_run_priority=0, @subsystem=N'TSQL',

@command=N'

Exec IOPending_Disk ''2''',

@database_name=N'DBA',

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'IOPending_Request',

@enabled=1,

@freq_type=4,

@freq_interval=1,

@freq_subday_type=4,

@freq_subday_interval=5,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20151216,

@active_end_date=20151217,

@active_start_time=0,

@active_end_time=235959,

@schedule_uid=N'ea5a0b43-7bb0-4012-bc2b-18c633a4b6e6'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

Declare @date datetime

set @date ='2015-12-16 09:00:33.707'

SELECT [Database]

,[physical_name]

,[io_pending]

,[io_pending_ms_ticks]

,[io_type]

,[num_of_reads]

,[num_of_writes]

,[date]

FROM [dbo].[IOpending_Disktable]

where date>=@date

Declare @date1 datetime

set @date1 ='2015-12-16 09:00:33.707'

/****** Script for SelectTopNRows command from SSMS ******/

SELECT [DBName]

,[LogicalFileName]

,[physical_name]

,[Bytesread]

,[BytesWritten]

,[IOStallReadms]

,[IOStallWriteMS]

,[Numberofreads]

,[Numberofwrites]

,[AvgReadTransfersMS]

,[AvgWriteTransfersMS]

,[updatedDate]

FROM [dbo].[DiskIO_SnapshotStats]

where [updatedDate]>=@date1

SQL EXPLORER

About Me

Wednesday, December 16, 2015

Isolate Storage Performance

No comments:

Post a Comment

Followers