estimated v.s. actual plan
An essential tool for designing indexes is the query plan. Use the graphical versions from the UI, or one of the SET SHOWPLAN commands to get a textual version of the estimated plan, or SET STATISTICS PROFILE ON for a textual version of the actual plan. Adding indexes without any real knowledge of how they are used often ends up creating useless indexes.
A common reason to work with estimated query plans is to evaluate performance of a query in development, particularly when the query execution time is long or when restoring the database to its state prior to query execution is challenging. You can add or change indexes or modify the query structure and then analyze changes to the estimated query plan after making these changes.
SET SHOWPLAN_XML ON Instructs SQL Server to generate the estimated plan without executing the query.
SET SHOWPLAN_TEXT ON Returns a single column containing a hierarchical tree that describes the operations and includes the physical operator and optionally the logical operator.
SET SHOWPLAN_ALL ON Returns the same information as SET SHOWPLAN_TEXT except the information is spread across a set of columns in which you can more easily see property values for each operator.
By hovering the cursor over the Index Seek (NonClustered) operator, you can display the tooltip that shows the Estimated Number of Rows is 1. Consequently, the query optimizer selects an index seek an operation because it is efficient for a single row.
To generate an actual query plan, uses the SET STATISTICS XML ON statement to have SQL Server generate a graphical actual query plan. As an alternative, you can use the SET STATISTICS PROFILE ON statement to get the query plan information in a hierarchical tree with profile information available across columns in the result set.
It shows that SQL Server recognized the change of greater than 20% in the table’s statistics and performed an automatic update which in turn forced a recompilation of the SELECT statement’s query plan. This time the query optimizer chose a Table Scan operator because the number of rows to retrieve is nearly half the number of rows in the table.
Example:
You have multiple queries that take a long time to complete. You need to identify the cause by using detailed information about the Transact-SQL statements in the queries. The Transact-SQL statements must not run as part of the analysis. Which Transact-SQL statement should you run?
A. SET STATISTICS IO ON
B. SET SHOWPLAN_TEXT ON
C. SET STATISTICS XML ON
D. SET STATISTICS PROFILE ON
Correct Answer: B
SET SHOWPLAN_TEXT ON causes Microsoft SQL Server not to execute Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are executed.
Incorrect Answers: C: SET STATISTICS XML ON causes Microsoft SQL Server to execute Transact-SQL statements and generate detailed information about how the statements were executed in the form of a well-defined XML document.
D: When STATISTICS PROFILE is ON, each executed query returns its regular result set, followed by an additional result set that shows a profile of the query execution. The additional result set contains the SHOWPLAN_ALL columns for the query and these additional columns.
Rows: Actual number of rows produced by each operator
Executes: Number of times the operator has been executed
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-showplan-text-transact-sql