Earlier last week, I published a blog post and SQL in Sixty Seconds video and it got thousands of view in just a few hours and I have received lots of questions based on this question about how to remove bookmark lookup. I have taken the 7 most asked questions and decided to answer them over here.
7 Q and A on Remove Bookmark Lookup
Question 1: I see you adding STATISTICS IO ON on the top of the query, does it slow down your query execution.
Answer 1: Everything you add to query which is not essential to the query will add overhead. There is no need to add STATISTICS IO ON on the production queries. Additionally, the overhead which it ads is next to nothing. I have tested it multiple times during my 10 years of career and I can tell you for sure that 99.99% it has no negative impact.
Additionally, during my demo, I had added the command both the queries, hence the comparison can be done apples to apples.
Question 2: What are different kinds of bookmark lookup?
Answer 2: In my career, I have come across two different kinds of bookmark lookups.
1) Row Lookup when there is no clustered index on the table and query has to look up the column which is not there in the nonclustered index.
2) Key Lookup when there is a clustered index on the table and query has to look up the column which is not in the nonclustered index.
Question 3: I have a column in the SELECT statement and not in the WHERE condition, where should I add that column in the index to resolve the bookmark lookup issue?
Answer 3: Start adding it in the include part only. In most cases that should fix the bookmark lookup issue.
Question 4: I have a column in the SELECT statement and in the WHERE condition, where should I add that column in the index to resolve the bookmark lookup?
Answer 4: I really wish the answer to this question is simple. Honestly, there is no good answer but I can tell you a good starting point here.
1) Add a column to include part of the index and see if it solves the problem.
2) If not, add a column to the key column part in the index and see if it solves the problem.
3) If not, I think it is time when we talk in detail.
Question 5: Are all the bookmark lookups bad?
Answer 5: In SQL Server there is never an absolute answer. Let me say this way, my experience says removing the bookmark lookup will get you necessary gain in performance. However, there are times, it is just fine to leave the bookmark lookup as it is when adding more columns to the index can further reduce the performance.
Question 6: Are all index scans are bad and index seeks are good?
Answer 6: Again, I will repeat that In SQL Server there is never an absolute answer. There are times when scans have almost the same impact as seeks in the real world and there have been moments when seeks are worst performing that scans. However, it is always a good idea to look up at your table operator, its costs and statistics to make decisions what is best for you.
However, during my consulting career of over 10 years, I have seen queries performing much better when there are seek operators instead of scan in over 90% of the cases.
Question 7: How many columns are too many for a single index?
Answer 7: I will say start with no more than 5-7 for key columns and no more than 5-7 for included columns. While you should carefully evaluate your data types, data distribution and queries to decide what number best works for you. My most client has found that to 5-7 columns are good enough for them.
I have received over 100 questions about my recent video posted here. While I have answered each of them, I have decided to post the seven most asked question in this blog post. Do let me know your feedback about remove bookmark video.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
