{"id":470,"date":"2012-11-02T08:20:51","date_gmt":"2012-11-02T08:20:51","guid":{"rendered":"\/blogs\/joe\/post\/Avoid-false-negatives-when-comparing-sysdm_io_virtual_file_stats-data-to-perfmon-counter-data.aspx"},"modified":"2013-02-11T12:30:39","modified_gmt":"2013-02-11T20:30:39","slug":"avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/","title":{"rendered":"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data"},"content":{"rendered":"

 <\/p>\n

A quick and somewhat nuanced tip today\u2026 When evaluating sys.dm_io_virtual_file_stats against collected perfmon counter data, be careful not to erroneously invalidate the latency findings by measuring and comparing against the averages<\/span><\/em> of already-averaged collected performance monitor data (e.g. Avg. Disk sec\/Write, Avg. Disk sec\/Read ).\u00a0 I\u2019ve seen scenarios where there is true I\/O latency that has been identified in dm_io_virtual_file_stats, but when the DBA goes to other teams to discuss, they lose the argument due to bad comparisons.<\/p>\n

To illustrate the point, I created a new database on one of my older, non-RAID USB drives:<\/p>\n

\r\nCREATE DATABASE [SampleIOHammering]\r\nCONTAINMENT = NONE\r\nON\u00a0 PRIMARY\r\n( NAME = N'SampleIOHammering',\r\nFILENAME = N'F:\\SampleIOHammering.mdf' ,\r\nSIZE = 8388608KB , FILEGROWTH = 1048576KB )\r\nLOG ON\r\n( NAME = N'SampleIOHammering_log',\r\nFILENAME = N'F:\\SampleIOHammering_log.ldf' ,\r\nSIZE = 1048576KB ,\r\nFILEGROWTH = 1048576KB );\r\nGO\r\n\r\nALTER DATABASE [SampleIOHammering] SET RECOVERY SIMPLE;\r\nGO\r\n<\/pre>\n
I\u2019ve intentionally used slow disk and the database creation operation took 7 minutes and 5 seconds (no IFI being used).<\/p>\n
Immediately after creating the database, I executed the following query (concentrating on write activity):<\/p>\n
\r\nSELECT\u00a0 file_id,\r\nio_stall_write_ms,\r\nnum_of_writes\r\nFROM\u00a0\u00a0\u00a0 sys.dm_io_virtual_file_stats(DB_ID('SampleIOHammering'), NULL);\r\n<\/pre>\n
This shows the following results \u2013 which don\u2019t truly represent the full lifecycle of the file creation and the associated latency:<\/p>\n\n\n\n\n\n
file_id<\/b><\/td>\nio_stall_write_ms<\/b><\/td>\nnum_of_writes<\/b><\/td>\n<\/tr>\n
1<\/b><\/td>\n76<\/td>\n10<\/td>\n<\/tr>\n
2<\/b><\/td>\n25<\/td>\n15<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
How can I tell that the accumulated stats are representative of the actual database creation?\u00a0 I did so through associated perfmon counters which were being collected via a data collector set concurrently\u2026 Here were the averages for the drive over the database creation period (and this is the average over the averages):<\/p>\n
\"image\"<\/a><\/p>\n
So we\u2019re definitely seeing latency that is beyond the recommended number represented in average disk seconds per write, and if anything, that latency isn\u2019t yet fully reflected in the virtual file stats DMV (yet).<\/p>\n
Next I created a new table and loaded it with 1.6 million rows from another database:<\/p>\n
\r\nUSE [SampleIOHammering];\r\nGO\r\n\r\nCREATE TABLE [dbo].[charge]\r\n(\r\n[charge_no] INT NOT NULL,\r\n[member_no] INT NOT NULL,\r\n[provider_no] INT NOT NULL,\r\n[category_no] INT NOT NULL,\r\n[charge_dt] [datetime] NOT NULL,\r\n[charge_amt] [money] NOT NULL,\r\n[statement_no] INT NOT NULL,\r\n[charge_code] CHAR(2) NOT NULL\r\n);\r\n\r\nGO\r\n\r\nINSERT\u00a0 [dbo].[charge]\r\nSELECT\u00a0 [c].[charge_no],\r\n[c].[member_no],\r\n[c].[provider_no],\r\n[c].[category_no],\r\n[c].[charge_dt],\r\n[c].[charge_amt],\r\n[c].[statement_no],\r\n[c].[charge_code]\r\nFROM\u00a0\u00a0\u00a0 [Credit].[dbo].[charge] AS c;\r\nGO\r\n<\/pre>\n
This shows the following virtual stats results:<\/p>\n\n\n\n\n\n
file_id<\/b><\/td>\nio_stall_write_ms<\/b><\/td>\nnum_of_writes<\/b><\/td>\n<\/tr>\n
1<\/b><\/td>\n8917<\/td>\n79<\/td>\n<\/tr>\n
2<\/b><\/td>\n575633<\/td>\n3783<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Unlike the database creation, we\u2019re now seeing some real stat accumulations. If we divide the IO stall write ms by the number of writes, we see about 152 ms as the average latency for the transaction log file (file 2).<\/p>\n
And now if we look at perfmon counters, we see the following associated latency data averages <\/strong>across the collection time period for my INSERT:<\/p>\n
\"image\"<\/a><\/p>\n
Focusing on Avg. Disk sec\/Write \u2013 we see 52 milliseconds certainly isn\u2019t ideal \u2013 but at this point you may have the storage or Windows admin say something like \u201cThis doesn\u2019t match your average of 152 milliseconds\u2026\u00a0 Your DMV must be wrong.\u201d<\/p>\n
But what if you switched from using an average of\u00a0 the perfmon data to the maximum<\/em>?<\/p>\n
\"image\"<\/a><\/p>\n
Making this change shifts the numbers as follows:<\/p>\n
\"image\"<\/a><\/p>\n
Notice now that the maximum average disk seconds per write is 156 milliseconds at peak \u2013 which is more in the ballpark of our 152 millisecond average we saw from a virtual file stats perspective.\u00a0 Granted it is the maximum measurement \u2013 but it brings us back into alignment with the two different data sources. Again, this is a nuanced point \u2013 but I\u2019ve noticed that sometimes non-SQL folks are doubtful about the SQL perspective of things, and so it helps to make sure you are comparing data in a fair way.<\/p>\n","protected":false},"excerpt":{"rendered":"
  A quick and somewhat nuanced tip today\u2026 When evaluating sys.dm_io_virtual_file_stats against collected perfmon counter data, be careful not to erroneously invalidate the latency findings by measuring and comparing against the averages of already-averaged collected performance monitor data (e.g. Avg. Disk sec\/Write, Avg. Disk sec\/Read ).\u00a0 I\u2019ve seen scenarios where there is true I\/O latency […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[],"class_list":["post-470","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"\nAvoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data - Joe Sack<\/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\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"  A quick and somewhat nuanced tip today\u2026 When evaluating sys.dm_io_virtual_file_stats against collected perfmon counter data, be careful not to erroneously invalidate the latency findings by measuring and comparing against the averages of already-averaged collected performance monitor data (e.g. Avg. Disk sec\/Write, Avg. Disk sec\/Read ).\u00a0 I\u2019ve seen scenarios where there is true I\/O latency […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-11-02T08:20:51+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-02-11T20:30:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/sys.dm_io_virtual_file_stats\/0571c167\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Joseph Sack\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Joseph Sack\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/\",\"name\":\"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-11-02T08:20:51+00:00\",\"dateModified\":\"2013-02-11T20:30:39+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/\",\"name\":\"Joe Sack\",\"description\":\"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\",\"name\":\"Joseph Sack\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g\",\"caption\":\"Joseph Sack\"},\"description\":\"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.\",\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/joe\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data - Joe Sack","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\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/","og_locale":"en_US","og_type":"article","og_title":"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data - Joe Sack","og_description":"  A quick and somewhat nuanced tip today\u2026 When evaluating sys.dm_io_virtual_file_stats against collected perfmon counter data, be careful not to erroneously invalidate the latency findings by measuring and comparing against the averages of already-averaged collected performance monitor data (e.g. Avg. Disk sec\/Write, Avg. Disk sec\/Read ).\u00a0 I\u2019ve seen scenarios where there is true I\/O latency […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/","og_site_name":"Joe Sack","article_published_time":"2012-11-02T08:20:51+00:00","article_modified_time":"2013-02-11T20:30:39+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/sys.dm_io_virtual_file_stats\/0571c167\/image_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/","name":"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-11-02T08:20:51+00:00","dateModified":"2013-02-11T20:30:39+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/avoid-false-negatives-when-comparing-sys-dm_io_virtual_file_stats-data-to-perfmon-counter-data\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/"},{"@type":"ListItem","position":2,"name":"Performance","item":"https:\/\/www.sqlskills.com\/blogs\/joe\/category\/performance\/"},{"@type":"ListItem","position":3,"name":"Avoid false negatives when comparing sys.dm_io_virtual_file_stats data to perfmon counter data"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/","name":"Joe Sack","description":"SQL Server Performance Tuning, High Availability and Disaster Recovery Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/joe\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648","name":"Joseph Sack","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/a4b39a7719a6bfff1add3ec00527810734579ee114d6d983e8e68f937b77be96?s=96&d=mm&r=g","caption":"Joseph Sack"},"description":"Joe Sack is a Principal Consultant with SQLskills. He has worked as a SQL Server professional since 1997 and has supported and developed for SQL Server environments in financial services, IT consulting, manufacturing, retail and the real estate industry. Prior to joining SQLskills he worked at Microsoft as a Premier Field Engineer supporting very large enterprise customer environments. He was responsible for providing deep SQL Server advisory services, training, troubleshooting and ongoing solutions guidance. His areas of expertise include performance tuning, scalability, T-SQL development and high-availability. In 2006 Joe earned the \u201cMicrosoft Certified Master: SQL Server 2005\u201d certification and in 2008 he earned the \u201cMicrosoft Certified Master: SQL Server 2008\u201d certification. In 2009 he took over responsibility for the entire SQL Server Microsoft Certified Master program and held that post until 2011. He was given the SQL Server MVP award in 2013.","sameAs":["http:\/\/3.209.169.194\/blogs\/joe","https:\/\/twitter.com\/https:\/\/twitter.com\/josephsack"],"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/author\/joe\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/comments?post=470"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/470\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=470"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}