Multi Statement Table-Valued function (MSTVF) VS Inline Table-Valued function (ITVF) – Inline is better

-

π–³π—π—‚π—Œ 𝗐𝖾𝖾𝗄, 𝖨 π–Ύπ—‡π–Όπ—ˆπ—Žπ—‡π—π–Ύπ—‹π–Ύπ–½ 𝖺 π—Šπ—Žπ–Ύπ—‹π—’ π—‰π–Ύπ—‹π–Ώπ—ˆπ—‹π—†π–Ίπ—‡π–Όπ–Ύ π—‚π—Œπ—Œπ—Žπ–Ύ π–½π—Žπ–Ύ π—π—ˆ 𝗍𝗁𝖾 π—Žπ—Œπ–Ύ π—ˆπ–Ώ 𝖺 π—†π—Žπ—…π—π—‚-π—Œπ—π–Ίπ—π–Ύπ—†π–Ύπ—‡π— 𝗍𝖺𝖻𝗅𝖾𝖽 π—π–Ίπ—…π—Žπ–Ύπ–½ π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡ (𝖬𝖲𝖳𝖡π–₯). 𝖳𝗁𝖾 π—Šπ—Žπ–Ύπ—‹π—’ π—‰π–Ύπ—‹π–Ώπ—ˆπ—‹π—†π—Œ π–Όπ—‹π—ˆπ—Œπ—Œ 𝖺𝗉𝗉𝗅𝗒 π—π—ˆ 𝖺 𝖬𝖲𝖳𝖡π–₯. 𝖠𝖿𝗍𝖾𝗋 π—†π—ˆπ–½π—‚π–Ώπ—’π—‚π—‡π—€ 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡ π—π—ˆ 𝖺𝗇 𝗂𝗇𝗅𝗂𝗇𝖾 𝗍𝖺𝖻𝗅𝖾-π—π–Ίπ—…π—Žπ–Ύπ–½ π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡ (𝖨𝖳𝖡π–₯), 𝗍𝗁𝖾 π—‰π–Ύπ—‹π–Ώπ—ˆπ—‹π—†π–Ίπ—‡π–Όπ–Ύ π—‚π—†π—‰π—‹π—ˆπ—π–Ύπ–½ 𝖻𝗒 𝟣πŸͺ𝗑. 

π– π—Œ 𝗍𝗁𝖾 𝗇𝖺𝗆𝖾 π—Œπ—Žπ—€π—€π–Ύπ—Œπ—π—Œ, 𝖬𝖲𝖳𝖡π–₯ π–Όπ—ˆπ—‡π—π–Ίπ—‚π—‡π—Œ π—†π—Žπ—…π—π—‚π—‰π—…π–Ύ π—Œπ—π–Ίπ—π–Ύπ—†π–Ύπ—‡π—π—Œ 𝗐𝗂𝗍𝗁𝗂𝗇 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡, 𝗐𝗁𝗂𝗅𝖾 𝖺𝗇 𝖨𝖳𝖡π–₯ π—‚π—Œ 𝖺 π—Œπ—‚π—‡π—€π—…π–Ύ π—Œπ—π–Ίπ—π–Ύπ—†π–Ύπ—‡π—. π–’π—ˆπ—‡π—Œπ–Ύπ—Šπ—Žπ–Ύπ—‡π—π—…π—’, 𝖲𝖰𝖫 𝖲𝖾𝗋𝗏𝖾𝗋 π—π–Ίπ—‡π–½π—…π–Ύπ—Œ 𝗍𝗁𝖾𝗆 𝖽𝗂𝖿𝖿𝖾𝗋𝖾𝗇𝗍𝗅𝗒.  

