{"id":494,"date":"2012-05-24T10:50:00","date_gmt":"2012-05-24T10:50:00","guid":{"rendered":"\/blogs\/joe\/post\/Tale-of-the-Inconsistent-UDF-Logical-IOs.aspx"},"modified":"2013-01-02T20:31:56","modified_gmt":"2013-01-03T04:31:56","slug":"tale-of-the-inconsistent-udf-logical-ios","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/","title":{"rendered":"Tale of the Inconsistent UDF Logical IOs"},"content":{"rendered":"<p>This post was motivated by an email question I got this week.<\/p>\n<p>Imagine you have the following scalar UDF:<\/p>\n<div class=\"csharpcode\">\n<pre class=\"alt\"><span class=\"kwrd\">CREATE<\/span> <span class=\"kwrd\">FUNCTION<\/span> dbo.RemoveYear (@<span class=\"kwrd\">date<\/span> datetime)<\/pre>\n<pre><span class=\"kwrd\">RETURNS<\/span> datetime<\/pre>\n<pre class=\"alt\"><span class=\"kwrd\">AS<\/span><\/pre>\n<pre><span class=\"kwrd\">BEGIN<\/span><\/pre>\n<pre class=\"alt\">    <\/pre>\n<pre>    <span class=\"kwrd\">DECLARE<\/span> @removeyear datetime = DATEADD(<span class=\"kwrd\">year<\/span>, -1, @<span class=\"kwrd\">date<\/span>);<\/pre>\n<pre class=\"alt\">&#160;<\/pre>\n<pre>    <span class=\"kwrd\">RETURN<\/span>(@removeyear);<\/pre>\n<pre class=\"alt\"><span class=\"kwrd\">END<\/span>;<\/pre>\n<pre>GO<\/pre>\n<\/div>\n<style type=\"text\/css\">\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>Now \u2013 aside from the fact that this function doesn\u2019t really need to exist in the first place since we could use the DATEADD function directly, the key point of this example is that we have a scalar UDF that doesn\u2019t access a data source unless you decide to apply it as part of a data accessing query.<\/p>\n<p>If I execute the UDF as follows, how many logical reads would you expect to see for the very first execution on the server?<\/p>\n<div class=\"csharpcode\">\n<pre class=\"alt\"><span class=\"kwrd\">SELECT<\/span> dbo.RemoveYear (<span class=\"str\">'12\/31\/1999'<\/span>);<\/pre>\n<pre>GO<\/pre>\n<\/div>\n<style type=\"text\/css\">\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>If I have SET STATISTICS IO ON, the answer is zero. If I\u2019m using SQL Profiler or Extended Events in SQL Server 2012, the answer is \u201c2\u201d.&#160; <\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/0238e830\/image.png\"><img fetchpriority=\"high\" decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/61b1a87d\/image_thumb.png\" width=\"642\" height=\"98\" \/><\/a><\/p>\n<p>What about consecutive executions?&#160; Let\u2019s free the procedure cache and give it a try\u2026<\/p>\n<pre class=\"csharpcode\"><span class=\"kwrd\">DBCC<\/span> FREEPROCCACHE;\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"kwrd\">SELECT<\/span> dbo.RemoveYear (<span class=\"str\">'12\/31\/1999'<\/span>);\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"kwrd\">SELECT<\/span> dbo.RemoveYear (<span class=\"str\">'12\/31\/1999'<\/span>);\r\n<span class=\"kwrd\">GO<\/span>\r\n\r\n<span class=\"kwrd\">SELECT<\/span> dbo.RemoveYear (<span class=\"str\">'12\/31\/1999'<\/span>);\r\nGO<\/pre>\n<p><style type=\"text\/css\">\n.csharpcode, .csharpcode pre\n{\n\tfont-size: small;\n\tcolor: black;\n\tfont-family: consolas, \"Courier New\", courier, monospace;\n\tbackground-color: #ffffff;\n\t\/*white-space: pre;*\/\n}\n.csharpcode pre { margin: 0em; }\n.csharpcode .rem { color: #008000; }\n.csharpcode .kwrd { color: #0000ff; }\n.csharpcode .str { color: #006080; }\n.csharpcode .op { color: #0000c0; }\n.csharpcode .preproc { color: #cc6633; }\n.csharpcode .asp { background-color: #ffff00; }\n.csharpcode .html { color: #800000; }\n.csharpcode .attr { color: #ff0000; }\n.csharpcode .alt \n{\n\tbackground-color: #f4f4f4;\n\twidth: 100%;\n\tmargin: 0em;\n}\n.csharpcode .lnum { color: #606060; }<\/style>\n<p>What do we see for reads?&#160; <\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/67f87f0b\/image.png\"><img decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/1983eca1\/image_thumb.png\" width=\"579\" height=\"124\" \/><\/a><\/p>\n<p>We see 2 reads for the initial post-DBCC FREEPROCCACHE and then 0 reads for consecutive executions.&#160; <\/p>\n<p>If I create an Extended Events session to look at any cache lookups, I see the following:<\/p>\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/31861f8c\/image.png\"><img decoding=\"async\" style=\"background-image: none; border-bottom: 0px; border-left: 0px; padding-left: 0px; padding-right: 0px; display: inline; border-top: 0px; border-right: 0px; padding-top: 0px\" title=\"image\" border=\"0\" alt=\"image\" src=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/298e7d2a\/image_thumb.png\" width=\"826\" height=\"302\" \/><\/a><\/p>\n<p>This was tested on SQL Server 2012, but I believe you\u2019ll see this in earlier versions as well.&#160; The takeaway is that we\u2019re not just tracking data page reads here. Post DBCC FREEPROCCACHE I see two cache attempts and then 2 logical reads for the statement completion.&#160; Consecutive calls show the attempts AND hit and then zero logical reads.<\/p>\n<p>If you\u2019ve seen similar or conflicting behavior, I\u2019d be quite interested to hear about it in the comments of this post.&#160; Thanks!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This post was motivated by an email question I got this week. Imagine you have the following scalar UDF: CREATE FUNCTION dbo.RemoveYear (@date datetime) RETURNS datetime AS BEGIN DECLARE @removeyear datetime = DATEADD(year, -1, @date); &#160; RETURN(@removeyear); END; GO Now \u2013 aside from the fact that this function doesn\u2019t really need to exist in the [&hellip;]<\/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-494","post","type-post","status-publish","format-standard","hentry","category-performance"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.9.1 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Tale of the Inconsistent UDF Logical IOs - 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\/tale-of-the-inconsistent-udf-logical-ios\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Tale of the Inconsistent UDF Logical IOs - Joe Sack\" \/>\n<meta property=\"og:description\" content=\"This post was motivated by an email question I got this week. Imagine you have the following scalar UDF: CREATE FUNCTION dbo.RemoveYear (@date datetime) RETURNS datetime AS BEGIN DECLARE @removeyear datetime = DATEADD(year, -1, @date); &#160; RETURN(@removeyear); END; GO Now \u2013 aside from the fact that this function doesn\u2019t really need to exist in the [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/\" \/>\n<meta property=\"og:site_name\" content=\"Joe Sack\" \/>\n<meta property=\"article:published_time\" content=\"2012-05-24T10:50:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-03T04:31:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/61b1a87d\/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=\"2 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\/tale-of-the-inconsistent-udf-logical-ios\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/\",\"name\":\"Tale of the Inconsistent UDF Logical IOs - Joe Sack\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#website\"},\"datePublished\":\"2012-05-24T10:50:00+00:00\",\"dateModified\":\"2013-01-03T04:31:56+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/#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\":\"Tale of the Inconsistent UDF Logical IOs\"}]},{\"@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":"Tale of the Inconsistent UDF Logical IOs - 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\/tale-of-the-inconsistent-udf-logical-ios\/","og_locale":"en_US","og_type":"article","og_title":"Tale of the Inconsistent UDF Logical IOs - Joe Sack","og_description":"This post was motivated by an email question I got this week. Imagine you have the following scalar UDF: CREATE FUNCTION dbo.RemoveYear (@date datetime) RETURNS datetime AS BEGIN DECLARE @removeyear datetime = DATEADD(year, -1, @date); &#160; RETURN(@removeyear); END; GO Now \u2013 aside from the fact that this function doesn\u2019t really need to exist in the [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/","og_site_name":"Joe Sack","article_published_time":"2012-05-24T10:50:00+00:00","article_modified_time":"2013-01-03T04:31:56+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-content\/uploads\/windows-live-writer\/d567ee4204e5\/61b1a87d\/image_thumb.png"}],"author":"Joseph Sack","twitter_misc":{"Written by":"Joseph Sack","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/","url":"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/","name":"Tale of the Inconsistent UDF Logical IOs - Joe Sack","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#website"},"datePublished":"2012-05-24T10:50:00+00:00","dateModified":"2013-01-03T04:31:56+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/#\/schema\/person\/533eb0113a15fb5a6e8067a49e4ae648"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/joe\/tale-of-the-inconsistent-udf-logical-ios\/#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":"Tale of the Inconsistent UDF Logical IOs"}]},{"@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\/494","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=494"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/posts\/494\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/media?parent=494"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/categories?post=494"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/joe\/wp-json\/wp\/v2\/tags?post=494"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}