SELECT
–virtual file latency
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
–avg bytes per IOP
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
–[vfs].*,
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
ORDER BY [Latency] DESC
IO
Find IO statistics of a Database – SQL server
USE [Database_Name]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[IO_stats]
as
;with IOS as(SELECT TOP 100
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count,
[Total IO] = (total_logical_reads + total_logical_writes),
[Execution count] = qs.execution_count,
qs.last_execution_time as [Time],
qs.total_rows as [Total_rows],qs.total_physical_reads as [Total_physical_reads],qs.last_physical_reads as [Last_physical_reads],
[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN (CONVERT (NVARCHAR (MAX), qt.text)) * 2
ELSE qs.statement_end_offset END – qs.statement_start_offset)/2),
[Parent Query] = qt.text,
qt.objectid as ‘Object_ID’,
DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
where qt.dbid = DB_ID(‘Database_Name’)
ORDER BY [Average IO] DESC)
select P.Name as [Procedure Name],IOS.[Average IO],IOS.[Total IO],IOS.[Execution count],IOS.[Time],IOS.[Total_physical_reads],IOS.[Total_rows],IOS.[Individual Query],IOS.DatabaseName
from IOS left join sys.procedures P on IOS.Object_ID=P.object_id
Go
Exec IO_stats
selectstarfromyogi 