{"id":488,"date":"2008-12-16T00:00:00","date_gmt":"2008-12-16T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/graphical-execution-plans-for-simple-sql-queries\/"},"modified":"2021-06-03T16:44:21","modified_gmt":"2021-06-03T16:44:21","slug":"graphical-execution-plans-for-simple-sql-queries","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/performance-sql-server\/graphical-execution-plans-for-simple-sql-queries\/","title":{"rendered":"Graphical Execution Plans for Simple SQL Queries"},"content":{"rendered":"<div id=\"pretty\">\n<p>The aim of this article is to enable you to interpret <i>basic<\/i> graphical execution plans, in other words, execution plans for simple <b>SELECT<\/b>, <b>UPDATE<\/b>, <b>INSERT<\/b> or <b>DELETE<\/b> queries, with only a few joins and no advanced functions or hints. In order to do this, we&#8217;ll cover the following graphical execution plan topics:<\/p>\n<ul>\n<li><b>Operators<\/b> &#8211; introduced in the last article, now you&#8217;ll see more  <\/li>\n<li><b>Joins<\/b> &#8211; what&#8217;s a relational system without the joins between tables  <\/li>\n<li><b>WHERE<\/b> clause &#8211; you need to filter your data and it does affect the execution plans  <\/li>\n<li><b>Aggregate<\/b><b>s<\/b> &#8211; how grouping data changes execution plans  <\/li>\n<li>Insert &#160;<b>Update<\/b> and <b>Delete<\/b> execution plans <\/li>\n<\/ul>\n<h2>The Language of Graphical Execution Plans&#160; <\/h2>\n<p>In some ways, learning how to read graphical execution plans is similar to learning a new language, except that the language is icon-based, and the number of words (icons) we have to learn is minimal. Each icon represents a specific operator within the execution plan. We will be using the terms &#8216;icon&#8217; and &#8216;operator&#8217; interchangeably in this article.<\/p>\n<p>In the previous article, we only saw two operators (<b>Select<\/b> and <b>Table Scan<\/b><b> <\/b><b><\/b>). However, there are a total of 79 operators available. Fortunately for us, we don&#8217;t have to memorize all 79 of them before we can read a graphical execution plan. Most queries use only a small subset of the icons, and those are the ones we are going to focus on in this article. If you run across an icon not covered here, you can find out more information about it on Books Online:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">http:\/\/msdn2.microsoft.com\/en-us\/library\/ms175913.aspx\n<\/pre>\n<p>Four distinct types of operator are displayed in a graphical execution plan:<\/p>\n<ul>\n<li><b>Logical and physical operators<\/b><b>,<\/b> also called iterators, are displayed as blue icons and represent query execution or Data Manipulation Language (DML) statements.  <\/li>\n<li><b>Parallelism physical operators<\/b> are also blue icons and are used to represent parallelism operations. In a sense, they are a subset of logical and physical operators, but are considered separate because they entail an entirely different level of execution plan analysis.  <\/li>\n<li><b>Cursor operators<\/b><b> <\/b>have yellow icons and represent Transact-SQL cursor operations  <\/li>\n<li><b>Language elements<\/b> are green icons and represent Transact-SQL language elements, such as Assign, Declare, If, Select (Result), While, and so on. <\/li>\n<\/ul>\n<p>In this article we&#8217;ll focus mostly on logical and physical operators, including the parallelism phys&#173;ical operators. Books Online lists them in alphabetical order, but this is not the easiest way to learn them, so we will forgo being &#8220;alphabetically correct&#8221; here. Instead, we will focus on the most-used icons. Of course, what is considered most-used and least-used will vary from DBA to DBA, but the following are what I would consider the more common oper&#173;ators, listed from left-to-right and top-to-bottom, roughly in the order of most common to least common:<\/p>\n<table class=\"MsoNormalTable\" id=\"table1\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>Select (Result)<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Sort <\/b><b>&#160;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Clustered Index Seek <\/b><b><\/b><b>&#160;<\/b><b> <\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Clustered Index Scan <\/b><b><\/b><b>&#160;<\/b><b> <\/b><b>&#160;<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Non-clustered Index Scan <\/b><b><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Non-clustered Index Seek <\/b><b><\/b><b>&#160;<\/b><b> <\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Table Scan <\/b><b><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>RID Lookup<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Key Lookup<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Hash Match <\/b><b><\/b><b>&#160;<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Nested Loops <\/b><b><\/b><b>&#160;<\/b><b> <\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Merge Join <\/b><b><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Top<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Compute Scalar <\/b><b><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Constant Scan <\/b><b><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Filter<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Lazy Spool<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Spool<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Eager Spool <\/b><b><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Stream Aggregate <\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Distribute Streams&#160; <\/p>\n<\/td>\n<td valign=\"top\">\n<p>Repartition Streams <\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gather Streams&#160; <\/p>\n<\/td>\n<td valign=\"top\">\n<p>Bitmap<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Split<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Those picked out in bold are covered in this article. The rest will be covered in the book.<\/p>\n<p>Operators have behavior that is worth understanding. Some operators &#8211; primarily <b>sort<\/b>, <b>hash match (aggregate)<\/b> and <b>hash join<\/b><b> <\/b><b><\/b>&#160;- require a variable amount of memory in order to execute. Because of this, a query with one of these operators may have to wait for available memory prior to execution, possibly adversely affecting performance. Most operators behave in one of two ways, non-blocking or blocking. A non-blocking operator creates output data at the same time as it receives the input. A blocking operator has to get all the data prior to producing its output. A blocking operator might contribute to concurrency problems, hurting performance. <\/p>\n<h2>Some Single table Queries<\/h2>\n<p>Let&#8217;s start by looking at some very simple plans, based on single table queries.<\/p>\n<h3>Clustered Index Scan&#160; &#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"37\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-first.png\" width=\"42\" alt=\"618-first.png\" \/><\/p>\n<p>Consider the following simple (but inefficient!) query against the <b>Person.Contact<\/b> table in the AdventureWorks database:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT *\nFROM&#160;&#160;&#160; Person.Contact\n<\/pre>\n<p>Following is the actual execution plan:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"438\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-second.png\" width=\"533\" alt=\"618-second.png\" \/><\/p>\n<p class=\"caption\">Figure 1<\/p>\n<p>We can see that a clustered index scan operation is performed to retrieve the required data. If you place the mouse pointer over the Clustered Index Scan&#160;&#160;icon, to bring up the ToolTip window, you will see that the clustered index used was <b>PK_Contact_ContactID<\/b> and that the estimated number of rows involved in the operation was 19972. <\/p>\n<p>Indexes in SQL Server are stored in a B-tree (a series of nodes that point to a parent). A clustered index not only stores the key structure, like a regular index, but also sorts and stores the data, which is the main reason why there can be only one clustered index per table.<\/p>\n<p>As such, a clustered index <b>scan<\/b> is almost the same in concept as a table scan. The entire index, or a large percentage of it, is being traversed, row-by-row, in order to identify the data needed by the query.<\/p>\n<p>An index scan often occurs, as in this case, when an index exists but the optimizer determines that so many rows need to be returned that it is quicker to simply scan all the values in the index rather than use the keys provided by that index.<\/p>\n<p>An obvious question to ask if you see an index scan in your execution plan is whether you are returning more rows than is necessary. If the number of rows returned is higher than you expect, that&#8217;s a strong indication that you need to fine-tune the <b>WHERE<\/b> clause of your query so that only those rows that are actually needed are returned. Returning unnecessary rows wastes SQL Server resources and hurts overall performance. <\/p>\n<h3>Clustered Index Seek&#160; &#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"32\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-third.png\" width=\"32\" alt=\"618-third.png\" \/><\/p>\n<p>We can easily make the previous query more efficient by adding a <b>WHERE<\/b> clause:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT *\nFROM&#160;&#160;&#160;&#160;Person.Contact &#160;\n<\/pre>\n<p>The plan now looks as shown in figure 2:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"540\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-fourth.png\" width=\"553\" alt=\"618-fourth.png\" \/><\/p>\n<p class=\"caption\">Figure 2<\/p>\n<p>Index seeks are completely different from scans, where the engine walks through the rows to find what it needs. An index seek, clustered or not, occurs when the optimizer is able to locate an index that it can use to retrieve the required records. Therefore, it tells the storage engine to look up the values based on the keys of the given index. Indexes in SQL Server are stored in a B-tree (a series of nodes that point to a parent). A <b>clustered index<\/b> stores not just the key structure, like a regular index, but also sorts and stores the data, which is the main reason why there can be only one clustered index per table.<\/p>\n<p>When an index is used in a seek operation, the key values are used to quickly identify the row, or rows, of data needed. This is similar to looking up a word in the index of a book to get the correct page number. The added value of the clustered index seek is that, not only is the index seek an inexpensive operation as compared to an index scan, but no extra steps are required to get the data because it is stored in the index.<\/p>\n<p>In the above example, we have a <b>Clustered Index Seek<\/b> operation carried out against the <b>Person.Contact<\/b> table, specifically on the <b>PK_Contact_ContactId<\/b>, which is happens to be both the primary key and the clustered index for this table.<\/p>\n<p>Note on the ToolTips window for the Clustered Index Seek that the <b>Ordered<\/b> property is now true, indicating that the data was ordered by the optimizer. <\/p>\n<h3>Non-clustered Index Seek&#160; &#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"45\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-fifth.png\" width=\"38\" alt=\"618-fifth.png\" \/><\/p>\n<p>Let&#8217;s run a slightly different query against the <b>Person.Contact<\/b> table; one that uses a non-clustered index:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; ContactID\nFROM&#160;&#160;&#160; Person.Contact\nWHERE&#160;&#160; EmailAddress LIKE 'sab%'\n<\/pre>\n<p>We get a non-clustered index seek. Notice in the ToolTip shown in figure 3 that the non-clustered index, <b>IX_Contact_EmailAddress<\/b> has been used.<\/p>\n<p><b>NOTE<\/b>: The non-clustered Index Seek icon is misnamed and called an Index Seek in the execution plan below. Apparently, this was a mistake by Microsoft and hopefully will be fixed at some point. No big deal, but something for you to be aware of.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"610\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-Sixth.png\" width=\"534\" alt=\"618-Sixth.png\" \/><\/p>\n<p class=\"caption\">Figure 3<\/p>\n<p>Like a clustered index seek, a non-clustered index seek uses an index to look up the rows to be returned directly. Unlike a clustered index seek, a non-clustered index seek has to use a non-clustered index to perform the operation. Depending on the query and index, the query optimizer might be able to find all the data in the non-clustered index, or it might have to look up the data in the clustered index, slightly hurting performance due to the additional I\/O required to perform the extra lookups &#8211; more on this in the next section.<\/p>\n<h3>Key LookUp &#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"51\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-Seventh.png\" width=\"42\" alt=\"618-Seventh.png\" \/><\/p>\n<p>Let&#8217;s take our query from the previous sections and alter it so that it returns just a few more columns:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; ContactID,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; LastName,\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; Phone\nFROM&#160;&#160;&#160; Person.Contact\nWHERE&#160;&#160; EmailAddress LIKE 'sab%'\n<\/pre>\n<p>You should see a plan like that shown in figure 4:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"156\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-eighth.png\" width=\"533\" alt=\"618-eighth.png\" \/><\/p>\n<p class=\"caption\">Figure 4<\/p>\n<p>Finally, we get to see a plan that involves more than a single operation! Reading the plan from right-to-left and top-to-bottom, the first operation we see is an Index Seek against the <b>IX_Contact_EmailAddress<\/b> index. This is a non-unique, non-clustered index and, in the case of this query, it is <i>non-covering<\/i>. A non-covering index is an index that does not contain all of the columns that need to be returned by a query, forcing the query optimizer to not only read the index, but to also read the clustered index to gather all the data it needs so it can be returned.<\/p>\n<p>We can see this in the ToolTips window from the <i>Output List<\/i> for the Index Seek, in figure 5, which shows the <b>EmailAddress<\/b> and <b>ContactID<\/b> columns.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"652\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ninth.png\" width=\"407\" alt=\"618-ninth.png\" \/><\/p>\n<p class=\"caption\">Figure 5<\/p>\n<p>The key values are then used in a <b>Key Lookup<\/b> on the <b>PK_Contact_ContactID<\/b> clustered index to find the corresponding rows, with the output list being the <b>LastName<\/b> and <b>Phone<\/b> columns, as shown in figure 6.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"537\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-tenth.png\" width=\"424\" alt=\"618-tenth.png\" \/><\/p>\n<p class=\"Illustration\">&#160;<\/p>\n<p class=\"caption\">Figure 6<\/p>\n<p>A Key Lookup is a bookmark lookup on a table with a clustered index. (Pre-SP2, this operation would have been represented with a Clustered Index scan, with a LookUp value of True) . <\/p>\n<p>A Key Lookup essentially means that the optimizer cannot retrieve the rows in a single operation, and has to use a clustered key (or a row ID) to return the corresponding rows from a clustered index (or from the table itself).<\/p>\n<p>The presence of a Key Lookup is an indication that query performance might benefit from the presence of a covering or included index. Both a covering or included index include all of the columns that need to be returned by a query, so all the columns of each row are found in the index, and a Key Lookup does not have to occur in order to get all the columns that need to be returned.<\/p>\n<p>A Key LookUp is always accompanied by the Nested Loop join operation that combines the results of the two operations.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"458\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-eleventh.png\" width=\"346\" alt=\"618-eleventh.png\" \/><\/p>\n<p class=\"caption\">Figure 7<\/p>\n<p>Typically, a Nested Loops join is a standard type of join and by itself does not indicate any performance issues. In this case, because a Key Lookup operation is required, the Nested Loops join is needed to combine the rows of the Index Seek and Key Lookup. If the Key Lookup was not needed (because a covering index was available), then the Nested Loops operator would not be needed and would not appear in the graphical execution plan.<\/p>\n<h3>Table Scan&#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"36\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-FortySixth.png\" width=\"40\" alt=\"618-FortySixth.png\" \/><\/p>\n<p>This operator is fairly self-explanatory and is one we previously encountered in Article 1. It indicates that the required rows were returned by scanning the table, one row after another. You can see a table scan operation by executing the following query:<\/p>\n<p>SELECT&#160; *<\/p>\n<p>FROM&#160;&#160;&#160; [dbo].[DatabaseLog]<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"592\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-FortyFifth.png\" width=\"523\" alt=\"618-FortyFifth.png\" \/><\/p>\n<p class=\"caption\">Figure 8<\/p>\n<p>A table scan can occur for several reasons, but it&#8217;s often because there are no useful indexes on the table, and the query optimizer has to search through every row in order to identify the rows to return. Another common reason why a table scan may occur is when all the rows of a table are returned, as is the case in this example. When all (or the majority) of the rows of a table are returned then, whether an index exists or not, it is often faster for the query optimizer to scan through each row and return them than look up each row in an index. And last, sometimes the query optimizer determines that it is faster to scan each row than it is to use an index to return the rows. This commonly occurs in tables with few rows.<\/p>\n<p>Assuming that the number of rows in a table is relatively small, table scans are generally not a problem. On the other hand, if the table is large and many rows are returned, then you might want to investigate ways to rewrite the query to return fewer rows, or add an appropriate index to speed performance.<\/p>\n<h3>RID LookUp&#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"36\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-FortyFourth.png\" width=\"44\" alt=\"618-FortyFourth.png\" \/><\/p>\n<p>If we specifically filter the results of our previous <b>DatabaseLog<\/b> query using the primary key column, we see a different plan that uses a combination of an Index Seek&#160; &#160;and a <b>RID LookUp<\/b><b> <\/b><b><\/b>.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160;&#160;*\nFROM&#160;&#160;&#160;&#160;[dbo].[DatabaseLog]\nWHERE&#160;&#160; DatabaseLogID = 1\n<\/pre>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"220\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-FortyThird.png\" width=\"521\" alt=\"618-FortyThird.png\" \/><\/p>\n<p class=\"caption\">Figure 9<\/p>\n<p>To return the results for this query, the query optimizer first performs an Index Seek on the primary key. While this index is useful in identifying the rows that meet the <b>WHERE<\/b> clause criteria, all the required data columns are not present in the index. How do we know this? <\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"568\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-FortySecond.png\" width=\"401\" alt=\"618-FortySecond.png\" \/><\/p>\n<p class=\"caption\">Figure 10<\/p>\n<p>If you look at the ToolTip above for the Index Seek, we see &#8220;Bmk1000&#8243; is in the Output list. This&#8221;Bmk1000&#8221; is telling us that this Index Seek is actually part of a query plan that has a bookmark lookup.<\/p>\n<p>Next, the query optimizer performs a RID LookUp, which is a type of bookmark lookup that occurs on a heaptable (a table that doesn&#8217;t have a clustered index), and uses a row identifier to find the rows to return. In other words, since the table doesn&#8217;t have a clustered index (that includes all the rows), it must use a row identifier that links the index to the heap. This adds additional disk I\/O because two different operations have to be performed instead of a single operation, which are then combined with a Nested Loops operation.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"593\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-Fortyfirst.png\" width=\"438\" alt=\"618-Fortyfirst.png\" \/><\/p>\n<p class=\"caption\">Figure 11<\/p>\n<p>In the above ToolTip for the RID Lookup, notice that &#8220;Bmk1000&#8221; is used again, but this time in the Seek Predicates section. This is telling us that a bookmark lookup (specifically a RID Lookup in our case) was used as part of the query plan. In this particular case, only one row had to be looked up, which isn&#8217;t a big deal from a performance perspective. But if a RID Lookup returns many rows, you should consider taking a close look at the query to see how you can make it perform better by using less disk I\/O &#8211; perhaps by rewriting the query, by adding a clustered index, or by using a covering or included index.<\/p>\n<h2>Table Joins&#160; <\/h2>\n<p>Up to now, we have worked with single tables. Let&#8217;s spice things up a bit and introduce joins into our query. The following query retrieves employee information, concatenating the <b>FirstName<\/b> and <b>LastName<\/b> columns in order to return the information in a more pleasing manner.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; e.[Title],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; a.[City],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; c.[LastName] + ', ' + c.[FirstName] AS EmployeeName\nFROM&#160;&#160;&#160; [HumanResources].[Employee] e\nJOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID]&#160;&#160; = ed.[EmployeeID]\nJOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID]\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID];\n<\/pre>\n<p>The execution plan for this query is shown in figure 12.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"198\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-fortieth.jpeg\" width=\"537\" alt=\"618-fortieth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 12<\/p>\n<p>With this query there are multiple processing steps occurring, with varying costs to the processor. The cost accumulates as you move thorough the execution tree from right to left.<\/p>\n<p>From the relative cost displayed below each operator icon, we can identify the three most costly operations in the plan, in descending order: <\/p>\n<ol>\n<li>\n<p class=\"numberedList\">&#160;The Index Scan against the <b>Person.Address<\/b> table (45%)<\/p>\n<\/li>\n<li>\n<p class=\"numberedList\">The Hash Match join operation between the <b>HumanResources.EmployeeAddress<\/b> and <b>Person.Address<\/b> (28%)<\/p>\n<\/li>\n<li>\n<p class=\"numberedList\">The Clustered Index Seek on the <b>Person.Contact<\/b> table (18%)<\/p>\n<\/li>\n<\/ol>\n<p>Let&#8217;s consider each of the operators we see in this plan.<\/p>\n<p>Starting on the right of Figure 12 above, the first thing we see is an Index Scan against the <b>HumanResources.EmployeeAddress<\/b> table, and directly below this is another index scan against the <b>Person.Address<\/b> table. The latter was the most expensive operation in the plan, so let&#8217;s investigate further. By bringing up the ToolTip, shown in Figure 13, we can see that the scan was against the index <b>IX_Address_AddressLine_&#173;AddressLine2_&#173;City_&#173;StateProvinceId_&#173;PostalCode<\/b> and that the storage engine had to walk through 19,614 rows to arrive at the data that we needed.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"367\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtyNinth.jpeg\" width=\"274\" alt=\"618-ThirtyNinth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 13<\/p>\n<p>The query optimizer needed to get at the <b>AddressId<\/b> and the <b>City<\/b> columns, as shown by the output list. The optimizer calculated, based on the selectivity of the indexes and columns in the table, that the best way to arrive at that data was to walk though the index. Walking through those 19,614 rows took 45% of the total query cost or an estimated operator cost of 0.180413. The estimated operator cost is the cost to the query optimizer for executing this specific operation, which is an internally calculated number used by the query optimizer to evaluate the relative costs of specific operations. The lower this number, the more efficient the operation.<\/p>\n<h3>Hash Match&#160; &#160;(Join)&#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"39\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-twelvth.png\" width=\"49\" alt=\"618-twelvth.png\" \/><\/p>\n<p>Continuing with the above example, the output of the two index scans is combined through a <b>Hash Match<\/b><b> <\/b><b><\/b><b>&#160;join <\/b><b>&#160;<\/b>, the second most expensive operation of this execution plan. The ToolTip for this operator is shown in Figure 14:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"366\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-thirteenth.jpeg\" width=\"274\" alt=\"618-thirteenth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 14<\/p>\n<p>Before we can talk about what a Hash Match join is, we need to understand two new concepts: <b>hashing<\/b>&#160;and <b>hash table<\/b><b> <\/b><b><\/b>. Hashing is a programmatic technique where data is converted into a symbolic form that makes it easier to be searched for quickly. For example, a row of data in a table can be programmatically converted into a unique value that represents the contents of the row. In many ways it is like taking a row of data and encrypting it. Like encryption, a hashed value can be converted back to the original data. Hashing is often used within SQL Server to convert data into a form that is more efficient to work with, or in this case, to make searching more efficient.<\/p>\n<p>A hash table, on the other hand, is a data structure that divides all of the elements into equal-sized categories, or buckets, to allow quick access to the elements. The hashing&#160;function determines which bucket an element goes into. For example, you can take a row from a table, hash it into a hash value, then store the hash value into a hash table.<\/p>\n<p>Now that we understand these terms, a <b>Hash Match<\/b><b> <\/b><b><\/b>&#160;join occurs when SQL Server joins two tables by hashing&#160;the rows from the smaller of the two tables to be joined, and then inserting them into a hash table, then processing the larger table one row at a time against the smaller hashed table, looking for&#160; matches where rows need to be joined. Because the smaller of the tables provides the values in the hash table, the table size is kept at a minimum, and because hashed values instead of real values are used, comparisons can be made very quickly. As long as the table that is hashed is relatively small, this can be a quick process. On the other hand, if both tables are very large, a Hash Match join can be very inefficient as compared to other types of joins.<\/p>\n<p>In this example, the data from <b>HumanResources.EmployeeAddress.AddressId<\/b> is matched with <b>Person.Address<\/b> table.<\/p>\n<p>Hash Match joins are often very efficient with large data sets, especially if one of the tables is substantially smaller than the other. Hash Match joins also work well for tables that are not sorted on join columns, and they can be efficient in cases where there are no useable indexes. On the other hand, a Hash Match join might indicate that a more efficient join method (Nested Loops or Merge) could be used. For example, seeing a Hash Match join in an execution plan sometimes indicates:<\/p>\n<ul>\n<li>\n<p>a missing or incorrect index<\/p>\n<\/li>\n<li>\n<p>a missing <b>WHERE<\/b> clause<\/p>\n<\/li>\n<li>\n<p>a <b>WHERE<\/b> clause with a calculation or conversion that makes it non-sargeable (a commonly used term meaning that the search argument, &#8220;sarg&#8221; can&#8217;t be used). This means it won&#8217;t use an existing index.<\/p>\n<\/li>\n<\/ul>\n<p>While a Hash Match join may be the most efficient way for the query optimizer to join two tables, it does not mean there are not more efficient ways to join two tables, such as adding appropriate indexes to the joined tables, reducing the amount of data returned by adding a more restrictive WHERE clause, or by making the WHERE clause sargeble. In other words, a seeing a Hash Match join should be a cue for you to investigate if the join operation can be improved or not. If it can be improved, then great. If not, then there is nothing else to do, as the Hash Match join might be the best overall way to perform the join.<\/p>\n<p>Worth noting in this example is the slight discrepancy between the estimated number of rows returned, 282.216 (proving this is a calculation since you can&#8217;t possibly return .216 rows), and the actual number of rows, 290. A difference this small is not worth worrying about, but a larger discrepancy indicates that your statistics are out of date and need to be updated. A large difference can lead to differences in the Estimated and Actual plans.<\/p>\n<p>The query proceeds from here with another index scan against the <b>HumanResources&#173;.Employee<\/b> table and another Hash Match between the results of the first Hash Match and the index scan. <\/p>\n<h3>Clustered Index Seek&#160; &#160; <\/h3>\n<p>After the Hash Match&#160; &#160;Join, we see a <b>Clustered Index Seek<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b>&#160;operation carried out against the <b>Person.Contact<\/b> table, specifically on the <b>PK_Contact_ContactId<\/b>, which is both the primary key and clustered index for this table. This is the third most-expensive operation in the plan. The ToolTip is shown in Figure 15.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"425\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-fourteenth.jpeg\" width=\"298\" alt=\"618-fourteenth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure15<\/p>\n<p>Note from the <b>Seek Predicates<\/b><b> <\/b><b><\/b>&#160;section in figure 15 above, that the operation was joining directly between the <b>ContactId<\/b> column in the<b> HumanResources.Employee<\/b> table and the <b>Person.Contact<\/b> table.<\/p>\n<h3>Nested Loops Join&#160;&#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"37\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-fifteenth.png\" width=\"47\" alt=\"618-fifteenth.png\" \/><\/p>\n<p>Following the clustered index seek, the data accumulated by the other operations are joined with the data collected from the seek, through a <b>Nested Loop<\/b><b>s <\/b><b><\/b><b>&#160;Join&#160; <\/b><b><\/b><b>&#160; <\/b><b><\/b>, as shown in Figure 16.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"380\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-Sixteenth.jpeg\" width=\"275\" alt=\"618-Sixteenth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 16<\/p>\n<p>The <b>nested loops join<\/b> is also called a nested iteration. This operation takes the input from two sets of data and joins them by scanning the outer data set (the bottom operator in a graphical execution plan) once for each row in the inner set. The number of rows in each of the two data sets was small, making this a very efficient operation. As long as the inner data set is small and the outer data set, small or not, is indexed, this becomes an extremely efficient join mechanism. Unless you have very large data sets, this is the type of join that you most want to see in an execution plan.<\/p>\n<h3>Compute Scalar&#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"41\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtyEighth.png\" width=\"47\" alt=\"618-ThirtyEighth.png\" \/><\/p>\n<p>Finally, in the execution plan shown in figure 12, right before the Select operation, we have a Compute Scalar operation. The Tooltip for this operator is shown in Figure 19.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"265\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtySeventh.jpeg\" width=\"270\" alt=\"618-ThirtySeventh.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 19<\/p>\n<p>This is simply a representation of an operation to produce a scalar, a single defined value, usually from a calculation &#8211; in this case, the alias <b>EmployeeName<\/b> which combines the columns <b>Contact.LastName<\/b> and <b>Contact.FirstName<\/b> with a comma in between them. While this was not a zero-cost operation, 0.0000282, it&#8217;s so trivial in the context of the query as to be essentially free of cost.<\/p>\n<h3>Merge Join&#160; <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"35\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-Thirtysixth.png\" width=\"37\" alt=\"618-Thirtysixth.png\" \/><\/p>\n<p>Besides the Hash and Nested Loops Join&#160;, the query optimizer can also perform a <b>Merge Join<\/b><b> <\/b><b><\/b>. To seen an example of a Merge Join, we can run the following code in the AdventureWorks database:<\/p>\n<p>SELECT&#160; c.CustomerID<br \/>FROM&#160;&#160;&#160; Sales.SalesOrderDetail od<br \/>&#160;&#160;&#160;&#160;&#160;&#160;&#160; JOIN Sales.SalesOrderHeader oh <br \/>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ON od.SalesOrderID = oh.SalesOrderID<br \/>&#160;&#160;&#160;&#160;&#160;&#160;&#160; JOIN Sales.Customer c&#160;&#160;&#160;&#160; <br \/>&#160;&#160;&#160;&#160;&#160;&#160;&#160;ON oh.CustomerID = c.CustomerID<\/p>\n<p>The above query produces an execution plan that looks as shown in figure 17.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"220\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtyFifth.png\" width=\"553\" alt=\"618-ThirtyFifth.png\" \/><\/p>\n<p class=\"caption\">Figure 17<\/p>\n<p>According to the execution plan, the query optimizer performs a Clustered Index Scan on the <b>Customer<\/b> table and a non-clustered Index Scan&#160; &#160;on the <b>SalesOrderHeader<\/b> table. Since a <b>WHERE<\/b> clause was not specified in the query, a scan was performed on each table to return all the rows in each table.<\/p>\n<p>Next, all the rows from both the <b>Customer<\/b> and <b>SalesOrderHeader<\/b> tables are joined using the <b>Merge Join<\/b><b> <\/b><b><\/b>&#160;operator. A Merge Join occurs on tables where the join columns are presorted. For example, in the ToolTip window for the Merge Join, shown in figure 18, we see that the join columns are <b>Sales <\/b>and <b>CustomerID<\/b>. In this case, the data in the join columns are presorted in order. A Merge Join is an efficient way to join two tables, when the join columns are presorted but if the join columns are not presorted, the query optimizer has the option of a) sorting the join columns first, then performing a Merge Join, or b) performing a less efficient Hash Join. The query optimizer considers all the options and generally chooses the execution plan that uses the least resources.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"522\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtyFourth.png\" width=\"336\" alt=\"618-ThirtyFourth.png\" \/><\/p>\n<p><b>Figure 18<\/b><\/p>\n<p>Once the Merge Join has joined two of the tables, the third table is joined to the first two using a Hash Match Join, which was discussed earlier. And finally, the joined rows are returned.<\/p>\n<p>The key to performance of a Merge Join is that the joined columns are already presorted. If they are not, and the query optimizer chooses to sort the data before it performs a Merge Join, and this might be an indication that a Merge Join is not an ideal way to join the tables, or it might indicate that you need to consider some different indexes.<\/p>\n<h2>Adding a WHERE Clause <\/h2>\n<p>Only infrequently will queries run without some sort of conditional statements to limit the results set:; in other words, a <b>WHERE<\/b> clause. We&#8217;ll investigate two multi-table, conditional queries using graphical execution plans.<\/p>\n<p>Run the following query against AdventureWorks, and look at the actual execution plan. This query is the same as the one we saw at the start of the <i>Table Joins<\/i><i> <\/i><i><\/i>&#160;section, but now has a <b>WHERE<\/b> clause.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; e.[Title],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; a.[City],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; c.[LastName] + ',' + c.[FirstName] AS EmployeeName\nFROM&#160;&#160;&#160; [HumanResources].[Employee] e\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; JOIN [HumanResources].[EmployeeAddress] ed ON e.[EmployeeID] = ed.[EmployeeID]\n&#160;&#160;&#160;JOIN [Person].[Address] a ON [ed].[AddressID] = [a].[AddressID]\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; JOIN [Person].[Contact] c ON e.[ContactID] = c.[ContactID]\nWHERE&#160;&#160; e.[Title] = 'Production Technician - WC20' ;\n<\/pre>\n<p>Figure 20 shows the actual execution plan for this query:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"197\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtyThird.jpeg\" width=\"538\" alt=\"618-ThirtyThird.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 20<\/p>\n<p>Starting from the right, we see that the optimizer has used the criteria from the <b>WHERE<\/b> clause to do a clustered index scan, using the primary key. The <b>WHERE<\/b> clause limited the number of rows to 22, which you can see by hovering your mouse pointer over the arrow coming out of the <b>Clustered Index Scan<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b>&#160;operator (see figure 21). <\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"570\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtySecond.png\" width=\"437\" alt=\"618-ThirtySecond.png\" \/><\/p>\n<p class=\"caption\">Figure 21<\/p>\n<p>The optimizer, using the available statistics, was able to determine this up front, as we see by comparing the estimated and actual rows returned in the ToolTip.<\/p>\n<p>Working with a smaller data set and a good index on the <b>Person.Contact<\/b> table, as compared to the previous query, the optimizer was able to use the more efficient <b>Nested Loop<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b><b>&#160;Join&#160; <\/b><b><\/b><b>&#160; <\/b><b><\/b>. Since the optimizer changed where that table was joined, it also moved the scalar calculation right next to the join. Since it&#8217;s still only 22 rows coming out of the scalar operation, a clustered index seek and another nested loop were used to join the data from the <b>HumanResources.EmployeeAddress<\/b> table. This then leads to a final clustered index seek and the final nested loop. All these more efficient joins are possible because we reduced the initial data set with the <b>WHERE<\/b> clause, as compared to the previous query which did not have a <b>WHERE<\/b> clause.<\/p>\n<p>Frequently, developers who are not too comfortable with T-SQL will suggest that the &#8220;easiest&#8221; way to do things is to simply return all the rows to the application, either without joining the data between tables, or even without adding the <b>WHERE<\/b> clause. This was a very simple query with only a small set of data, but you can use this as an example, when confronted with this sort of argument. The final subtree cost for the optimizer for this query, when we used a <b>WHERE<\/b> clause, was 0.112425. Compare that to the 0.400885 of the previous query. That&#8217;s four times faster even on this small, simple query. Just imagine what it might be like when the data set gets bigger and the query becomes more complicated. <\/p>\n<h2>Execution Plans with GROUP BY and ORDER BY <\/h2>\n<p>When other basic clauses are added to a query, different operators are displayed in the execution plans.<\/p>\n<h3>Sort&#160; <\/h3>\n<p>&#160;<img loading=\"lazy\" decoding=\"async\" height=\"37\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-image007.png\" width=\"40\" alt=\"618-image007.png\" \/>&#160;<\/p>\n<p>Take a simple select with an <b>ORDER BY<\/b> clause as an example:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; *\nFROM&#160;&#160;&#160; [Production].[ProductInventory]\nORDER BY [Shelf]\n<\/pre>\n<p>The execution plan&#160; is shown in figure 22.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"109\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-ThirtyFirst.jpeg\" width=\"392\" alt=\"618-ThirtyFirst.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 22<\/p>\n<p>The <b>Clustered Index Scan<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b>&#160;operator outputs into the <b>Sort<\/b><b> <\/b><b>&#160;<\/b><b>&#160;<\/b>operator. Compared to many of the execution plan icons, the Sort operator is very straightforward. It literally is used to show when the query optimizer is sorting data within the execution plan. If an <b>ORDER<\/b> <b>BY<\/b> clause does not specify order, the default order is ascending, as you will see from the ToolTip for the <b>Sort<\/b> icon (see figure 23 below).<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"581\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-Thirtieth.png\" width=\"321\" alt=\"618-Thirtieth.png\" \/><\/p>\n<p class=\"caption\">Figure 23<\/p>\n<p>If you pull up the ToolTip window for the Sort icon (see figure 24), you&#8217;ll see that the Sort operator is being passed 1069 rows. The Sort operator takes these 1069 rows from the Clustered Index Scan , sorts them, and then passes the 1069 rows back in sorted order.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"151\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-TwentyNinth.png\" width=\"375\" alt=\"618-TwentyNinth.png\" \/><\/p>\n<p class=\"caption\">Figure 24<\/p>\n<p>The most interesting point to note is that the Sort operation is 76% of the cost of the query. There is no index on this column, so the Sort operation is done within the query execution. <\/p>\n<p>As a rule-of-thumb, I would say that when sorting takes more than 50% of a query&#8217;s total execution time, then you need to carefully review it to ensure that it is optimized. In our case the reason why we are breaking this rule is fairly straightforward: we are missing a <b>WHERE<\/b> clause. Most likely, this query is returning more rows to be sorted than needs to be returned. However, even if a <b>WHERE<\/b> clause exists, you need to ensure that it limits the amount of rows to only the required number of rows to be sorted, not rows that will never be used.<\/p>\n<p>Other things to consider are:<\/p>\n<ul>\n<li>Is the sort really necessary? If not, remove it to reduce overhead.  <\/li>\n<li>Is it possible to have the data presorted so it doesn&#8217;t have to be sorted? For example, can a clustered index be used that already sorts the data in the proper order? This is not always possible, but if it is, you will save sorting overhead if you create the appropriate clustered index.  <\/li>\n<li>If an execution plan has multiple Sort&#160; &#160;operators, review the query to see if they are all necessary, or if the code can be rewritten so that fewer sorts are needed to accomplish the goal of the query. <\/li>\n<\/ul>\n<p>If we change the query to the following:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; *\nFROM&#160;&#160;&#160; [Production].[ProductInventory]\nORDER BY [ProductID]\n<\/pre>\n<p>We get the execution plan shown in figure 25:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"112\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-TwentyEighth.png\" width=\"523\" alt=\"618-TwentyEighth.png\" \/><\/p>\n<p class=\"caption\">&#160;Figure 25<\/p>\n<p>Although this query is almost identical to the previous query, and it includes an <b>ORDER BY<\/b> clause, we don&#8217;t see a sort operator in the execution plan. This is because the column we are sorting by has changed, and this new column has a clustered index on it, which means that the returned data does not have to be sorted again, as it is already sorted as a byproduct of it being the clustered index. The query optimizer is smart enough to recognize that the data is already ordered, and does not have to order it again. If you have no choice but to sort a lot of data, you should consider using the SQL Server 2005 Profiler to see if any Sort Warnings are generated. To boost performance, SQL Server 2005 attempts to perform sorting in memory instead of disk. Sorting in RAM is much faster than sorting on disk. But if the sort operation is large, SQL Server may not be able to sort the data in memory, instead, having to write data to the tempdb database. Whenever this occurs, SQL Server generates a Sort Warning event, which can be captured by Profiler. If you see that your server is performing a lot of sorts, and many Sort Warnings are generated, then you may need to add more RAM to your server, or to speed up tempdb access.<\/p>\n<h3>Hash Match (Aggregate) <\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"30\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-TwentySeventh.png\" width=\"42\" alt=\"618-TwentySeventh.png\" \/><\/p>\n<p>Earlier in this article, we took a look at the Hatch Match operator for joins. This same Hatch Match operator also can occur when aggregations occur within a query. Let&#8217;s consider a simple aggregate query against a single table using the <b>COUNT<\/b> operator:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; [City],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; COUNT([City]) AS CityCount\nFROM&#160;&#160;&#160; [Person].[Address]\nGROUP BY [City]\n<\/pre>\n<p>The actual execution plan is shown below.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"124\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-TwentySixth.png\" width=\"536\" alt=\"618-TwentySixth.png\" \/><\/p>\n<p class=\"caption\">Figure 26<\/p>\n<p>The query execution begins with an Index Scan, because all of the rows are returned for the query. There is no <b>WHERE<\/b> clause to filter the rows. These rows then need to be aggregated in order to perform the requested <b>COUNT<\/b> aggregate operation. In order for the query optimizer to count each row for each separate city, it must perform a Hatch Match operation. Notice that underneath Hatch Match in the execution plan that the word &#8220;aggregate&#8221; is put between parentheses. This is to distinguish it from a Hatch Match operation for a join. As with a Hatch Match with a join, a Hatch Match with an aggregate causes SQL Server to create a temporary hash table&#160;in memory in order to count the number of rows that match the <b>GROUP<\/b> <b>BY<\/b> column, which in this case is &#8220;City.&#8221; Once the results are aggregated, then the results are passed back to us.<\/p>\n<p>Quite often, aggregations with queries can be expensive operations. About the only way to &#8220;speed&#8221; the performance of an aggregation via code is to ensure that you have a restrictive <b>WHERE<\/b> clause to limit the number of rows that need to be aggregated, thus reducing the amount of aggregation that needs to be done.<\/p>\n<h3>Filter<\/h3>\n<p><img loading=\"lazy\" decoding=\"async\" height=\"37\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-twentyfifth.png\" width=\"40\" alt=\"618-twentyfifth.png\" \/><\/p>\n<p>If we add a simple <b>HAVING<\/b> clause to our previous query, our execution plan gets more complex<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT&#160; [City],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; COUNT([City]) AS CityCount\nFROM[Person].[Address]\nGROUP BY [City]\nHAVING&#160; COUNT([City]) &gt; 1\n<\/pre>\n<p>The execution plan now looks as shown in figure 27:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"113\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-twentyfourth.jpeg\" width=\"536\" alt=\"618-twentyfourth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 27<\/p>\n<p>By adding the <b>HAVING<\/b> clause, the Filter operator has been added to the execution plan. We see that the <b>Filter<\/b> operator is applied to limit the output to those values of the column, <b>City<\/b>, that are greater than 1. One useful bit of knowledge to take away from this plan is that the <b>HAVING<\/b> clause is not applied until all the aggregation of the data is complete. We can see this by noting that the actual number of rows in the <b>Hash Match<\/b><b> <\/b><b><\/b>&#160;operator is 575 and in the <b>Filter<\/b> operator it&#8217;s 348.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"120\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-twentythird.png\" width=\"443\" alt=\"618-twentythird.png\" \/><\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"141\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-twentysecond.png\" width=\"326\" alt=\"618-twentysecond.png\" \/><\/p>\n<p class=\"caption\">Figure 28<\/p>\n<p>While adding a <b>HAVING<\/b> clause reduces the amount of data returned, it actually adds to the resources needed to produce the query results, because the <b>HAVING<\/b> clause does not come into play until after the aggregation. This hurts performance. As with the previous example, if you want to speed the performance of a query with aggregations, the only way to do so in code is to add a <b>WHERE<\/b> clause to the query to limit the number of rows that need to be selected and aggregated.<\/p>\n<h2>Rebinds and Rewinds Explained&#160; <\/h2>\n<p>While examining the ToolTips for physical operators, throughout this article, you will have seen these terms several times:<\/p>\n<ul>\n<li>\n<p>Actual Rebinds or Estimated Rebinds<\/p>\n<\/li>\n<li>\n<p>Actual Rewind or Estimated Rewinds<\/p>\n<\/li>\n<\/ul>\n<p>Most of the time in this article, the value for both the rebinds and rewinds has been zero, but for the Sort&#160; &#160;operator example, a little earlier, we saw that there was <b>one<\/b> actual rebind and <b>zero<\/b> actual rewinds.<\/p>\n<p>In order to understand what these values mean, we need some background. Whenever a physical operator, such as the SORT operator in an execution plan occurs, three things happen.<\/p>\n<ul>\n<li>First, the physical operator is initialized and any required data structures are set up. This is called the <b>Init()<\/b> method. In all cases this happens once for an operator, although it is possible to happen many times.  <\/li>\n<li>Second, the physical operator gets (or receives) the rows of data that it is to act on. This is called the <b>GetNext()<\/b> method. Depending on the type of operator, it may receive none, or many <b>GetNext()<\/b> calls.  <\/li>\n<li>Third, once the operator is done performing its function, it needs to clean itself up and shut itself down. This is called the <b>Close()<\/b> method. A physical operator only ever receives a single <b>Close()<\/b> call. <\/li>\n<\/ul>\n<p>A rebind or rewind is a count of the number of times the <b>Init()<\/b> method is called by an operator. A rebind and a rewind both count the number of times the <b>Init()<\/b> method is called, but do so under different circumstances.<\/p>\n<p>A rebind count occurs when one or more of the correlated parameters of a join change and the inner side must be reevaluated. A rewind count occurs when none of the correlated parameters change and the prior inner result set may be reused. Whenever either of these circumstances occur, a rebind or rewind occurs, and increases their count.<\/p>\n<p>Given the above information, you would expect to see a value of one or higher for the rebind or rewind in every ToolTips or Properties screen for a physical operator. But you don&#8217;t. What is odd is that the rebind and rewind count values are only populated when particular physical operators occur, and are not populated when other physical operators occur. For example, if any of the following six operators occur, the rebind and rewind counts are populated:<\/p>\n<ul>\n<li>Nonclustered Index Spool&#160;  <\/li>\n<li>Remote Query  <\/li>\n<li>Row Count Spool  <\/li>\n<li>Sort&#160;  <\/li>\n<li>Table Spool&#160;  <\/li>\n<li>Table-Valued Function <\/li>\n<\/ul>\n<p>If the following operators occur, the rebind and rewind counts will only be populated when the <b>StartupExpression<\/b><b> <\/b><b><\/b>&#160;for the physical operation is set to <b>TRUE<\/b>, which can vary depending on how the query optimizer evaluates the query. This is set by Microsoft in code and is something we have no control over.<\/p>\n<ul>\n<li>Assert&#160;  <\/li>\n<li>Filter <\/li>\n<\/ul>\n<p>And for all other physical operators, they are not populated. In these cases, the counts for rebind zero count doeot mean that zero rebinds or rewinds occurred, just that these values were not populated. As you can imagine, this can get a little confusing. This also explains why most of the time you see zero values for rebind and rewind.<\/p>\n<p>So, what does it mean when you see a value for either rebind or rewind for the eight operators where rebind and rewind may be populated?<\/p>\n<p>If you see an operator where rebind equals one and rewinds equals zero, this means that an <b>Init()<\/b> method was called one time on a physical operator that is NOT on the inner side of a loop join. If the physical operator is ON the inner side of a loop join used by an operator, then the sum of the rebinds and rewinds will equal the number of rows process on the outer side of a join used by the operator<\/p>\n<p>So how is this helpful to the DBA? Generally speaking, it is ideal if the rebind and rewind counts are as low as possible, as higher counts indicate more disk I\/O. If the counts are high, it might indicate that a particular operator is working harder than it needs to, hurting server performance. If this is the case, it might be possible to rewrite the query, or modify current indexing, to use a different query plan that uses fewer rebinds and rewinds, reducing I\/O and boosting performance.<\/p>\n<h2>Insert&#160; Update and Delete Execution Plans<\/h2>\n<p>Execution plans are generated for all queries against the database in order for the engine to figure out how best to undertake the request you&#8217;ve submitted. While the previous examples have been for <b>SELECT<\/b> queries, in this section we will take a look at the execution plans of <b>INSERT<\/b>, <b>UPDATE<\/b>, and <b>DELETE<\/b> queries.<\/p>\n<h3>Insert&#160;Statements&#160; <\/h3>\n<p>Here is a very simple <b>INSERT<\/b> statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT&#160; INTO [AdventureWorks].[Person].[Address]\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; (\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; [AddressLine1],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; [AddressLine2],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; [City],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; [StateProvinceID],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; [PostalCode],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; [rowguid],\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; [ModifiedDate]\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; )\nVALUES&#160; (\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; '1313 Mockingbird Lane',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'Basement',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; 'Springfield',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; '79',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; '02134',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; NEWID(),\n&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; GETDATE()\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) ;\n<\/pre>\n<p>This statement generates this rather interesting estimated plan (so that I don&#8217;t actually affect the data within the system), shown in Figure 29.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"86\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-twentyfirst.jpeg\" width=\"538\" alt=\"618-twentyfirst.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 29<\/p>\n<p>The execution plan starts off, reading right to left, with an operator that is new to us: <b>Constant Scan<\/b><b> <\/b><b><\/b>. This operator introduces a constant number of rows into a query. In our case, it&#8217;s building a row in order for the next two operators to have a place to add their output. The first of these is a <b>Compute<\/b> <b>Scalar<\/b><b> <\/b><b><\/b>&#160;operator to call a function called <b>getidentity<\/b>. This is the moment within the query plan when an identity value is generated for the data to follow. Note that this is the first thing done within the plan, which helps explain why, when an insert fails, you get a gap in the identity values for a table.<\/p>\n<p>Another scalar operation occurs which outputs a series of placeholders for the rest of the data and creates the new <b>uniqueidentifier<\/b><b> <\/b><b>&#160;<\/b>&#160;value, and the date and time from the <b>GETDATE<\/b> function. All of this is passed to the <b>Clustered Index Insert<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b>&#160;operator, where the majority of the cost of this plan is realized. Note the output value from the INSERT statement, the <b>Person.Address.StateProvinceId<\/b>. This is passed to the next operator, the <b>Nested Loop<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b>&#160;join, which also gets input from the <b>Clustered Index Seek<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b>&#160;against the <b>Person.StateProvince<\/b> table. In other words, we had a read during the INSERT to check for referential integrity on the foreign key of <b>StateProvinceId<\/b>. The join then outputs a new expression which is tested by the next operator, <b>Assert<\/b><b> <\/b><b>&#160;<\/b>. An <b>Assert<\/b> verifies that a particular condition exists. This one checks that the value of Expr1014 equals zero. Or, in other words, that the data that was attempted to be inserted into the <b>Person.Address.StateProvinceId<\/b> field matched a piece of data in the <b>Person.StateProvince<\/b> table; this was the referential check.<\/p>\n<h3>Update Statements&#160; <\/h3>\n<p>Consider the following update statement:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">UPDATE&#160; [Person].[Address]\nSET &#160;&#160;&#160;&#160;[City] = 'Munro',\n&#160;&#160;&#160;&#160;&#160;&#160;&#160; [ModifiedDate] = GETDATE()\nWHERE&#160;&#160; [City] = 'Monroe' ;\n<\/pre>\n<p>The estimated execution plan is shown below:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"83\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-twentieth.png\" width=\"537\" alt=\"618-twentieth.png\" \/><\/p>\n<p class=\"caption\">Figure 30<\/p>\n<p>Let&#8217;s begin reading this execution plan, from right to left. The first operator is a non-clustered Index Scan, which retrieves all of the necessary rows from a non-clustered index, scanning through them, one row at a time. This is not particular efficient and should be a flag to you that perhaps the table needs better indexes to speed performance. The purpose of this operator is to identify all the rows <b>WHERE [City] = &#8216;Monroe&#8217;<\/b>, and then send them to the next operator.<\/p>\n<p>The next operator is <b>TOP<\/b>. In an <b>UPDATE<\/b> execution plan, it is used to enforce row count limits, if there are any. In this case, no limits have been enforced because the <b>TOP<\/b> clause was not used in the <b>UPDATE<\/b> query.<\/p>\n<p><b>Note<\/b>: If the <b>TOP<\/b> operator is found in a <b>SELECT<\/b> statement, not an <b>UPDATE<\/b> statement, it indicates that a specified number, or percent, of rows have been returned, based on the <b>TOP<\/b> command used in the <b>SELECT<\/b> statement.<\/p>\n<p>The next operator is an <b>Eager Spool<\/b><b> <\/b><b><\/b>&#160;(a form of a Table Spool&#160; ). This obscure sounding operator essentially takes each of the rows to be updated and stores them in a hidden temporary object stored in the tempdb database. Later in the execution plan, if the operator is rewound (say due to the use of a Nested Loops operator in the execution plan) and no rebinding is required, the spooled data can be reused instead of having to rescan the data again (which means the non-clustered Index Scan has to be repeated, which would be an expensive option). In this particular query, no rewind operation was required.<\/p>\n<p>The next three operators are all Compute Scalar operators, which we have seen before. In this case, they are used to evaluate expressions and to produce a computed scalar value, such as the <b>GETDATE()<\/b> function used in the query.<\/p>\n<p>Now we get to the core of the <b>UPDATE<\/b> statement, the Clustered Index Update&#160;operator. In this case, the values being updated are part of a clustered index. So this operator identifies the rows to be updated, and updates them.<\/p>\n<p>And last of all, we see the generic T-SQL Language Element Catchall operator, which tells us that an <b>UPDATE<\/b> operation has been completed.<\/p>\n<p>From a performance perspective, one of the things to watch for is how the rows to be updated are retrieved. In this example, an non-clustered Index Scan was performed, which is not very efficient. Ideally, a clustered or non-clustered index seek would be preferred from a performance standpoint, as either one of them would use less I\/O to perform the requested <b>UPDATE<\/b>.<\/p>\n<h3>Delete Statements&#160; <\/h3>\n<p>What kind of execution plan is created with a <b>DELETE<\/b> statement? For example, let&#8217;s run the following code and check out the execution plan.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DELETE&#160; FROM [Person].[Address]\nWHERE&#160;&#160; [AddressID] = 52;\n&#160;\n<\/pre>\n<p>Figure 31 shows the estimated execution plan:<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"194\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-nineteenth.jpeg\" width=\"537\" alt=\"618-nineteenth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 31<\/p>\n<p>I know this is a bit difficult to read. I just wanted to show how big a plan is necessary to delete data within a relational database. Remember, removing a row, or rows, is not an event isolated to the table in question. Any tables related to the primary key of the table where we are removing data will need to be checked, to see if removing this piece of data affects their integrity. To a large degree, this plan looks more like a <b>SELECT<\/b> statement than a <b>DELETE <\/b>statement.<\/p>\n<p>Starting on the right, and reading top to bottom, we immediately get a Clustered Index Delete operator. There are a couple of interesting points in this operation. The fact that the delete occurs at the very beginning of the process is good to know. The second interesting fact is that the Seek Predicate on this <b>Clustered Index Seek<\/b><b> <\/b><b><\/b><b>&#160;<\/b><b> <\/b><b>&#160;To Delete <\/b><b><\/b>&#160;operation was:<\/p>\n<p>Prefix: [AdventureWorks].[Person].[Address].AddressID = Scalar Operator&#173;(CONVERT_IMPLICIT(int,[@1],0)).<\/p>\n<p>This means that a parameter, <b>@1<\/b>, was used to look up the <b>AddressId<\/b>. If you&#8217;ll notice in the code, we didn&#8217;t use a parameter, but rather used a constant value, 52. Where did the parameter come from? This is an indication of the query engine generating a reusable query plan, as per the rules of simple parameterization. <\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"380\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-Eighteenth.jpeg\" width=\"306\" alt=\"618-Eighteenth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 32<\/p>\n<p>After the delete, a series of Index and Clustered Index Seek&#160; s and Scans are combined through a series of Nested Loop Join&#160;operators. These are specifically Left Semi Joins. These operators return a value if the join predicate between the two tables in question matches or if there is no join predicate supplied. Each one returns a value. Finally, at the last step, an Assertoperator, the values returned from each Join, all the tables related to the table from which we&#8217;re attempting to delete data, are checked to see if referential data exists. If there is none, the delete is completed. If they do return a value, an error would be generated, and the <b>DELETE<\/b> operation aborted.<\/p>\n<p class=\"Illustration\"><img loading=\"lazy\" decoding=\"async\" height=\"308\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/imported\/618-seventeenth.jpeg\" width=\"272\" alt=\"618-seventeenth.jpeg\" \/><\/p>\n<p class=\"caption\">Figure 33<\/p>\n<h2>Summary<\/h2>\n<p>This article represents a major step in learning how to read graphical execution plans. However, as we discussed at the beginning of this article, we only focused on the most common type of operators and we only looked at simple queries. So if you decide to analyze a 200-line query and get a graphical execution plan that is just about as long, don&#8217;t expect to be able to analyze it immediately. Learning how to read and analyze execution plans takes time and effort. But once you gain some experience, you will find that it becomes easier and easier to read and analyze, even for the most complex of execution plans.<\/p>\n<div>&#160;  <\/p>\n<hr \/>\n<div id=\"ftn1\">\n<p class=\"MsoFootnoteText\">&#160;<\/p>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Learning how to read and analyze execution plans takes time and effort. But once you gain some experience, you will find them an essential skill for getting to grips with performance issues in SQL Server Queries. Grant here describes the range of execution plans for different simple SQL Queries.&hellip;<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143529],"tags":[5966,4206,4150,4151,4912],"coauthors":[6785],"class_list":["post-488","post","type-post","status-publish","format-standard","hentry","category-performance-sql-server","tag-monitor","tag-performance","tag-sql","tag-sql-server","tag-sql-tsql-transact-sql-execution-plans"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/488","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=488"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/488\/revisions"}],"predecessor-version":[{"id":83112,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/488\/revisions\/83112"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=488"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=488"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=488"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=488"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}