Performance Basics: Key lookup operator and query optimization
One of the most basic things to improve performance in our queries is using indexes, but this doesn't mean that we can blindly create them for all our tables, or just thinking that it is enough to create them only for the columns we use to " search ", yes, this could be beneficial if our tables are now big enough, our system is not under a great I/O pressure. But for high concurrency systems, queries to large tables or more complex T-SQL statements, we could tune indexes a little bit more, and try to avoid disk reads as possible as we can do it. What we can do to improve performance is to avoid key lookups whenever possible. Key lookup icon The Key Lookup operator works this way: you have an index defined for your search argument, the index is used to locate the record, and then once located, an additional search is made to locate the record and retrieve the required columns in our query. So... for example, if the query is not used often and only to locate a