Interpreting Graphical Execution Plan Icons

sqlserver2014

These icons are displayed in the SQL Server Management Studio when displaying the graphical execution plan and represent operators used by SQL Server to execute statements. For more detailed information, see this Microsoft article on SQL Server 2008 R2.

Execution plans are not displayed for encrypted stored procedures or for triggers.

When we create a sql query and pass that statement to SQL Server, it goes to is the relational engine first. As the sql arrives, it passes through a process that verifies the sql was written correctly and that it is properly formatted. This process is called query parsing. If a query fails to parse correctly because of a typo, then parsing stops and SQL Server returns an error. The output of the Parser process is a parse tree, or query tree. The parse tree represents the logical steps required to execute the requested query. If the T-SQL string is a data definition language (DDL) query, instead of a  data manipulation language (DML) statement, it will not be optimized during this process.

If the T-SQL string is a DML statement and it has parsed correctly, the parse tree is passed to a process called the algebrizer. The algebrizer resolves all the names of the various objects, tables, and columns in the query string. The algebrizer outputs a binary called the query processor tree, which is then passed on to the query optimizer. Part of what is passed to the query optimizer is a hash value that represents the query. The query optimizer attempts to find this hash value in it’s history, and if it does then it just uses the previous plan to execute the query. If it doesn’t find a similar hash value, it creates a new query plan. This technique is intended to reduce the overhead required by the query optimizer to generate a new plan for each query.

The query optimizer is a very complicated piece of software that attempts to return query results using the least amount of time possible. Once the results are returned, you can use the execution plan to uncover how the results were generated and potentially make changes to improve the speed of the results. These graphic images can be difficult to understand at first, but it is important that you earn them to provide the best technical support possible.

Execution Plan Example

 

This example screen shot shows some of the icons which you might see in our execution plans.

Operators that are executed in parallel are displayed with the following parallel process icon above the operator. Parallel process icon

Icon Operator
Arithmetic expression operator icon Arithmetic Expression
Assert operator icon Assert
Bitmap operator icon Bitmap
Bookmark lookup operator icon Bookmark Lookup
Clustered index delete operator icon Clustered Index Delete
Clustered index insert operator icon Clustered Index Insert
Clustered index scan operator icon Clustered Index Scan
Clustered index seek operator icon Clustered Index Seek
Clustered index update operator icon Clustered Index Update
Collapse operator icon Collapse
Compute scalar operator icon Compute Scalar
Concatenation operator icon Concatenation
Constant scan operator icon Constant Scan
Delete (Database Engine) operator icon Delete
Delete scan operator icon Deleted Scan
Spool operator icon Eager Spool
Filter (Database Engine) operator icon Filter
Hash match operator icon Hash Match
Hash match root operator icon Hash Match Root
Hash match team operator icon Hash Match Team
Insert (Database Engine) operator icon Insert
Inserted scan operator icon Inserted Scan
Iterator catchall operator icon Iterator Catchall
Spool operator icon Lazy Spool
Log row scan operator icon Log Row Scan
Merge interval operator icon Merge Interval
Merge join operator icon Merge Join
Nested loops operator icon Nested Loops
Nonclustered index delete operator icon Nonclustered Index Delete
Nonclustered index insert operator icon Nonclustered Index Insert
Nonclustered index scan operator icon Nonclustered Index Scan
Nonclustered index seek operator icon Nonclustered Index Seek
Nonclustered index spool operator icon Nonclustered Index Spool
Nonclustered index update operator icon Nonclustered Index Update
Online index insert operator icon Online Index Insert
Parameter table scan operator icon Parameter Table Scan
Remote delete operator icon Remote Delete
Remote insert operator icon Remote Insert
Remote query operator icon Remote Query
Remote scan operator icon Remote Scan
Remote update operator icon Remote Update
RID lookup operator icon RID Lookup
Row count spool operator icon Row Count Spool
Segment operator icon Segment
Sequence operator icon Sequence
Sequence project operator icon SequenceProject
Sort operator icon Sort
Split operator icon Split
Spool operator icon Spool
Stream aggregate operator icon Stream Aggregate
Switch operator icon Switch
Table delete operator icon Table Delete
Table insert operator icon Table Insert
Table scan operator icon Table Scan
Table spool operator icon Table Spool
Table update operator icon Table Update
Table-valued function operator icon Table-valued Function
Top operator icon Top
Extended operator (UDX) icon UDX
Update (Database Engine) operator icon Update

The following icons displayed in the graphical execution plan represent the Cursor Logical and Physical Showplan Operators used by SQL Server to execute statements.

The following icons displayed in the graphical execution plan represent the Parallelism Showplan Operator physical operators used by SQL Server to execute statements.

Icon     Parallelism physical operator
Distribute streams parallelism operator icon Distribute Streams
Repartition streams parallelism operator icon Repartition Streams
Gather streams parallelism operator icon Gather Streams

The following icons displayed in the graphical execution plan represent the Transact-SQL language elements used by SQL Server.

Advertisements

9 thoughts on “Interpreting Graphical Execution Plan Icons”

  1. It is really a nice and useful piece of information. I’m glad that you shared this helpful information with us. Please keep us informed like this. Thank you for sharing.

    Like

  2. Pingback: Dude
  3. Presently TELLER MACHINE skimming it’s difficult issue, Thanks for discussing this publish. This is a great and also helpful for everyone.

    Like

  4. Great blog here! Also your website loads up fast! What web host are you using? Can I get your affiliate link to your host? I wish my website loaded up as quickly as yours lol

    Like

  5. Greetings! Very helpful advice in this particular post! It is the little changes that produce the most significant changes. Thanks for sharing!

    Like

  6. You have made some really good points there. I checked on the internet to find out more about the issue and found most individuals will go along with your views on this web site.

    Like

  7. I have been browsing online more than 3 hours today, yet I never found any interesting article like yours. It’s pretty worth enough for me. In my opinion, if all site owners and bloggers made good content as you did, the web will be much more useful than ever before.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s