26 performance tuning questions and solutions, Advanced SQL interview questions, Advanced SQL Queries, Advanced SQL tutorial, Advanced SQL tutorial pdf, Difficult SQL Interview Questions, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, How to tune SQL queries, Interview questions for SQL Server Performance Tuning, key lookups, Looking for SQL Optimization Interview Questions, lookups, Lookups are bad, performance sql server, Performance tips for faster SQL queries, Performance Tuning, Performance Tuning for SQL Server, Query Optimization, Query Performance Tuning, SQL, SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL Complex Queries, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Optimization Interview Questions, sql performance, sql performance and tuning, sql performance explained pdf, sql performance tips, SQL Performance Tuning, sql performance tuning and optimization, sql performance tuning interview questions, sql performance tuning tips, SQL Queries asked in interviews, SQL Query Optimizer, SQL Query Tuning or Query Optimization, SQL Questions, SQL Server, SQL Server - General Interview Questions and Answers, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, SQL SERVER Interview questions and answers for experienced, sql server interview questions and answers for net developers, SQL SERVER Interview questions for experienced, SQL SERVER Interview questions pdf, SQL server optimization interview questions and answers, sql server performance query, sql server performance slow, SQL Server Performance Tuning, SQL Server Performance Tuning Tips, SQL SERVER Tips, SQL SERVER Tricky questions, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, SQL Tricky question, SQL Tuning Overview, SQL Tutorial, T-SQL, T-SQL Interview questions, T-SQL Programming, T-SQL Server Interview Questions, T-SQL Tutorial, Tips for SQL Database Tuning and Performance, Top 10 performance tuning tips for relational databases, TOP 100 SQL SERVER INTERVIEW QUESTIONS, Top 50 SQL Server Questions & Answers, Tough PL SQL Interview Questions, Tough SQL Interview Questions, Tough SQL Queries, Tough SQL Queries Interview Questions, Tough SQL Questions, Tricky SQL Interview Questions, Tricky SQL Interview Questions and answers, Tricky SQL Queries for Interview, Tricky SQL SERVER Interview Questions and answers, Why Lookups are bad for performance?
Why Lookups are bad for performance?
Well today we will talk about why lookups are bad for query performance. So honestly lookups are not bad every time. It depends where optimizer have used it. So we will go step by step to understand this concept in detail. First we will understand what lookups, their types are and when they are bad for performances and what are the remedies we have.
1. What are lookups?
2. What are the types of lookups?
3. Examples and lookup operators in execution plan
4. Scenarios where they are bad for performances?
5. Remedies for lookups? How to avoid lookups?
What are lookups?
Please note that lookups happens only in case of Non Clustered Indexes only. This is because in clustered indexes we will have data for all columns at leaf level.
Now lookup case your query is able to use Non Clustered to satisfy the search argument from where clause (Predicate) but you have at least a column in the select list which is not available (Not included) with this index. So effectively from somewhere we have to pull out the remaining columns as optimizer has to satisfy the user’s query. So what it does, it uses RID or clustered key (Clustered Index Key) to fetch the remaining columns from either heap or clustered Index.
So effectively we have two types of lookups
1. RID Lookup (Heap) – If the table referenced does not have a clustered index then the query optimizer has to go back to the heap to get the additional information. This is called RID lookup (Heap).
2. Key Lookup (Clustered) – So if the table referenced has a clustered index then the query optimizer has to go back to the clustered index using Clustered key it got from the Non Clustered index. This is called Clustered Key Lookup.
Example of RID Lookup (Heap)
-- /* Create a simple customer table */ CREATE TABLE Customers ( ID INT ,Name VARCHAR(10) ,Addr VARCHAR(10) ) GO /* Insert some data */ INSERT INTO Customers SELECT DISTINCT number --, LEFT(CAST(NEWID() AS VARCHAR(255)),8),LEFT(CAST(NEWID() AS VARCHAR(255)),8) FROM MASTER..SPT_VALUES WHERE number > 0 and number < 1000 GO INSERT INTO Customers SELECT DISTINCT number, '02BBD08D', 'Sometext' FROM MASTER..SPT_VALUES WHERE number > 1158 and number < 1258 /* Create Non Clustered index on Name column */ CREATE NONCLUSTERED INDEX Ix_Name ON Customers(Name) /*Execute the below query and examine the execution plan*/ SELECT Name, Addr FROM Customers WITH (INDEX(Ix_Name)) WHERE Name = '02BBD08D' --
Now let’s examine the execution plan for the above query.
Here we have RID Lookup (Heap) since we don’t have Addr column in non-clustered index. Query optimizer uses RID lookup to fetch the additional information. Now let’s check whether it is costly for us not.
Now check out the number of executions for this iterator. It is 101 in our case. Here the number is on the higher side. If this number of executions are 1, 2, 3 or some smaller then it is fine but if you have got lots of rows the cost of this operator becomes astronomical.
In this case we are reading 104 pages from disk. Check out the output of StatisticsIO ON
Lookups are very easy to fix. Now let’s modify our index to include the Addr column in the include section. Now why I am adding addr column in the include section is because we are not using this column in the where that’s why. If we would have used in the where then we could have used it in the key column section.
-- /* Drop the existing index and create Non Clustered index on Name column with Addr Column in the include section */ DROP INDEX Ix_Name ON Customers GO CREATE NONCLUSTERED INDEX Ix_Name ON Customers(Name) INCLUDE(Addr) GO /*Execute the below query and examine the execution plan*/ SELECT Name, Addr FROM Customers WHERE Name = '02BBD08D' --
Now let’s examine the execution plan and how many pages we are reading.
-- (101 row(s) affected) Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) --
In this case we are reading only 2 pages. So we are doing great!
In this case we are doing great since we removed RID Lookup(Heap) and Nested Loop(Inner Join) iterator. Enjoy
Example of Key Lookup (Clustered)
-- USE AdventureWorks2014 GO SELECT p.Name , p.Color FROM [Production].[Product] p WHERE p.Name LIKE 'k%' --
Now let’s examine the execution plan.
Now let’s include color column also in the index.
-- /* Drop the existing index and create Non Clustered index on Name column with color column in the include section */ DROP INDEX AK_Product_Name ON [Production].[Product] GO CREATE NONCLUSTERED INDEX AK_Product_Name ON [Production].[Product](Name) INCLUDE(Color) GO --
Now let’s check the new execution plan
Please understand that Lookups are very easy to fix but you also need to consider other things like how many total indexes you have on your table as indexes comes with a cost. Also whenever you do some DML operation (Insert, Update and Delete) you indexes will be restructured.
Now let’s talk about scenarios where lookups are bad and there remedies.
1. If the number of executions are on the higher side then only should concern about this. If the number of executions in the lookup section is small then we can ignore this.
2. If your query is doing a RID lookup, which means you don’t have Clustered Index on your table. In this scenario you should create a clustered index on your table to get better statistics and eventually an optimal execution plan rather than.
3. Now check whether you really need that extra column(s) or not, most of the time people tend to write * and don’t understand whether they really need that or not. Select only columns that you actually required.
4. If you query id doing a Key lookup(Clustered) then you need to consider things like how many existing indexes you have on your table and whether other queries are badly affected or not due to inclusion of this column. My rule of thumb is that each table should have less than five indexes. We can go ahead and add that column in the include section or the key section depending of the requirement of the query.
I hope you have enjoyed the article. Thanks for reading !
Sanjay Kumar said:
11/10!! Your blog is such a complete read. I like your approach with Why Lookups are bad for performance?. Clearly, you wrote it to make learning a cake walk for me.
how to find first non-space character from end of the string
ex: ‘abc def g ‘
I should get 4 in this case. (the first nonspace character from backwards in the above example is g and the position is 4 from backwards.
Could you help me in getting this?
I am so grateful for your blog. Really looking forward to read more.