Page History

The Sort operator presents the sorting of the rows returned from the previously executed operation (appears just below this operator) in a specific order, optionally eliminating duplicate entries.

Sorting is required when no index that satisfies the requested ordering exists, or when an index scan is more expensive than sorting. It is usually used as the final step to retrieve the fetched data or a prior step for the join or stream aggregate (group by) operators. In some cases, the sort operation requires more than the available memory, in which case a temporary table is used. This involves disk I/O, which impacts performance. Sorting a large number of rows makes heavy use of the CPU, which can also affect overall server performance.

The amount of memory needed for sorting can be estimated by multiplying the number of rows by the average row size. Therefore, minimizing the row size by selecting only the necessary fields will decrease the memory required for the sort operation.

Hash join algorithms are used as follows:

• Joins. Create a hash table for the rows from the first table. For each row from the second table, probe the hash table according the hash function and return rows dictated by the join type.
• Remove duplicates. Create a hash table for the rows of the table. Scan the hash table and return only one item for each entry.
• Unions. Create a hash table for the rows of the first table. Return rows from the second table that do not exist in the hash table. The second table must not contain duplicate entries.

Nested joins (also called nested loops) use the nested algorithm to join two tables. The nested algorithm works as follows: For each row in the outer table (the first table, appearing directly below the nested loop operator in the explain tree), SQL Server executes the access plan for the inner table (the second one).

A large number of iterations may indicate a problem in choosing the access plan due to out-of-date statistics.

The Potential Problem icon indicates there is a potential problem in the specified table. A potential problem can be one of the following:

• Operators of type table scan, index scan, clustered index scan and filter, where the estimated cost is greater than a predefined value (this value can be changed).
• Operators with warnings.

The number of pages allocated in the disk for the table records, index records, and text or image data. The value might be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the DBCC UPDATEUSAGE command to recalculate the space usage fields.

See SQL Server Books Online for DBCC UPDATEUSAGE , for additional information.

The number of pages used in the disk for the table records, index records, and text or image data. The value might be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the DBCC UPDATEUSAGE command to recalculate the space usage fields.

See SQL Server Books Online for DBCC UPDATEUSAGE , for additional information.

The number of pages allocated in the disk for the index records. The value may be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the DBCC UPDATEUSAGE command to recalculate the space usage fields.

See SQL Server Books Online for DBCC UPDATEUSAGE , for additional information.

The number of pages used in the disk for the index records. The value may be incorrect due to out-of-date space usage information. If you suspect the value to be incorrect, use the DBCC UPDATEUSAGE command to recalculate the space usage fields.

See SQL Server Books Online for DBCC UPDATEUSAGE , for additional information.

Displays the major types of the operators.

The operators in the Execution plan tree are separated into the following major types:

• Table (any access to table)
• Index (any access to index)
• Sort (any sort operation)
• Join (any join operation)
• Other (all other operations)

Displays the major types of the operators.

The operators in the Execution plan tree are separated into the following major types:

• Table (any access to table)
• Index (any access to index)
• Sort (any sort operation)
• Join (any join operation)
• Other (all other operations)

Displays the type of change detected. Can be one of the following values:

• Schema changes
• Instance parameters changes and database option changes, as collected by the Collect Schema Changes process
• Estimated access plan changes, as collected by the Explain Statements process
• Actual access plan changes, as collected by the Explain Statements process (SQL Server 2005 only)
• Volume changes, as collected by Collect Space Utilization process

Displays the type of schema change detected. Can be one of the following values:

• File Group
• No of Columns
• Delete Trigger Count
• Insert Trigger Count
• Update Trigger Count
• Column Attribute Changed
• Index Created
• Index Dropped
• Index keys Changed
• Index Cluster Property Changed
• Index Unique Property Changed
• Index Pad Property Changed
• Index Auto Statistics Property Changed
• Index Row Lock Disallowed Changed
• Index Page Lock Disallowed Changed
• Index Fill Factor Changed

Switches to Plan view, with the selected statement in-context.

This icon is only enabled at the batch level and in the statement that was captured by the Collector.