{"id":1040,"date":"2012-07-26T15:27:55","date_gmt":"2012-07-26T19:27:55","guid":{"rendered":"http:\/\/sqlity.net\/en\/?page_id=1040"},"modified":"2014-11-04T15:19:53","modified_gmt":"2014-11-04T20:19:53","slug":"sql-server-query-execution-internals","status":"publish","type":"page","link":"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/","title":{"rendered":"SQL Server \u2013 Query Execution Internals"},"content":{"rendered":"<h3>Course Description<\/h3>\n<p>\nThis is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query.\n<\/p>\n<h3>Course Objectives<\/h3>\n<p>\nAfter successful completion of this course, students will be able to:\n<\/p>\n<ul>\n<li>generate graphical  and XML execution plans<\/li>\n<li>access \"post-compilation\" execution plans and \"post-execution\" execution plans<\/li>\n<li>read and interpret execution plans<\/li>\n<li>analyze the control flow as well as the data flow of an execution plan<\/li>\n<li>identify estimated and actual values of\n<ul>\n<li>CPU cost<\/li>\n<li>I\/O cost<\/li>\n<li>Row counts<\/li>\n<li>Memory usage<\/li>\n<\/ul>\n<\/li>\n<li>explain different data access patterns<\/li>\n<li>identify suboptimal data access operators<\/li>\n<li>recognize suboptimal Joins, Sorts and Aggregates<\/li>\n<li>explain insert \/ update operations <\/li>\n<li>recognize spool operators and their impacts on performance<\/li>\n<li>describe parallelism exchange operators<\/li>\n<li>recognize parallelism-preventing operations<\/li>\n<li>identify memory-consuming queries<\/li>\n<\/ul>\n<h3>Intended Audience<\/h3>\n<p>\nThis intensive class is designed for SQL Server professionals that write or maintain T-SQL code.\n<\/p>\n<h3>Course Prerequisites<\/h3>\n<p>\nTo get the most out of this class, students should have at least one year of solid working experience in writing queries and stored procedures for SQL Server.\n<\/p>\n<h3>Course Topics<\/h3>\n<h6>Day One<\/h6>\n<ol>\n<li>Query Execution Phases\n<ul>\n<li>Parse<\/li>\n<li>Bind<\/li>\n<li>Optimize<\/li>\n<li>Execute<\/li>\n<li>Summary<\/li>\n<\/ul>\n<\/li>\n<li>Execution plans\n<ul>\n<li>RelOps Interface <\/li>\n<li>RelOps Properties <\/li>\n<li>Execution plan forms   <\/li>\n<li>Execution plan sources<\/li>\n<li>Query cost<\/li>\n<li>Runtime information<\/li>\n<\/ul>\n<\/li>\n<li>Data Access Operations\n<ul>\n<li>Indexes<\/li>\n<li>Table Scan<\/li>\n<li>Index Scan<\/li>\n<li>Index Seek<\/li>\n<li>Key Lookup<\/li>\n<li>Constant Scan<\/li>\n<\/ul>\n<\/li>\n<li>Joins\n<ul>\n<li>Inner, outer, semi, apply<\/li>\n<li>Nested Loops<\/li>\n<li>Hash<\/li>\n<li>Merge<\/li>\n<li>Apply <\/li>\n<\/ul>\n<\/li>\n<\/ol>\n<h6>Day Two<\/h6>\n<ol>\n<li>Calculations and Functions\n<ul>\n<li>Constants (Row & Columns)<\/li>\n<li>Calculations<\/li>\n<li>Function Calls<\/li>\n<li>Filters<\/li>\n<\/ul>\n<\/li>\n<li>TOP<\/li>\n<li>Sorts\n<ul>\n<li>Operators and Algorithms<\/li>\n<li>TOP(n) Sort<\/li>\n<li>Distinct<\/li>\n<\/ul>\n<\/li>\n<li>Unions\n<ul>\n<li>Hash<\/li>\n<li>Concatenation<\/li>\n<li>Merge Join (Concatenation)\t<\/li>\n<\/ul>\n<\/li>\n<li>Aggregates<\/li>\n<li>Data Change Queries<\/li>\n<li>Spools\n<ul>\n<li>Table Spool<\/li>\n<li>Index Spool<\/li>\n<li>Row Count Spool<\/li>\n<\/ul>\n<\/li>\n<li>Parallelism<\/li>\n<li>Blocking and memory-consuming queries<\/li>\n<\/ol>\n<h3>\nHow to attend<br \/>\n<\/h3>\n<p>\nIf you would like us to teach it in your neighborhood, <a href=\"http:\/\/sqlity.net\/en\/contact-us\/\">contact us now<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query. <a href=\"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/\">[more&#8230;]<\/a><\/p>\n","protected":false},"author":3,"featured_media":0,"parent":6,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":{"jetpack_post_was_ever_published":false,"footnotes":""},"class_list":["post-1040","page","type-page","status-publish","hentry"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Server \u2013 Query Execution Internals - sqlity.net<\/title>\n<meta name=\"description\" content=\"This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server \u2013 Query Execution Internals - sqlity.net\" \/>\n<meta property=\"og:description\" content=\"This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/\" \/>\n<meta property=\"og:site_name\" content=\"sqlity.net\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/sqlity.net\" \/>\n<meta property=\"article:modified_time\" content=\"2014-11-04T20:19:53+00:00\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:site\" content=\"@sqlity\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/training\\\/sql-server-query-execution-internals\\\/\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/training\\\/sql-server-query-execution-internals\\\/\",\"name\":\"SQL Server \u2013 Query Execution Internals - sqlity.net\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\"},\"datePublished\":\"2012-07-26T19:27:55+00:00\",\"dateModified\":\"2014-11-04T20:19:53+00:00\",\"description\":\"This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/training\\\/sql-server-query-execution-internals\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/sqlity.net\\\/en\\\/training\\\/sql-server-query-execution-internals\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/training\\\/sql-server-query-execution-internals\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Training\",\"item\":\"https:\\\/\\\/sqlity.net\\\/en\\\/training\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"SQL Server \u2013 Query Execution Internals\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/sqlity.net\\\/en\\\/#website\",\"url\":\"https:\\\/\\\/sqlity.net\\\/en\\\/\",\"name\":\"sqlity.net\",\"description\":\"Quality for SQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/sqlity.net\\\/en\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server \u2013 Query Execution Internals - sqlity.net","description":"This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query.","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:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server \u2013 Query Execution Internals - sqlity.net","og_description":"This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query.","og_url":"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/","og_site_name":"sqlity.net","article_publisher":"https:\/\/www.facebook.com\/sqlity.net","article_modified_time":"2014-11-04T20:19:53+00:00","twitter_card":"summary_large_image","twitter_site":"@sqlity","twitter_misc":{"Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/","url":"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/","name":"SQL Server \u2013 Query Execution Internals - sqlity.net","isPartOf":{"@id":"https:\/\/sqlity.net\/en\/#website"},"datePublished":"2012-07-26T19:27:55+00:00","dateModified":"2014-11-04T20:19:53+00:00","description":"This is an advanced two-day class designed for SQL Server Professionals. It covers the lifecycle of SQL Server Execution Plans \u2013 how they are created and how SQL Server uses them to execute a query.","breadcrumb":{"@id":"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/sqlity.net\/en\/training\/sql-server-query-execution-internals\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/sqlity.net\/en\/"},{"@type":"ListItem","position":2,"name":"Training","item":"https:\/\/sqlity.net\/en\/training\/"},{"@type":"ListItem","position":3,"name":"SQL Server \u2013 Query Execution Internals"}]},{"@type":"WebSite","@id":"https:\/\/sqlity.net\/en\/#website","url":"https:\/\/sqlity.net\/en\/","name":"sqlity.net","description":"Quality for SQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/sqlity.net\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/P2wXuw-gM","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/pages\/1040","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/comments?post=1040"}],"version-history":[{"count":0,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/pages\/1040\/revisions"}],"up":[{"embeddable":true,"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/pages\/6"}],"wp:attachment":[{"href":"https:\/\/sqlity.net\/en\/wp-json\/wp\/v2\/media?parent=1040"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}