𝖲𝖰𝖫 𝖲𝖾𝗋𝗏𝖾𝗋 π—π—‹π–Ύπ–Ίπ—π—Œ 𝖺𝗇 𝖨𝖳𝖡π–₯ π—Œπ—‚π—†π—‚π—…π–Ίπ—‹π—…π—’ π—π—ˆ 𝖺 𝗏𝗂𝖾𝗐, 𝗂𝗇𝗅𝗂𝗇𝗂𝗇𝗀 𝗍𝗁𝖾 π—…π—ˆπ—€π—‚π–Όπ–Ίπ—… π—Šπ—Žπ–Ύπ—‹π—’ π—ˆπ–Ώ 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡ π—‚π—‡π—π—ˆ 𝗍𝗁𝖾 π—ˆπ—Žπ—π–Ύπ—‹ π—Šπ—Žπ–Ύπ—‹π—’ 𝗍𝗁𝖺𝗍 π–Όπ–Ίπ—…π—…π—Œ 𝗂𝗍 π–»π–Ύπ–Ώπ—ˆπ—‹π–Ύ π–Όπ—ˆπ—†π—‰π—‚π—…π–Ίπ—π—‚π—ˆπ—‡. π–§π—ˆπ—π–Ύπ—π–Ύπ—‹, 𝗂𝗍 𝖼𝖺𝗇’𝗍 π–½π—ˆ 𝗍𝗁𝖾 π—Œπ–Ίπ—†π–Ύ 𝗐𝗂𝗍𝗁 𝖺𝗇 𝖬𝖲𝖳𝖡π–₯. 𝖨𝗍 𝖼𝖺𝗇’𝗍 𝗂𝗇𝗅𝗂𝗇𝖾 𝖺𝗇 𝖬𝖲𝖳𝖡π–₯. π–¨π—‡π—Œπ—π–Ύπ–Ίπ–½, 𝖲𝖰𝖫 𝖲𝖾𝗋𝗏𝖾𝗋 π—‰π—‹π—ˆπ–Όπ–Ύπ—Œπ—Œπ–Ύπ—Œ 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡ π–Ίπ—Œ 𝖺 π—Œπ–Ύπ—‰π–Ίπ—‹π–Ίπ—π–Ύ π–Ύπ—‘π–Ύπ–Όπ—Žπ—π—‚π—ˆπ—‡, π—Œπ—π—ˆπ—‹π—‚π—‡π—€ 𝗍𝗁𝖾 π—‹π–Ύπ—Œπ—Žπ—…π— π—‚π—‡π—π—ˆ 𝖺 𝗍𝖺𝖻𝗅𝖾 𝗏𝖺𝗋𝗂𝖺𝖻𝗅𝖾 𝖺𝗇𝖽 𝗍𝗁𝖾𝗇 π—ƒπ—ˆπ—‚π—‡π—‚π—‡π—€ 𝗂𝗍 𝖻𝖺𝖼𝗄 π—π—ˆ 𝗍𝗁𝖾 𝖼𝖺𝗅𝗅𝗂𝗇𝗀 π—Šπ—Žπ–Ύπ—‹π—’. 

