Posts

Showing posts from November, 2024

Tools I Used During Query Performance Troubleshooting

-
Image
1. Execution Plan - An execution plan in SQL Server is a detailed roadmap created by the SQL Server Query Optimizer to determine the most efficient way to execute a query. It shows which operators being used (e.g. scans, seeks, nested join, hash match, ...), data volume, access order, indexes being used, row estimates, and other valuable information that helps understand how the optimizer views and optimizes queries. - There are two types of execution plan: estimated execution plan and actual execution plan. An Actual Execution Plan includes both estimated and run time properties, thus I prefer using actual execution plans for troubleshooting. An actual execution plan allows me to identify if there is a discrepancy in the estimated number of rows and the actual value. - To view actual execution plans, we enable  Include Actual Execution Plan on the tool bar and execute queries. - However, if we encounter a situation when we can't run queries to obtain actual execution...
Post a Comment
Read more

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

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