Kristian Ask (11/16/2012)
I know it's an old topic but how is included columns affecting memory footprint? Some say higher use to put index in.memory cache. Some say lower since less pages are read and cached since it is in the index.K
There is less memory footprint because the idea behind a non-clustered index with INCLUDE columns is to help avoid bookmark lookups in the query plan. Bookmark lookups are operations that go and fetch data from the clustered index (ie. the actual table) to help return columns that are in the SELECT portion of the query. Imagine a very wide table with tens or hundreds of columns being loaded into memory for the bookmark lookup. The memory footprint will be higher than if only a select few columns are instead loaded into memory as include columns in a noncl. Index.
Does my somewhat muddled explanation make sense? 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]