Execution plans
Improving Query Performance in SQL Server
Dean Smith
Founder, Atamai Analytics
Optimization phase
Optimization phase
Cost parameters evaluated include:
- Processor usage
- Memory usage
- Data page reads
Optimization phase
Cost parameters evaluated include;
- Processor usage
- Memory usage
- Data page reads
Estimated execution plan in SSMS
Estimated execution plan in SSMS
Viewing executions plans in SSMS
Operator statistics
Reading execution plans
Index example
SELECT *
FROM Customers
WHERE CustomerID = 'PARIS';
SELECT *
FROM CustomersCI
WHERE CustomerID = 'PARIS';
Sort operator example
SELECT FruitName, FruitType
FROM Fruits1
UNION
SELECT FruitName, FruitType
FROM Fruits2;
SELECT FruitName, FruitType
FROM Fruits1
UNION ALL
SELECT FruitName, FruitType
FROM Fruits2;
The same execution plan?
SELECT *
FROM Customers
WHERE CustomerID IN
(SELECT CustomerID
FROM Orders);
SELECT *
FROM Customers c
WHERE EXISTS
(SELECT 1
FROM Orders o
WHERE c.CustomerID = o.CustomerID);
