{"id":2104,"date":"2017-06-07T16:51:02","date_gmt":"2017-06-07T20:51:02","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/jonathan\/?p=2104"},"modified":"2017-06-07T18:29:53","modified_gmt":"2017-06-07T22:29:53","slug":"sql-101-parallelism-inhibitors-scalar-user-defined-functions","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/","title":{"rendered":"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions"},"content":{"rendered":"

As Kimberly blogged about recently<\/a>, SQLskills is embarking on a new initiative to blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101<\/strong> blog posts, check out SQLskills.com\/help\/SQL101<\/a>.<\/em><\/p>\n

At the spring SQLintersection conference in Orlando, one of the attendees asked me a question about why a query wouldn\u2019t go parallel even though the cost for the plan was in the hundreds.\u00a0 There are actually a number of different reasons why a query might execute serially, but usually one of the first things that comes to mind is scalar user defined functions. Developers love to use scalar user defined functions inside of SQL Server because it lets them compartmentalize code and easily reuse it, which is a common goal in object-oriented programming, but it\u2019s also a performance anti-pattern for SQL Server as I\u2019ll demonstrate in this post.<\/p>\n

For the purposes of this post, I\u2019m using the WorldWideImporters Standard Edition example database, and I\u2019m going to recreate a scalar user defined function that I recently saw in a client engagement that was used to format the output of a column strictly for client presentation purposes.\u00a0 Using the [Warehouse].[ColdRoomTemperatures_Archive] table, which stores 5 second intervals of temperature readings as the basis for my example report, we are going to calculate the delta temperature between samples. For the delta if the number is greater than zero, then we want it to show with a + sign before the value and it if is below zero a \u2013 sign. An example query to provide the basic data set for the report would be:<\/p>\n

\r\nUSE [WideWorldImporters]\r\nGO\r\nSELECT \r\n    a.ColdRoomSensorNumber,\r\n    a.Temperature AS StartingTemp, \r\n    b.Temperature AS EndingTemp, \r\n    a.ValidFrom, \r\n    a.ValidTo, \r\n    b.Temperature - a.Temperature AS Delta \r\nFROM Warehouse.ColdRoomTemperatures_Archive AS a\r\nINNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b \r\n   ON a.ValidTo = b.ValidFrom \r\n      AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber\r\nWHERE a.ValidFrom BETWEEN '05\/01\/2016' AND '05\/02\/2016'\r\nORDER BY ColdRoomSensorNumber, ValidFrom;\r\n<\/pre>\n
For the sake of argument, the scenario I am using is a presentation layer issue, and the argument can be made that this problem should be handled by the presentation\/application tier to do the formatting requested.\u00a0 However, there could be scenarios where the same data is needed in more than one application, reports, API feeds, etc. so for consistency the formatting is determined to be required for the SQL output.\u00a0 This is where a developer might write a function to handle the formatting so the code can be reused anytime we need to output temperature deltas to make sure that everything does it exactly the same way.<\/p>\n
\r\nIF OBJECT_ID('dbo.GetFormatedTemperatureDelta') IS NOT NULL\r\n    DROP FUNCTION dbo.GetFormatedTemperatureDelta;\r\nGO\r\nCREATE FUNCTION dbo.GetFormatedTemperatureDelta\r\n(@StartingTemperature decimal(10,2), @EndingTemperature decimal(10,2))\r\nRETURNS VARCHAR(10)\r\nAS \r\nBEGIN\r\n    DECLARE @Result VARCHAR(10);\r\n    SET @Result =    CASE \r\n                        WHEN @StartingTemperature-@EndingTemperature > 0 \r\n                            THEN '+'+CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)\r\n                        WHEN @StartingTemperature-@EndingTemperature < 0 \r\n                            THEN CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)\r\n                        ELSE '0.00' \r\n                    END;    \r\n    RETURN(@Result);\r\nEND\r\nGO\r\n\r\nSELECT \r\n    a.ColdRoomSensorNumber,\r\n    a.Temperature AS StartingTemp, \r\n    b.Temperature AS EndingTemp, \r\n    a.ValidFrom, \r\n    a.ValidTo, \r\n    dbo.GetFormatedTemperatureDelta(b.Temperature, a.Temperature) AS Delta \r\nFROM Warehouse.ColdRoomTemperatures_Archive AS a\r\nINNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b \r\n   ON a.ValidTo = b.ValidFrom \r\n      AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber\r\nWHERE a.ValidFrom BETWEEN '05\/01\/2016' AND '05\/02\/2016'\r\nORDER BY ColdRoomSensorNumber, ValidFrom;\r\n<\/pre>\n
As soon as they apply this formatting to the reporting query using the function, performance may or may not be noticeably impacted. Using the above two queries the duration of execution for both is just above 1 second of time based on the current data being requested for the report as shown by the STATISTICS TIME output for both:<\/p>\n
SQL Server Execution Times:
\nCPU time = 1155 ms,\u00a0 elapsed time = 643 ms.<\/p>\n
SQL Server Execution Times:
\nCPU time = 1250 ms,\u00a0 elapsed time = 1680 ms<\/p><\/blockquote>\n
However, notice the difference in the duration for the original statement vs the statement using the user defined function.\u00a0 The user defined function took over 2.5 times the duration.\u00a0 This problem gets worse as the size of the data set gets larger, but what we don\u2019t see is the impact that this had to the way the query executes.\u00a0 Comparing the before function use and after function use execution plans:<\/p>\n
\"image\"<\/a>
\nPlan Without Function <\/strong><\/span><\/p>\n
\"image\"<\/a>
\nPlan With Function<\/strong><\/span><\/p>\n
Before using the function, we had a parallel execution and after the plan is now serial.\u00a0 If we look at what happens for each statements execution with Extended Events using the sqlserver.sql_statement_completed and sqlserver.module_end events, filtered to my specific session_id and tracking how events relate to each other with TRACK_CAUSLITY=ON for the session, we\u2019ll find that the function is executing for every row returned by the query, turning our set based operation into a RBAR (row-by-agonizing-row) operation.<\/p>\n
\r\nCREATE EVENT SESSION [TrackFunctions] ON SERVER \r\nADD EVENT sqlserver.module_end(\r\n    WHERE ([sqlserver].[session_id]=(85))),\r\nADD EVENT sqlserver.sql_statement_completed(\r\n    WHERE ([sqlserver].[session_id]=(85)))\r\nWITH (TRACK_CAUSALITY=ON)\r\nGO\r\n<\/pre>\n
\"image\"<\/a>
\nExtended Event Session Results (grouped by activity_id)<\/span><\/strong><\/p>\n
We could change the function to do nothing at all and the impacts would be exactly the same.\u00a0 The query with the scalar user defined function will always run serially and row-by-row with the exception of if the function is created using SQLCLR which does allow scalar user defined functions that DO NOT perform data access to leverage parallelism.\u00a0 However, for this simple logic SQLCLR wouldn\u2019t be required or recommended just to gain parallel query execution back. The easier fix is to simply inline the code to the original query:<\/p>\n
\r\nSELECT \r\n    a.ColdRoomSensorNumber,\r\n    a.Temperature AS StartingTemp, \r\n    b.Temperature AS EndingTemp, \r\n    a.ValidFrom, \r\n    a.ValidTo, \r\n    CASE WHEN b.Temperature - a.Temperature > 0 THEN '+'+CAST(b.Temperature-a.Temperature AS VARCHAR)\r\n        WHEN b.Temperature - a.Temperature < 0 THEN CAST(b.Temperature-a.Temperature AS VARCHAR)\r\n        ELSE '0.00' END         \r\n         AS Delta \r\nFROM Warehouse.ColdRoomTemperatures_Archive AS a\r\nINNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b \r\n   ON a.ValidTo = b.ValidFrom \r\n      AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber\r\nWHERE a.ValidFrom BETWEEN '05\/01\/2016' AND '05\/02\/2016'\r\nORDER BY ColdRoomSensorNumber, ValidFrom\r\nGO\r\n<\/pre>\n
SQL Server Execution Times:
\nCPU time = 1233 ms,\u00a0 elapsed time = 676 ms.<\/p><\/blockquote>\n
\"image\"<\/a>
\nPlan with inline expression<\/strong><\/p>\n
Here we have the fast execution time with a parallel plan but we are sacrificing the reuse of the code for other places where we might want to encapsulate the same logic.\u00a0 However, that doesn\u2019t have to be the case since this logic can be done inline to the query, it can also be written into an inline-table valued function.<\/p>\n
\r\nIF OBJECT_ID('dbo.GetFormatedTemperatureDelta_tvf') IS NOT NULL\r\n    DROP FUNCTION dbo.GetFormatedTemperatureDelta_tvf;\r\nGO\r\nCREATE FUNCTION dbo.GetFormatedTemperatureDelta_tvf\r\n(@StartingTemperature decimal(10,2), @EndingTemperature decimal(10,2))\r\nRETURNS TABLE\r\nAS \r\nRETURN (SELECT Delta =CASE \r\n                        WHEN @StartingTemperature-@EndingTemperature > 0 \r\n                            THEN '+'+CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)\r\n                        WHEN @StartingTemperature-@EndingTemperature < 0 \r\n                            THEN CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)\r\n                        ELSE '0.00' \r\n                    END)\r\nGO\r\n\r\nSELECT \r\n    a.ColdRoomSensorNumber,\r\n    a.Temperature AS StartingTemp, \r\n    b.Temperature AS EndingTemp, \r\n    a.ValidFrom, \r\n    a.ValidTo, \r\n    Delta \r\nFROM Warehouse.ColdRoomTemperatures_Archive AS a\r\nINNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b \r\n   ON a.ValidTo = b.ValidFrom \r\n      AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber\r\nCROSS APPLY dbo.GetFormatedTemperatureDelta_tvf(b.Temperature, a.Temperature)\r\nWHERE a.ValidFrom BETWEEN '05\/01\/2016' AND '05\/02\/2016'\r\nORDER BY ColdRoomSensorNumber, ValidFrom\r\nGO\r\n<\/pre>\n
SQL Server Execution Times:
\nCPU time = 1251 ms,\u00a0 elapsed time = 729 ms.<\/p><\/blockquote>\n
\"image\"<\/a>
\nPlan With TVF<\/span><\/strong><\/p>\n
Here we get the best of both worlds, a parallel execution plan and the ability to reuse this logic in other places where we need the formatted output.\u00a0 If you look closely at the plan and the Compute Scalar operator, you will find that it is identical to the plan with inline code shown above.\u00a0 As a consultant, I\u2019ve done a lot of conversions of scalar user defined functions to inline table valued functions to resolve performance issues and improve code scalability for clients. This is a very common problem to see in engagements and understanding the impacts of scalar user defined functions to performance is important for fast performance and optimizing TSQL code.<\/p>\n","protected":false},"excerpt":{"rendered":"
As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we\u2019re calling SQL101. We\u2019ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[49,52],"tags":[],"class_list":["post-2104","post","type-post","status-publish","format-standard","hentry","category-performance-tuning","category-sql101"],"yoast_head":"\nSQL 101: Parallelism Inhibitors – Scalar User Defined Functions - Jonathan Kehayias<\/title>\n<meta name=\"description\" content=\"Learn how Scalar User Defined Functions negatively affect SQL Server query execution performance and prevent parallelism.\" \/>\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\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Learn how Scalar User Defined Functions negatively affect SQL Server query execution performance and prevent parallelism.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2017-06-07T20:51:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-06-07T22:29:53+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2017\/06\/image_thumb.png\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions\",\"datePublished\":\"2017-06-07T20:51:02+00:00\",\"dateModified\":\"2017-06-07T22:29:53+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/\"},\"wordCount\":1373,\"commentCount\":1,\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/image_thumb.png\",\"articleSection\":[\"Performance Tuning\",\"SQL101\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/\",\"name\":\"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/image_thumb.png\",\"datePublished\":\"2017-06-07T20:51:02+00:00\",\"dateModified\":\"2017-06-07T22:29:53+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"description\":\"Learn how Scalar User Defined Functions negatively affect SQL Server query execution performance and prevent parallelism.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/image_thumb.png\",\"contentUrl\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/wp-content\\\/uploads\\\/2017\\\/06\\\/image_thumb.png\",\"width\":892,\"height\":195},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance Tuning\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/performance-tuning\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?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\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions - Jonathan Kehayias","description":"Learn how Scalar User Defined Functions negatively affect SQL Server query execution performance and prevent parallelism.","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\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/","og_locale":"en_US","og_type":"article","og_title":"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions - Jonathan Kehayias","og_description":"Learn how Scalar User Defined Functions negatively affect SQL Server query execution performance and prevent parallelism.","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/","og_site_name":"Jonathan Kehayias","article_published_time":"2017-06-07T20:51:02+00:00","article_modified_time":"2017-06-07T22:29:53+00:00","og_image":[{"url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2017\/06\/image_thumb.png","type":"","width":"","height":""}],"author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions","datePublished":"2017-06-07T20:51:02+00:00","dateModified":"2017-06-07T22:29:53+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/"},"wordCount":1373,"commentCount":1,"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2017\/06\/image_thumb.png","articleSection":["Performance Tuning","SQL101"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/","name":"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#primaryimage"},"image":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#primaryimage"},"thumbnailUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2017\/06\/image_thumb.png","datePublished":"2017-06-07T20:51:02+00:00","dateModified":"2017-06-07T22:29:53+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"description":"Learn how Scalar User Defined Functions negatively affect SQL Server query execution performance and prevent parallelism.","breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#primaryimage","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2017\/06\/image_thumb.png","contentUrl":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-content\/uploads\/2017\/06\/image_thumb.png","width":892,"height":195},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/sql-101-parallelism-inhibitors-scalar-user-defined-functions\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Performance Tuning","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/performance-tuning\/"},{"@type":"ListItem","position":3,"name":"SQL 101: Parallelism Inhibitors – Scalar User Defined Functions"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?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\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/2104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=2104"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/2104\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=2104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=2104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=2104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}