Do indexes consume memory?

Question

I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is taking up the lion's share of space in the buffer pool. Looking further, using sys.allocation_units and sys.indexes, I confirmed this is likely caused by the heavy use of indexes in the database. Most indexes are clustered.

Another database developer believes we are having memory issues on the server - that queries are starting to run long because there is no available memory.

My question here is - does the use of these indexes, and their existence in the buffer pool, take away memory available for other processes?

Answer 1

Yes, the data pages of a used index that are cached in the buffer pool will be taking up space in the data cache. But don't let that turn you away from using indexes (first off, a clustered index is the actual table data so keep that in mind as well). The use of indexes (properly designed and implemented, of course) is a good thing.

Your memory issues are most likely not from having indexes on your tables. Dive into the memory issues, what exactly are the problems? Are you having a low Page Life Expectancy? How is your memory configured on the server? Is the max server memory to low restricting the size of the buffer pool?

To get a breakdown of the index pages in your data cache, you can run the below query:

select
    count(*) as total_page_count,
    count(*) * 8 as total_consumption_kb,
    sum(row_count) as total_row_count
from sys.dm_os_buffer_descriptors
where page_type = 'INDEX_PAGE'
group by page_type

To get these stats by database:

select
    db_name(database_id) as database_name,
    count(*) as total_page_count,
    count(*) * 8 as total_consumption_kb,
    sum(row_count) as total_row_count
from sys.dm_os_buffer_descriptors
where page_type = 'INDEX_PAGE'
group by database_id
order by total_consumption_kb desc

Answer 2

Indexes consume buffer pool space, yes. This is one more reason why you should take care with your indexing strategy and minimise duplicates.

I confirmed this is likely caused by the heavy use of indexes in the database. Most indexes are clustered.

Remember that a clustered index is the table. The only overhead that exists for a clustered index over and above that for a heap (which is generally undesirable) is for the non-leaf index pages and the cluster key's inclusion in all non-clustered indexes for that table. This is why narrow cluster key's are preferred.

Kimberley Tripp's articles on clustered key choices are an excellent reference for this.