SQL Server Performance Forum – Threads Archive
BookMark Lookups
The main cost of one query I have is the BookMark lookup. Is there any way to get this to speed up? From what I understand, if the nonclustered index has all the data contained in the index then the BookMark lookup is not required. All data will be retrieved from the index.Should I index more columns??? I have a couple more fields I can index but they are not selective at all, but are used in the where clause. ideas?
The index that you are talking about here are called covering index. The disadvantage of having a index with large no. of columns is that the size of the index increases and so the query optimizer will have to go thro’ more pages to reach the leaf page. Another obvious disadvantage is the maintainence cost increases. So create an index and see if the otimizer is using it. Also see the impact of index on other queries. Note, you don’t need to include the clustered index columns in the index as they are present by default. HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
It depends. how many more columns do you want to add to the cover index, and what is thier datatype? how many inserts vs searches do you have? Did you consider a clustered index? If you already have one, is an indexed view an option? Bambola.
only the lead columns of an index needs to be selective.
I prefer to lead with the grouping column when selecting multiple rows (ex: CustomerID in the Orders table) even though it may not be the most selective, followed by some other column(s) to make to form a unique set (CustomerID followed by OrderID) when multiple rows are selected frequently, i will look into a covering index to get rid of the bookmark lookup. My criteria is not necessarily raw size of the entire index, but rather the average size of the index row. For large tables, I like to keep this under 50bytes. for small tables, its not important the cost of each additional index appears to add ~20% to the base cost of an insert/update/delete operation (excluding additional cost for FK), so I would advise some estimate on the benefit versus cost (ie, selects vs. writes, weighted by row count)
]]>