Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

acceptance got how much IO per seconds? Expand / Collapse
Posted Friday, January 17, 2014 5:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:27 AM
Points: 1,084, Visits: 3,089

I have collected IO historical data with help of

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.

Post #1532005
Posted Sunday, January 19, 2014 3:43 AM



Group: General Forum Members
Last Login: Today @ 5:41 AM
Points: 14,652, Visits: 28,904
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:

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
The Scary DBA
Author of:
SQL Server Query Performance Tuning
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1532433
Posted Wednesday, January 22, 2014 3:43 PM


Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 475, Visits: 1,464
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,
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 ReadLatency DESC, Drive;

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.
Post #1533875
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse