Part 3 of how to Remove Bookmark Lookup, RID Lookup and Key Lookup. A covering index holds every column the query uses in SELECT, JOIN and WHERE.
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup – Part 2
Part 2 compares two ways to remove key lookup and remove RID lookup: a covering non-clustered index versus an index with included columns.
SQL SERVER – Query Optimization – Remove Bookmark Lookup – Remove RID Lookup – Remove Key Lookup
A quick tip to remove bookmark lookup, RID lookup or key lookup: why it happens when a query needs columns missing from the non-clustered index.
SQL SERVER – Interesting Observation – Query Hint – FORCE ORDER
A junior developer used the FORCE ORDER query hint without knowing what it does. Here is how it works and how much this one hint can change performance.
Filtered Indexes and Where They Help
Filtered indexes help SQL Server target active rows in a larger table; learn useful predicates, parameter limitations, and how to verify plan choices.
ALLOW_PAGE_LOCKS OFF Stops REORGANIZE
ALLOW_PAGE_LOCKS OFF prevents a rowstore index from being reorganized. A controlled example shows the maintenance error, checks the index setting and verifies that enabling page locks allows REORGANIZE without changing the stored rows.
SQL SERVER – Interesting Observation – Execution Plan and Results of Aggregate Concatenation Queries
Reader Bob explains why aggregate concatenation results depend on expression location, using an ANSI point of view, and shares a similar example to run.