{"id":17665,"date":"2021-12-11T18:16:28","date_gmt":"2021-12-11T12:46:28","guid":{"rendered":"http:\/\/onlineappsdba.com\/?p=17665"},"modified":"2021-12-11T18:16:28","modified_gmt":"2021-12-11T12:46:28","slug":"dp-300-day5","status":"publish","type":"post","link":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/","title":{"rendered":"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans"},"content":{"rendered":"<p>An execution plan in SQL Server is a simple graphical representation of the operations that the query optimizer generates to calculate the most efficient way to return a set of results. The query optimizer calculates a cost for multiple possible plans based on the statistics it has on the columns being utilized and the possible indexes that can be used for each operation in each query plan.<\/p>\n<p><b data-stringify-type=\"bold\">What are Execution Plans?<\/b><\/p>\n<p>Every time you execute a query, the query optimizer uses a path of operations to process your query and retrieve data results. the optimizer also collects statistics about the operation and execution of this plan. After being generated by the optimizer, query plans are cached in the area of memory called the plan cache. The Database engine stores these SQL Server Query Plans internally as XML.<\/p>\n<p>Do check our blog at <a href=\"https:\/\/k21academy.com\/microsoft-azure\/database-administrator\/dp-300-day5\/?utm_source=onlineappsdba&amp;utm_medium=referral&amp;utm_campaign=dp300day5_december21\" target=\"_blank\" rel=\"noopener noreferrer\">k21academy.com\/dp300day5<\/a> for more information<\/p>\n<p><b data-stringify-type=\"bold\">What is Query Optimizer?<\/b><\/p>\n<p>The Query Optimizer takes a query and returns a query plan and will attempt several plans to choose the best guess and generating a plan has high CPU cost, So SQL Server caches plans.<\/p>\n<p><b data-stringify-type=\"bold\">What are the Common plan operators for sorting and filtering?<\/b><\/p>\n<p><b data-stringify-type=\"bold\">Nested Loops:<\/b>\u00a0Performs inner, outer, semi and anti semi joins Performs a search on the inner table for each row of the outer table<br \/>\n<b data-stringify-type=\"bold\">Hash Match:\u00a0<\/b>Creates a hash for required columns for each row Then creates a hash for the second table and finds matches<br \/>\n<b data-stringify-type=\"bold\">TOP:<\/b>\u00a0Returns the specified top number of rows<br \/>\n<b data-stringify-type=\"bold\">Sort:<\/b>\u00a0Sorts the incoming rows<br \/>\n<b data-stringify-type=\"bold\">Stream Aggregate:<\/b> groups rows by one or more columns and calculates one or more aggregate expressions<\/p>\n<p>If you are planning to become a Microsoft Azure Database Administrator Associate, then join the\u00a0<strong>FREE CLASS<\/strong>\u00a0now at\u00a0<a href=\"https:\/\/k21academy.com\/microsoft-azure-database-training-free-class\/?utm_source=onlineappsdba&amp;utm_medium=referral&amp;utm_campaign=dp30002_November21\" target=\"_blank\" rel=\"noopener noreferrer\"><em><strong>https:\/\/k21academy.com\/ dp30002<\/strong><\/em><\/a><\/p>\n<p><a href=\"https:\/\/k21academy.com\/microsoft-azure-database-training-free-class\/?utm_source=onlineappsdba&amp;utm_medium=referral&amp;utm_campaign=dp30002_November21\" target=\"_blank\" rel=\"noopener noreferrer\"><img decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/k21academy.com\/wp-content\/uploads\/2021\/06\/DBAdmin_CU.gif\" alt=\"DBAdmin_CU\" \/><\/a><\/p>\n<p>Motivate your friends as well as it is the next big hit in the technology world. Also, do not forget to join us on our FREE Telegram group\u00a0<a class=\"c-link\" tabindex=\"-1\" href=\"https:\/\/t.me\/k21microsoftazure\" target=\"_blank\" rel=\"noopener noreferrer\" data-stringify-link=\"https:\/\/t.me\/k21microsoftazure\" data-sk=\"tooltip_parent\" data-remove-tab-index=\"true\">https:\/\/t.me\/k21microsoftazure<\/a>\u00a0, and be the first to receive Microsoft Azure related news and updates.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>An execution plan in SQL Server is a simple graphical representation of the operations that the query optimizer generates to [&hellip;]<\/p>\n","protected":false},"author":115,"featured_media":17666,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[568,540],"tags":[],"class_list":["post-17665","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dp-300","category-microsoft-azure"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v25.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans<\/title>\n<meta name=\"description\" content=\"On our Day 5 Live Session of the [DP-300], we covered the concepts of Understanding SQL Server Query Plans, Explore Performance-based Database Design\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans\" \/>\n<meta property=\"og:description\" content=\"On our Day 5 Live Session of the [DP-300], we covered the concepts of Understanding SQL Server Query Plans, Explore Performance-based Database Design\" \/>\n<meta property=\"og:url\" content=\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-12-11T12:46:28+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1273\" \/>\n\t<meta property=\"og:image:height\" content=\"714\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Masroof Ahmad\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Masroof Ahmad\" \/>\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:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/\",\"url\":\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/\",\"name\":\"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans\",\"isPartOf\":{\"@id\":\"https:\/\/onlineappsdba.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png\",\"datePublished\":\"2021-12-11T12:46:28+00:00\",\"author\":{\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\"},\"description\":\"On our Day 5 Live Session of the [DP-300], we covered the concepts of Understanding SQL Server Query Plans, Explore Performance-based Database Design\",\"breadcrumb\":{\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#primaryimage\",\"url\":\"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png\",\"contentUrl\":\"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png\",\"width\":1273,\"height\":714},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/onlineappsdba.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/onlineappsdba.com\/#website\",\"url\":\"https:\/\/onlineappsdba.com\/\",\"name\":\"\",\"description\":\"Oracle Implementation &amp; Training Experts\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/onlineappsdba.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb\",\"name\":\"Masroof Ahmad\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g\",\"caption\":\"Masroof Ahmad\"},\"url\":\"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans","description":"On our Day 5 Live Session of the [DP-300], we covered the concepts of Understanding SQL Server Query Plans, Explore Performance-based Database Design","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:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/","og_locale":"en_US","og_type":"article","og_title":"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans","og_description":"On our Day 5 Live Session of the [DP-300], we covered the concepts of Understanding SQL Server Query Plans, Explore Performance-based Database Design","og_url":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/","article_published_time":"2021-12-11T12:46:28+00:00","og_image":[{"width":1273,"height":714,"url":"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png","type":"image\/png"}],"author":"Masroof Ahmad","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Masroof Ahmad","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/","url":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/","name":"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans","isPartOf":{"@id":"https:\/\/onlineappsdba.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#primaryimage"},"image":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#primaryimage"},"thumbnailUrl":"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png","datePublished":"2021-12-11T12:46:28+00:00","author":{"@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb"},"description":"On our Day 5 Live Session of the [DP-300], we covered the concepts of Understanding SQL Server Query Plans, Explore Performance-based Database Design","breadcrumb":{"@id":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#primaryimage","url":"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png","contentUrl":"https:\/\/onlineappsdba.com\/wp-content\/uploads\/2021\/12\/DP300Day5_BlogImage.png","width":1273,"height":714},{"@type":"BreadcrumbList","@id":"https:\/\/onlineappsdba.com\/index.php\/2021\/12\/11\/dp-300-day5\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/onlineappsdba.com\/"},{"@type":"ListItem","position":2,"name":"[DP-300] Day5 Q\/A Review \u2013 Understanding SQL Server Query Plans"}]},{"@type":"WebSite","@id":"https:\/\/onlineappsdba.com\/#website","url":"https:\/\/onlineappsdba.com\/","name":"","description":"Oracle Implementation &amp; Training Experts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/onlineappsdba.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/909a876ed58d400faf82caf81d61bfdb","name":"Masroof Ahmad","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/onlineappsdba.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/10f9db7bdbbd7f9ccfbe9b2d208e5978fc28315e9c704383e639a926ea0fce5f?s=96&d=mm&r=g","caption":"Masroof Ahmad"},"url":"https:\/\/onlineappsdba.com\/index.php\/author\/masroof\/"}]}},"_links":{"self":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/17665","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/users\/115"}],"replies":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/comments?post=17665"}],"version-history":[{"count":0,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/posts\/17665\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media\/17666"}],"wp:attachment":[{"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/media?parent=17665"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/categories?post=17665"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/onlineappsdba.com\/index.php\/wp-json\/wp\/v2\/tags?post=17665"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}