{"id":638,"date":"2023-11-21T23:34:36","date_gmt":"2023-11-21T23:34:36","guid":{"rendered":"https:\/\/test.samplewebsitelink.com\/ordba\/?p=638"},"modified":"2023-11-21T23:35:39","modified_gmt":"2023-11-21T23:35:39","slug":"query-processing-architecture-guide","status":"publish","type":"post","link":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/","title":{"rendered":"Query Processing Architecture Guide"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\"><strong>Query Processing Architecture Guide<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>02\/21\/2020<\/li>\n\n\n\n<li>89 minutes to read<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Applies to:<\/strong>&nbsp;SQL Server (all supported versions)&nbsp;Azure SQL Database<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. The following topics cover how SQL Server processes queries and optimizes query reuse through execution plan caching.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/ordba.net\/assets\/images\/topic_image\/topic_image\/query-processing-architecture-img.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Execution modes<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The SQL Server Database Engine can process Transact-SQL statements using two distinct processing modes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Row mode execution<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Batch mode execution<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Row mode execution<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Row mode execution<\/em>&nbsp;is a query processing method used with traditional RDMBS tables, where data is stored in row format. When a query is executed and accesses data in row store tables, the execution tree operators and child operators read each required row, across all the columns specified in the table schema. From each row that is read, SQL Server then retrieves the columns that are required for the result set, as referenced by a SELECT statement, JOIN predicate, or filter predicate.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Note<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Row mode execution is very efficient for OLTP scenarios, but can be less efficient when scanning large amounts of data, for example in Data Warehousing scenarios.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Batch mode execution<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><em>Batch mode execution<\/em>&nbsp;is a query processing method used to process multiple rows together (hence the term batch). Each column within a batch is stored as a vector in a separate area of memory, so batch mode processing is vector-based. Batch mode processing also uses algorithms that are optimized for the multi-core CPUs and increased memory throughput that are found on modern hardware.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Batch mode execution is closely integrated with, and optimized around, the columnstore storage format. Batch mode processing operates on compressed data when possible, and eliminates the exchange operator used by row mode execution. The result is better parallelism and faster performance.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When a query is executed in batch mode, and accesses data in columnstore indexes, the execution tree operators and child operators read multiple rows together in column segments. SQL Server reads only the columns required for the result, as referenced by a SELECT statement, JOIN predicate, or filter predicate.<br>For more information on columnstore indexes, see Columnstore Index Architecture.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Note<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Batch mode execution is very efficient Data Warehousing scenarios, where large amounts of data are read and aggregated.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>SQL Statement Processing<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Processing a single Transact-SQL statement is the most basic way that SQL Server executes Transact-SQL statements. The steps used to process a single&nbsp;SELECT&nbsp;statement that references only local base tables (no views or remote tables) illustrates the basic process.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Logical Operator Precedence<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When more than one logical operator is used in a statement,&nbsp;NOT&nbsp;is evaluated first, then&nbsp;AND, and finally&nbsp;OR. Arithmetic, and bitwise, operators are handled before logical operators. For more information, see&nbsp;<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/language-elements\/operator-precedence-transact-sql?view=sql-server-ver15\">Operator Precedence<\/a>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">In the following example, the color condition pertains to product model 21, and not to product model 20, because&nbsp;AND&nbsp;has precedence over&nbsp;OR.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQLCopy<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SELECT ProductID, ProductModelID<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">FROM Production.Product<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">WHERE ProductModelID = 20 OR ProductModelID = 21<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&nbsp; AND Color = &#8216;Red&#8217;;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">GO<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You can change the meaning of the query by adding parentheses to force evaluation of the&nbsp;OR&nbsp;first. The following query finds only products under models 20 and 21 that are red.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQLCopy<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SELECT ProductID, ProductModelID<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">FROM Production.Product<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">WHERE (ProductModelID = 20 OR ProductModelID = 21)<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&nbsp; AND Color = &#8216;Red&#8217;;<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">GO<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Using parentheses, even when they are not required, can improve the readability of queries, and reduce the chance of making a subtle mistake because of operator precedence. There is no significant performance penalty in using parentheses. The following example is more readable than the original example, although they are syntactically the same.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SQLCopy<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">SELECT ProductID, ProductModelID<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">FROM Production.Product<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">WHERE ProductModelID = 20 OR (ProductModelID = 21<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">&nbsp; AND Color = &#8216;Red&#8217;);<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">GO<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong><a href=\"https:\/\/ordba.net\/pdf\/query-processing-architecture-guide.pdf\" target=\"_blank\" rel=\"noreferrer noopener\">Know More (Link to PDF)<\/a><\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Query Processing Architecture Guide Applies to:&nbsp;SQL Server (all supported versions)&nbsp;Azure SQL Database The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. The following topics cover how SQL Server processes queries and optimizes query reuse through execution plan caching. Execution modes &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/\"> <span class=\"screen-reader-text\">Query Processing Architecture Guide<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":639,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"","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":"","ast-page-background-enabled":"default","ast-page-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-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-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-gradient":""}},"ast-content-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-gradient":""},"tablet":{"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-gradient":""},"mobile":{"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-gradient":""}},"footnotes":""},"categories":[11],"tags":[],"class_list":["post-638","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v21.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Query Processing Architecture Guide - Ordba<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Query Processing Architecture Guide - Ordba\" \/>\n<meta property=\"og:description\" content=\"Query Processing Architecture Guide Applies to:&nbsp;SQL Server (all supported versions)&nbsp;Azure SQL Database The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. The following topics cover how SQL Server processes queries and optimizes query reuse through execution plan caching. Execution modes &hellip; Query Processing Architecture Guide Read More &raquo;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/\" \/>\n<meta property=\"og:site_name\" content=\"Ordba\" \/>\n<meta property=\"article:published_time\" content=\"2023-11-21T23:34:36+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-11-21T23:35:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/test.samplewebsitelink.com\/ordba\/wp-content\/uploads\/2023\/11\/query-processing-architecture-img.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"820\" \/>\n\t<meta property=\"og:image:height\" content=\"446\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"ordba\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ordba\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/\",\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/\",\"name\":\"Query Processing Architecture Guide - Ordba\",\"isPartOf\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#website\"},\"datePublished\":\"2023-11-21T23:34:36+00:00\",\"dateModified\":\"2023-11-21T23:35:39+00:00\",\"author\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f\"},\"breadcrumb\":{\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/test.samplewebsitelink.com\/ordba\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Query Processing Architecture Guide\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#website\",\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/\",\"name\":\"Ordba\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/test.samplewebsitelink.com\/ordba\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f\",\"name\":\"ordba\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g\",\"caption\":\"ordba\"},\"sameAs\":[\"https:\/\/test.samplewebsitelink.com\/ordba\"],\"url\":\"https:\/\/test.samplewebsitelink.com\/ordba\/author\/ordba\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Query Processing Architecture Guide - Ordba","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:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/","og_locale":"en_US","og_type":"article","og_title":"Query Processing Architecture Guide - Ordba","og_description":"Query Processing Architecture Guide Applies to:&nbsp;SQL Server (all supported versions)&nbsp;Azure SQL Database The SQL Server Database Engine processes queries on various data storage architectures such as local tables, partitioned tables, and tables distributed across multiple servers. The following topics cover how SQL Server processes queries and optimizes query reuse through execution plan caching. Execution modes &hellip; Query Processing Architecture Guide Read More &raquo;","og_url":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/","og_site_name":"Ordba","article_published_time":"2023-11-21T23:34:36+00:00","article_modified_time":"2023-11-21T23:35:39+00:00","og_image":[{"width":820,"height":446,"url":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-content\/uploads\/2023\/11\/query-processing-architecture-img.jpg","type":"image\/jpeg"}],"author":"ordba","twitter_card":"summary_large_image","twitter_misc":{"Written by":"ordba","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/","url":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/","name":"Query Processing Architecture Guide - Ordba","isPartOf":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#website"},"datePublished":"2023-11-21T23:34:36+00:00","dateModified":"2023-11-21T23:35:39+00:00","author":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f"},"breadcrumb":{"@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/2023\/11\/21\/query-processing-architecture-guide\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/test.samplewebsitelink.com\/ordba\/"},{"@type":"ListItem","position":2,"name":"Query Processing Architecture Guide"}]},{"@type":"WebSite","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#website","url":"https:\/\/test.samplewebsitelink.com\/ordba\/","name":"Ordba","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/test.samplewebsitelink.com\/ordba\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/7365beabb32b7aa77cf0e890ee90ea0f","name":"ordba","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/test.samplewebsitelink.com\/ordba\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/dd4335bb4e225c36ce7c56a0aff84bb202ebb523ebdc5d1e1a11cc3f3e6085f3?s=96&d=mm&r=g","caption":"ordba"},"sameAs":["https:\/\/test.samplewebsitelink.com\/ordba"],"url":"https:\/\/test.samplewebsitelink.com\/ordba\/author\/ordba\/"}]}},"_links":{"self":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/638","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/comments?post=638"}],"version-history":[{"count":2,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/638\/revisions"}],"predecessor-version":[{"id":641,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/posts\/638\/revisions\/641"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/media\/639"}],"wp:attachment":[{"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/media?parent=638"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/categories?post=638"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/test.samplewebsitelink.com\/ordba\/wp-json\/wp\/v2\/tags?post=638"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}