SQLServerCentral.com / SQL Server 2008 / SQL Server 2008 Administration / acceptance got how much IO per seconds? / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 16:45:40 GMT20RE: acceptance got how much IO per seconds?http://www.sqlservercentral.com/Forums/Topic1532005-1550-1.aspxYou can try this one as well ... wrote by Paul Randal, I think ...[code="sql"] --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_nameFROM sys.dm_io_virtual_file_stats (NULL,NULL) AS vfsJOIN 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)) &gt;20ORDER BY Latency DESC--ORDER BY ReadLatency DESC, Drive;GO[/code]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.Wed, 22 Jan 2014 15:43:47 GMTsql-loverRE: acceptance got how much IO per seconds?http://www.sqlservercentral.com/Forums/Topic1532005-1550-1.aspxThere 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:[code="sql"]SELECT *FROM sys.dm_exec_query_stats AS deqsCROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS destCROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp;[/code]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. [url=http://technet.microsoft.com/en-us/library/bb630354(v=SQL.105).aspx]Here's an introduction to them[/url].Sun, 19 Jan 2014 03:43:18 GMTGrant Fritcheyacceptance got how much IO per seconds?http://www.sqlservercentral.com/Forums/Topic1532005-1550-1.aspxHi,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-166976Maximum 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 scriptsI 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.ThanksanandaFri, 17 Jan 2014 05:10:41 GMTSQL Galaxy