A bookmark key lookup slows down any query. Here are my four posts on removing it, gathered in one place so you can read them in order.
SQL SERVER – Size of Index Table – A Puzzle to Find Index Size for Each Index on Table
sp_spaceused shows the total index size of a table. My puzzle for you: write a query that adds the size of each index to the sys.indexes list.
SQL SERVER – 2005 2008 – Backup, Integrity Check and Index Optimization By Ola Hallengren
Ola Hallengren’s free scripts for backup, integrity check and index optimization in SQL Server 2005 and 2008, with notes from Ola on how they work.
SQL SERVER – Disable CHECK Constraint – Enable CHECK Constraint
Need to disable CHECK constraint validation for a bulk load? Here is how, and how to enable it again with WITH CHECK CHECK CONSTRAINT, the syntax with CHECK twice.
SQL SERVER – Removing Key Lookup – Seek Predicate – Predicate – An Interesting Observation Related to Datatypes
Seek Predicate beats Predicate. An observation from a query optimization project on removing key lookup and how datatypes decide which one you get.
SQL SERVER – How to Enable Index – How to Disable Index – Incorrect syntax near ‘ENABLE’
ALTER INDEX with ENABLE fails with Incorrect syntax near ‘ENABLE’. To enable index after you disable it for a bulk load, you rebuild it instead.
Slow Query Cartoon: Filtered Indexes and Functions, Tested
This slow query cartoon from Community TechDays in Ahmedabad answers a slow query with questions, not an automatic index. The tests here measure a full index against a filtered index, show why a function on a column forces a scan, and show a parameter case where the filtered index is skipped.