What is a SQL execution Plan?
Every SQL query is broken down in to series of execution steps called as operators. Each operator performs basic operations like insertion, search, scan, updation, aggregation etc. There are 2 kinds of operators Logical operators and physical operators. Logical operators describe how the execution will be executed at a conceptual level while physical operators are the actual logic / routine which perform the action. On the query plan you always physical operators. One logical operator can map to multiple physical operator. It can also go vice versa but that’s a rare scenario. Some operators are both physical as well as logical. You can check out all the logical and physical operators for SQL Server at http://msdn.microsoft.com/en-us/library/ms191158.aspx . Below table shows some sample mapping between logical and physical operators.
