|
|
An SQLServer execution plan is the result of the query optimizer's attempt to
calculate the most efficient way to process the request represented by the
SQL query text. Execution plans can help you understand how a query will be
executed, and therefore can assist to chose the most efficient query composition.
Lets look at a very simple query that runs on Employees table that
lacks any index.
SELECT EmpNo, DeptNo, FirstName, LastName
FROM dbo.Employees
WHERE EmpNo = 2;
|
Select query text and chose "Include Actual Execution Plan" option.
The results will look like this:
| EmpNo | DeptNo | FirstName | LastName |
| 2 | 1 | gret | garbo |
The Execution Plan will look like this:
Optimizer chosed "Table Scan" operator to process this query. "Table Scan" is
ok for quering small tables (no more than thousand records). For bigger tables
we would like something more sophisticated. Let's look at the same query plan
after adding Unique Clustered INDEX on EmpNo column.
CREATE UNIQUE CLUSTERED INDEX
[idx1_empno_unique_clust]
ON [dbo].[Employees]
(
[EmpNo] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
|
The Execution Plan will look like this:
"Clustered Index Seek" is a very efficient operator, cause server engine
will execute index search instead of sequential scan of entire Employees
table. Right clicking "Clustered Index Seek" icon inside execution plan
window, we recive tooltip information about actual execution cost involved:
|
