I'm trying to understand how MS SQL Server (2008) deals with indexes and caching. When a query runs, does it attempt to load all indexes referenced by the query into RAM? Does it leave those indexes in RAM after the query completes? Or are indexes in SQL
Server more like disk position descriptions -- they define the seek location for the disk storage?
Consider the following index report where the sizes are in KB. How much RAM would you expect would be necessary for a performant query on jb_mtblSourceGenericID? 2GB for that table alone?