Query plan properties of interest
Each operator in a query execution plan has several properties that provide context and metrics around its compilation, optimization, and execution. The plans also have global properties to provide overall context. Examining some key properties for the overall plan and some operators is especially relevant to writing T-SQL that scales well. We will look at these properties, understand their meaning, and become familiar with their significance, which will be important in the chapters where we explore T-SQL patterns and anti-patterns.
Plan-level properties
The root node of a plan has a few properties that are important for understanding the context of execution. Different trace flags or SET options change execution context and may drive query optimization choices, so having this information persisted in the showplan is a valuable tool.
The following example shows a query executed in the AdventureWorks sample database that allows us to examine...
