SQL Execution plan deep dive (03-Scan, Key Lookup)

Note: This blog is continuation to earlier 2 blogs
1.       SQL Execution plan deep dive (01-Basics I)
2.       SQL Execution plan deep dive (02-Basics II)

In the next few blogs, I will cover following topics which can be analyzed during performance analysis using Execution plans.

  • Lookups
  • Scans
  • Spool
  • Sort
  • Hash
  • Nested Loops

Lookups and Scans are explained in this blog. Lookups and scans can be alternatively used for the same query but wrong selection could impact the performance!

Lookups:
While using filter criteria in a query, SQL Server uses non-clustered index Seek to find the key and Key Lookup will be used to fetch other column information from Clustered Index.

Using Lookup sounds like an excellent plan when the filter criteria return very few rows but if the number of rows in filter criteria increases, the number of lookup operations increases resulting longer execution times. At one point SQL Optimizer decides to use Index Scan (Clustered Index or Heap) instead of Index Seek and lookup operation. The change when it occurs is called tipping point and the same is covered in a different blog (https://vijredblog.wordpress.com/2014/06/12/how-statistics-impact-the-performance-of-sql-query/).

Often we assume index Scan is very costly which needs to be avoided but lets us check what happens if Index Seek is wrongly used in place of Scan!

-- Enable actual execution plan (Ctrl + M)

-- Enable the option to Include Actual Execution Plan, Enable Statistics

SET STATISTICS IO ON;
GO

--Clear existing buffers
DBCC DROPCLEANBUFFERS;
GO

SELECT *
FROM [Production].[Product]
WHERE Name > 'd';

--Clear existing buffers
DBCC DROPCLEANBUFFERS;
GO

SELECT *
FROM [Production].[Product] WITH (INDEX(AK_Product_Name))
WHERE Name > 'd';
--

Query Results and Execution Plan:
Scan_Lookup_1

Referring to above image, Number of logical reads increased and Estimated Execution time increased 10 fold because of wrongly using AK_Product_Name index seek. Check the Key Lookup component, Number of executions are 485 which is increasing execution time.

Scans:
Scan reads all records in the table but it can be used to avoid lookup, a single table scan may prevent multiple lookups. The scan will be idle when the number of selected rows are the majority of the rows in the table.


-- Enable actual execution plan (Ctrl + M)

-- Enable the option to Include Actual Execution Plan, Enable Statistics

SET STATISTICS IO ON;
GO

--Clear existing buffers
DBCC DROPCLEANBUFFERS;
GO

SELECT *
FROM [Production].[Product]
WHERE Name > 'Women''s T';

--Clear existing buffers
DBCC DROPCLEANBUFFERS;
GO

SELECT *
FROM [Production].[Product] WITH (INDEX(PK_Product_ProductID))
WHERE Name > 'Women''s T';

DBCC DROPCLEANBUFFERS;
GO
--

Query results and execution plan:
Scan_Lookup_2

In above example, using index scan increased the execution time.

Advertisements
This entry was posted in Execution Plan, knowledge, Performance, Productivity, SQL, SQL Server, Uncategorized and tagged , , , , , . Bookmark the permalink.

One Response to SQL Execution plan deep dive (03-Scan, Key Lookup)

  1. Pingback: SQL Execution plan deep dive (04-Spool) | Blog for reference – Vijred

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s