π–³π—π—‚π—Œ π—…π–Ύπ–Ίπ–½π—Œ π—π—ˆ 𝗍𝗁𝗋𝖾𝖾 π—π–Ίπ—’π—Œ 𝗍𝗁𝖺𝗍 𝖬𝖲𝖳𝖡π–₯ 𝖼𝖺𝗇 π–Όπ–Ίπ—Žπ—Œπ–Ύ 𝖽𝖾𝗍𝗋𝗂𝗆𝖾𝗇𝗍𝖺𝗅 π—‰π–Ύπ—‹π–Ώπ—ˆπ—‹π—†π–Ίπ—‡π–Όπ–Ύ π—π—ˆ 𝗍𝗁𝖾 π—Šπ—Žπ–Ύπ—‹π—’: 

    πŸ£. 𝖱𝖑𝖠𝖱 - π–±π—ˆπ— 𝖻𝗒 π–Ίπ—€π—ˆπ—‡π—‚π—“π—‚π—‡π—€ π—‹π—ˆπ—: 𝖬𝖲𝖳𝖡π–₯ π—Žπ—Œπ–Ύπ—Œ π—‹π—ˆπ—-𝖻𝗒-π—‹π—ˆπ— π—‰π—‹π—ˆπ–Όπ–Ύπ—Œπ—Œπ—‚π—‡π—€, π—ƒπ—Žπ—Œπ— 𝗅𝗂𝗄𝖾 𝖺 π—…π—ˆπ—ˆπ—‰, π—π—π—Žπ—Œ π—’π—ˆπ—Ž 𝖼𝖺𝗇’𝗍 𝗍𝖺𝗄𝖾 𝖺𝖽𝗏𝖺𝗇𝗍𝖺𝗀𝖾 π—ˆπ–Ώ 𝗍𝗁𝖾 𝖲𝖰𝖫 π—Œπ–Ύπ—-π–»π–Ίπ—Œπ–Ύπ–½ π—‰π—‹π—ˆπ–Όπ–Ύπ—Œπ—Œπ—‚π—‡π—€.  

    πŸ€. π–¨π—‡π–Ίπ–Όπ–Όπ—Žπ—‹π–Ίπ—π–Ύ 𝖼𝖺𝗋𝖽𝗂𝗇𝖺𝗅𝗂𝗍𝗒 π–Ύπ—Œπ—π—‚π—†π–Ίπ—π–Ύπ—Œ: 𝖲𝖰𝖫 𝖲𝖾𝗋𝗏𝖾𝗋 π—π—‹π–Ύπ–Ίπ—π—Œ 𝗍𝗁𝖾 π—‹π–Ύπ—Œπ—Žπ—…π— π–Ώπ—‹π—ˆπ—† 𝖺𝗇 𝖬𝖲𝖳𝖡π–₯ 𝗅𝗂𝗄𝖾 𝖺 𝗍𝖺𝖻𝗅𝖾 𝗏𝖺𝗋𝗂𝖺𝖻𝗅𝖾, 𝗆𝖾𝖺𝗇𝗂𝗇𝗀 𝗍𝗁𝖾 π—ˆπ—‰π—π—‚π—†π—‚π—“π–Ύπ—‹ π–½π—ˆπ–Ύπ—Œπ—‡’𝗍 𝗁𝖺𝗏𝖾 π—Œπ—π–Ίπ—π—‚π—Œπ—π—‚π–Όπ—Œ π–Ίπ–»π—ˆπ—Žπ— 𝗂𝗍 𝖺𝗇𝖽 π–Ίπ—Œπ—Œπ—Žπ—†π–Ύπ—Œ 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡ π—‹π–Ύπ—π—Žπ—‹π—‡π—Œ π—ˆπ—‡π—…π—’ π—ˆπ—‡π–Ύ π—‹π—ˆπ—. π–³π—π—‚π—Œ π—ˆπ–Ώπ—π–Ύπ—‡ π—‹π–Ύπ—Œπ—Žπ—…π—π—Œ 𝗂𝗇 π—Žπ—‡π–½π–Ύπ—‹π–Ύπ—Œπ—π—‚π—†π–Ίπ—π–Ύπ–½ π—‹π—ˆπ— π–Όπ—ˆπ—Žπ—‡π—π—Œ 𝖺𝗇𝖽 π—Œπ—Žπ–»π—ˆπ—‰π—π—‚π—†π–Ίπ—… π–Ύπ—‘π–Ύπ–Όπ—Žπ—π—‚π—ˆπ—‡ π—‰π—…π–Ίπ—‡π—Œ. 

    πŸ₯. π–³π—‹π—ˆπ—Žπ–»π—…π–Ύπ—Œπ—π—ˆπ—ˆπ—π—‚π—‡π—€ π–½π—‚π–Ώπ–Ώπ—‚π–Όπ—Žπ—…π—π—’: π–Έπ—ˆπ—Ž 𝖼𝖺𝗇’𝗍 π—Œπ–Ύπ–Ύ 𝗍𝗁𝖾 π–Ύπ—‘π–Ύπ–Όπ—Žπ—π—‚π—ˆπ—‡ 𝗉𝗅𝖺𝗇, 𝗆𝖺𝗄𝗂𝗇𝗀 π—π—‹π—ˆπ—Žπ–»π—…π–Ύπ—Œπ—π—ˆπ—ˆπ—π—‚π—‡π—€ π—†π—ˆπ—‹π–Ύ 𝖼𝗁𝖺𝗅𝗅𝖾𝗇𝗀𝗂𝗇𝗀. 

𝖳𝗁𝖾 π–Ώπ—ˆπ—…π—…π—ˆπ—π—‚π—‡π—€ 𝖼𝗁𝖺𝗋𝗍 π—‚π—…π—…π—Žπ—Œπ—π—‹π–Ίπ—π–Ύπ—Œ 𝗍𝗁𝖾 𝗂𝗆𝗉𝖺𝖼𝗍 π—ˆπ—‡ 𝗍𝗁𝖾 π—Šπ—Žπ–Ύπ—‹π—’ π—‰π–Ύπ—‹π–Ώπ—ˆπ—‹π—†π–Ίπ—‡π–Όπ–Ύ 𝗐𝗁𝖾𝗇 π—Žπ—Œπ—‚π—‡π—€ 𝖬𝖲𝖳𝖡π–₯ π—π–Ύπ—‹π—Œπ—Žπ—Œ 𝖨𝖳𝖡π–₯: 

    - 𝖳𝗁𝖾 𝗑-π–Ίπ—‘π—‚π—Œ π—Œπ—π—ˆπ—π—Œ 𝗍𝗁𝖾 π—‡π—Žπ—†π–»π–Ύπ—‹ π—ˆπ–Ώ π—‹π—ˆπ—π—Œ π—‹π–Ύπ—π—Žπ—‹π—‡π–Ύπ–½ 𝖻𝗒 𝗍𝗁𝖾 π—ˆπ—Žπ—π–Ύπ—‹ π—Šπ—Žπ–Ύπ—‹π—’ 𝖺𝗇𝖽 π—‰π–Ίπ—Œπ—Œπ–Ύπ–½ π—π—ˆ 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡. 

    - 𝖳𝗁𝖾 𝗒-π–Ίπ—‘π—‚π—Œ π—‹π–Ύπ—‰π—‹π–Ύπ—Œπ–Ύπ—‡π—π—Œ 𝗍𝗁𝖾 π–½π—Žπ—‹π–Ίπ—π—‚π—ˆπ—‡ π—ˆπ–Ώ 𝗍𝗁𝖾 π—Šπ—Žπ–Ύπ—‹π—’ π—‰π—‹π—ˆπ–Όπ–Ύπ—Œπ—Œπ—‚π—‡π—€ π—Žπ—Œπ—‚π—‡π—€ 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡π—Œ. 

𝖳𝗁𝖾 𝖒𝖯𝖴 𝗍𝗂𝗆𝖾 π–Ώπ—ˆπ—‹ 𝗍𝗁𝖾 𝖬𝖲𝖳𝖡π–₯ π—€π—‹π—ˆπ—π—Œ 𝖺𝗍 𝖺 π—†π—Žπ–Όπ— π–Ώπ–Ίπ—Œπ—π–Ύπ—‹ 𝗋𝖺𝗍𝖾 𝗍𝗁𝖺𝗇 𝗍𝗁𝖺𝗍 π—ˆπ–Ώ 𝗍𝗁𝖾 𝖨𝖳𝖡π–₯ π–Ίπ—Œ 𝗍𝗁𝖾 π—‡π—Žπ—†π–»π–Ύπ—‹ π—ˆπ–Ώ π—‹π—ˆπ—π—Œ π—‰π–Ίπ—Œπ—Œπ–Ύπ–½ π—π—ˆ 𝗍𝗁𝖾 π–Ώπ—Žπ—‡π–Όπ—π—‚π—ˆπ—‡π—Œ π—‚π—‡π–Όπ—‹π–Ύπ–Ίπ—Œπ–Ύπ—Œ. 𝖨𝖳𝖡π–₯ π—Œπ–Όπ–Ίπ—…π–Ύπ—Œ 𝖻𝖾𝗍𝗍𝖾𝗋 𝗍𝗁𝖺𝗇 𝖬𝖲𝖳𝖡π–₯ 𝗐𝗁𝖾𝗇 π—‰π—‹π—ˆπ–Όπ–Ύπ—Œπ—Œπ—‚π—‡π—€ 𝗅𝖺𝗋𝗀𝖾𝗋 π–Ίπ—†π—ˆπ—Žπ—‡π— π—ˆπ–Ώ 𝖽𝖺𝗍𝖺.



 

