{"id":4424,"date":"2014-11-12T14:46:59","date_gmt":"2014-11-12T22:46:59","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4424"},"modified":"2018-09-20T05:09:55","modified_gmt":"2018-09-20T12:09:55","slug":"capturing-io-latencies-period-time","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/","title":{"rendered":"Capturing IO latencies for a period of time"},"content":{"rendered":"<p>In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I\/O latencies that occurred over a period of time.<\/p>\n<p>The script does the following:<\/p>\n<ul>\n<li>Creates two temporary tables<\/li>\n<li>Captures the output from sys.dm_io_virtual_file_stats into the first table<\/li>\n<li>Waits for a configurable delay (line 41 in the script &#8211; I made it 30 minutes in the example)<\/li>\n<li>Captures the output from sys.dm_io_virtual_file_stats into the second\u00a0table<\/li>\n<li>Provides my usual virtual file stats output on the results<\/li>\n<\/ul>\n<p>The original code and explanation about using the DMV are <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-examine-io-subsystem-latencies-from-within-sql-server\/\" target=\"_blank\">here<\/a>.<\/p>\n<p>Enjoy!<\/p>\n<pre class=\"brush: sql; title: ; toolbar: true; wrap-lines: true; notranslate\" title=\"\">\r\n\/*============================================================================\r\n  File:     ShortPeriodIOLatencies.sql\r\n\r\n  Summary:  Short snapshot of I\/O latencies\r\n\r\n  SQL Server Versions: 2005 onwards\r\n------------------------------------------------------------------------------\r\n  Written by Paul S. Randal, SQLskills.com\r\n\r\n  (c) 2014, SQLskills.com. All rights reserved.\r\n\r\n  For more scripts and sample code, check out http:\/\/www.SQLskills.com\r\n\r\n  You may alter this code for your own *non-commercial* purposes (e.g. in a\r\n  for-sale commercial tool). Use in your own environment is encouraged.\r\n  You may republish altered code as long as you include this copyright and\r\n  give due credit, but you must obtain prior permission before blogging\r\n  this code.\r\n\r\n  THIS CODE AND INFORMATION ARE PROVIDED &quot;AS IS&quot; WITHOUT WARRANTY OF\r\n  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED\r\n  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND\/OR FITNESS FOR A\r\n  PARTICULAR PURPOSE.\r\n============================================================================*\/\r\n \r\n\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n    WHERE &#x5B;name] = N'##SQLskillsStats1')\r\n    DROP TABLE &#x5B;##SQLskillsStats1];\r\n\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n    WHERE &#x5B;name] = N'##SQLskillsStats2')\r\n    DROP TABLE &#x5B;##SQLskillsStats2];\r\nGO\r\n\r\nSELECT &#x5B;database_id], &#x5B;file_id], &#x5B;num_of_reads], &#x5B;io_stall_read_ms],\r\n\t   &#x5B;num_of_writes], &#x5B;io_stall_write_ms], &#x5B;io_stall],\r\n\t   &#x5B;num_of_bytes_read], &#x5B;num_of_bytes_written], &#x5B;file_handle]\r\nINTO ##SQLskillsStats1\r\nFROM sys.dm_io_virtual_file_stats (NULL, NULL);\r\nGO\r\n\r\nWAITFOR DELAY '00:30:00';\r\nGO\r\n\r\nSELECT &#x5B;database_id], &#x5B;file_id], &#x5B;num_of_reads], &#x5B;io_stall_read_ms],\r\n\t   &#x5B;num_of_writes], &#x5B;io_stall_write_ms], &#x5B;io_stall],\r\n\t   &#x5B;num_of_bytes_read], &#x5B;num_of_bytes_written], &#x5B;file_handle]\r\nINTO ##SQLskillsStats2\r\nFROM sys.dm_io_virtual_file_stats (NULL, NULL);\r\nGO\r\n\r\nWITH &#x5B;DiffLatencies] AS\r\n(SELECT\r\n-- Files that weren't in the first snapshot\r\n        &#x5B;ts2].&#x5B;database_id],\r\n        &#x5B;ts2].&#x5B;file_id],\r\n        &#x5B;ts2].&#x5B;num_of_reads],\r\n        &#x5B;ts2].&#x5B;io_stall_read_ms],\r\n\t\t&#x5B;ts2].&#x5B;num_of_writes],\r\n\t\t&#x5B;ts2].&#x5B;io_stall_write_ms],\r\n\t\t&#x5B;ts2].&#x5B;io_stall],\r\n\t\t&#x5B;ts2].&#x5B;num_of_bytes_read],\r\n\t\t&#x5B;ts2].&#x5B;num_of_bytes_written]\r\n    FROM &#x5B;##SQLskillsStats2] AS &#x5B;ts2]\r\n    LEFT OUTER JOIN &#x5B;##SQLskillsStats1] AS &#x5B;ts1]\r\n        ON &#x5B;ts2].&#x5B;file_handle] = &#x5B;ts1].&#x5B;file_handle]\r\n    WHERE &#x5B;ts1].&#x5B;file_handle] IS NULL\r\nUNION\r\nSELECT\r\n-- Diff of latencies in both snapshots\r\n        &#x5B;ts2].&#x5B;database_id],\r\n        &#x5B;ts2].&#x5B;file_id],\r\n        &#x5B;ts2].&#x5B;num_of_reads] - &#x5B;ts1].&#x5B;num_of_reads] AS &#x5B;num_of_reads],\r\n        &#x5B;ts2].&#x5B;io_stall_read_ms] - &#x5B;ts1].&#x5B;io_stall_read_ms] AS &#x5B;io_stall_read_ms],\r\n\t\t&#x5B;ts2].&#x5B;num_of_writes] - &#x5B;ts1].&#x5B;num_of_writes] AS &#x5B;num_of_writes],\r\n\t\t&#x5B;ts2].&#x5B;io_stall_write_ms] - &#x5B;ts1].&#x5B;io_stall_write_ms] AS &#x5B;io_stall_write_ms],\r\n\t\t&#x5B;ts2].&#x5B;io_stall] - &#x5B;ts1].&#x5B;io_stall] AS &#x5B;io_stall],\r\n\t\t&#x5B;ts2].&#x5B;num_of_bytes_read] - &#x5B;ts1].&#x5B;num_of_bytes_read] AS &#x5B;num_of_bytes_read],\r\n\t\t&#x5B;ts2].&#x5B;num_of_bytes_written] - &#x5B;ts1].&#x5B;num_of_bytes_written] AS &#x5B;num_of_bytes_written]\r\n    FROM &#x5B;##SQLskillsStats2] AS &#x5B;ts2]\r\n    LEFT OUTER JOIN &#x5B;##SQLskillsStats1] AS &#x5B;ts1]\r\n        ON &#x5B;ts2].&#x5B;file_handle] = &#x5B;ts1].&#x5B;file_handle]\r\n    WHERE &#x5B;ts1].&#x5B;file_handle] IS NOT NULL)\r\nSELECT\r\n\tDB_NAME (&#x5B;vfs].&#x5B;database_id]) AS &#x5B;DB],\r\n\tLEFT (&#x5B;mf].&#x5B;physical_name], 2) AS &#x5B;Drive],\r\n\t&#x5B;mf].&#x5B;type_desc],\r\n\t&#x5B;num_of_reads] AS &#x5B;Reads],\r\n\t&#x5B;num_of_writes] AS &#x5B;Writes],\r\n\t&#x5B;ReadLatency(ms)] =\r\n\t\tCASE WHEN &#x5B;num_of_reads] = 0\r\n\t\t\tTHEN 0 ELSE (&#x5B;io_stall_read_ms] \/ &#x5B;num_of_reads]) END,\r\n\t&#x5B;WriteLatency(ms)] =\r\n\t\tCASE WHEN &#x5B;num_of_writes] = 0\r\n\t\t\tTHEN 0 ELSE (&#x5B;io_stall_write_ms] \/ &#x5B;num_of_writes]) END,\r\n\t-- &#x5B;Latency] =\r\n\t\t-- CASE WHEN (&#x5B;num_of_reads] = 0 AND &#x5B;num_of_writes] = 0)\r\n\t\t\t-- THEN 0 ELSE (&#x5B;io_stall] \/ (&#x5B;num_of_reads] + &#x5B;num_of_writes])) END,\r\n\t&#x5B;AvgBPerRead] =\r\n\t\tCASE WHEN &#x5B;num_of_reads] = 0\r\n\t\t\tTHEN 0 ELSE (&#x5B;num_of_bytes_read] \/ &#x5B;num_of_reads]) END,\r\n\t&#x5B;AvgBPerWrite] =\r\n\t\tCASE WHEN &#x5B;num_of_writes] = 0\r\n\t\t\tTHEN 0 ELSE (&#x5B;num_of_bytes_written] \/ &#x5B;num_of_writes]) END,\r\n\t-- &#x5B;AvgBPerTransfer] =\r\n\t\t-- CASE WHEN (&#x5B;num_of_reads] = 0 AND &#x5B;num_of_writes] = 0)\r\n\t\t\t-- THEN 0 ELSE\r\n\t\t\t\t-- ((&#x5B;num_of_bytes_read] + &#x5B;num_of_bytes_written]) \/\r\n\t\t\t\t-- (&#x5B;num_of_reads] + &#x5B;num_of_writes])) END,\r\n\t&#x5B;mf].&#x5B;physical_name]\r\nFROM &#x5B;DiffLatencies] AS &#x5B;vfs]\r\nJOIN sys.master_files AS &#x5B;mf]\r\n\tON &#x5B;vfs].&#x5B;database_id] = &#x5B;mf].&#x5B;database_id]\r\n\tAND &#x5B;vfs].&#x5B;file_id] = &#x5B;mf].&#x5B;file_id]\r\n-- ORDER BY &#x5B;ReadLatency(ms)] DESC\r\nORDER BY &#x5B;WriteLatency(ms)] DESC;\r\nGO\r\n\r\n-- Cleanup\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n    WHERE &#x5B;name] = N'##SQLskillsStats1')\r\n    DROP TABLE &#x5B;##SQLskillsStats1];\r\n\r\nIF EXISTS (SELECT * FROM &#x5B;tempdb].&#x5B;sys].&#x5B;objects]\r\n    WHERE &#x5B;name] = N'##SQLskillsStats2')\r\n    DROP TABLE &#x5B;##SQLskillsStats2];\r\nGO\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I\/O latencies that occurred over a period of time. The script does the [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,38,53,66,101],"tags":[],"class_list":["post-4424","post","type-post","status-publish","format-standard","hentry","category-buffer-pool","category-example-scripts","category-io-subsystems","category-performance-tuning","category-wait-stats"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Capturing IO latencies for a period of time - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Capturing IO latencies for a period of time - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I\/O latencies that occurred over a period of time. The script does the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2014-11-12T22:46:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-09-20T12:09:55+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/\",\"name\":\"Capturing IO latencies for a period of time - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2014-11-12T22:46:59+00:00\",\"dateModified\":\"2018-09-20T12:09:55+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Capturing IO latencies for a period of time\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Capturing IO latencies for a period of time - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/","og_locale":"en_US","og_type":"article","og_title":"Capturing IO latencies for a period of time - Paul S. Randal","og_description":"In both my wait statistics pre-conference workshops at the PASS Summit and SQLintersection I promised to do a bunch of blog posts. The second one on the list is a simple script to allow you to capture all the reads, writes, and I\/O latencies that occurred over a period of time. The script does the [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/","og_site_name":"Paul S. Randal","article_published_time":"2014-11-12T22:46:59+00:00","article_modified_time":"2018-09-20T12:09:55+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/","name":"Capturing IO latencies for a period of time - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2014-11-12T22:46:59+00:00","dateModified":"2018-09-20T12:09:55+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/capturing-io-latencies-period-time\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Capturing IO latencies for a period of time"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4424","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=4424"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4424\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4424"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4424"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4424"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}