acceptance got how much IO per seconds?

Question

acceptance got how much IO per seconds?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
January 17, 2014 at 5:10 am

#282470

Hi,
I have collected IO historical data with help of http://sqlserverio.com/2011/02/08/gather-virtual-file-statistics-using-t-sql-tsql2sday-15/#comment-166976
Maximum reached 985 seconds per transaction with in the 30 seconds time intervel and minimum is 0. So I want capture the SQL Statement which is using more than 30 seconds for IO latency , pls provide the scripts
I wants to determine for the performance IO Latency, as per my server current configuration as below how much IO per seconds acceptance.
1. Data file & log file – Both are storing at Disk volume “D” and RAID 5 controller
2. Single array controller to attach all the HD.
Thanks
ananda

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

There are basically two good ways to get query metrics out of the system. You can query the cache and see what's there for query performance, or you can audit the queries. To query the cache, you can just select from sys.dm_exec_query_stats. You can combine that with sys.dm_exec_sql_text and sys.dm_exec_query_plan to get the statement text and the execution plan. A very simple query would be done like this:

SELECT *

FROM sys.dm_exec_query_stats AS deqs

CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp;

From there you can select more or less columns, order by information as needed, etc. To audit queries, assuming you're on SQL Server 2008 or better, I strongly recommend using extended events. Here's an introduction to them.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sql-lover SSCoach Points: 18530 More actions · Answer 2

You can try this one as well ... wrote by Paul Randal, I think ...

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

--WHERE vfs.file_id = 2 -- log files

--WHERE (io_stall / (num_of_reads + num_of_writes)) >20

ORDER BY Latency DESC

--ORDER BY ReadLatency DESC, Drive;

GO

It gives you the latency values for each database file, including Tlog. Not exactly what you asked, which is per T-SQL statement, but it will tell you which databases is the more I/O intensive on your server.