Comments

Post a Comment

Popular posts from this blog

SQL SERVER INDEX (Part 1): Index Seek vs Index Scan and Statistics

-
Image
Firstly, I recommend watching this  How to Think Like the Engine - YouTube  by Brent Ozar on YouTube (all parts). It is incredibly helpful foundational material before you begin your SQL performance tuning journey. The SQL Server optimizer   is responsible for determining which operations —such as scans or seeks — should be used to query the least number of 8KB pages. However, the optimizer sometimes needs a DBA's intervention. For example, an ideal index for a query might not exist yet. In such cases, it is up to the DBA to create the index so that the optimizer can make use of it. Similarly, inaccurate row count estimations on the execution plan can lead to suboptimal choices by the optimizer. As DBAs, we may need to update statistics or tune the code depending on the cause of the misestimations. When we have accurate indexes and correct row estimations , the optimizer's choices are usually optimal, and we can stop our part of tuning the query. In this post, I will...
Read more

πŸ“ SQL Server Performance: Troubleshooting Memory Grant Overestimation using SQL Diagnostic Manager and SQL Server execution plan

-
Image
Have you ever wondered how SQL Server uses memory? Have you ever encountered a memory issue in SQL Server? Did you know that SQL Server isn't smart sometimes and it can overestimate and grant a significant amount of memory just for a single query to run —up to one-fifth of the server’s total memory 😱? If you are curious to learn more, read on πŸ€“. Introduction In SQL Server, before executing a query, a compilation phase may be triggered to generate an execution plan. An execution plan determines 3 key aspects: the order in which source tables are accessed, the operations used to retrieve data (e.g. seek, index/table scan), and the methods for joining, filtering, sorting, or aggregating data ( Execution plan overview - SQL Server | Microsoft Learn ). SQL Server can bypass the compilation phase if it finds a reusable cached plan for the query. During the compilation phase, SQL Server estimates how much data each operation (e.g. read, join, sort, aggregate) will handle and...
Read more

πŸ“ SQL Server Performance: Solving Memory Grant Overestimation - the limitations of Memory Grant Feedback and the power of SQL Tuning

-
Image
In an earlier blog SQL Server Performance: Troubleshooting Memory Grant Overestimation using SQL Diagnostic Manager and SQL Server execution plan (mydbadventure.blogspot.com) , I introduced how SQL Server miscalculated the required memory for processing a complex query, leading to memory contention on the server and impacting other concurrent queries. In this blog, we will explore and evaluate different approaches to resolve the issue ✨. Are you excited to follow along with me? If so, let’s dive in!!! πŸ€— Introduction When troubleshooting a SQL Server system issue, I found it effective to first identify the problem, and then weigh pros and cons, and exceptions of different solutions. For example, in this case, some solutions might not work, others work but take more time, but one solution stands out as the best solution. Don’t be easily fooled by any single approach πŸ˜‰. Let’s focus on understanding the problem and researching different ways to solve it. As I did a lot of res...
Read more