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?

"Another database developer believes we are having memory issues on the server" -- based on what? How much RAM does the server have, what are the the instance memory settings, and how much memory is being consumed by the procedure cache? — Jon Seigel, Jul 05 '12 at 13:26
Based on extended query times and looking at Task Manager - which my research has indicated is a "dirty, filthy liar" (thanks Brent Ozar - http://www.brentozar.com/archive/2011/09/sysadmins-guide-microsoft-sql-server-memory/). I may find there is no memory issue - I'm following all suggestions provided in these comments and answers! — JHFB, Jul 05 '12 at 13:33
Since we're rolling the 8 ball here, I think the queries run slow because they were written by that 'another' database developer... — Remus Rusanu, Jul 05 '12 at 13:55

Thomas Stringer · Accepted Answer · 2012-07-06T12:21:01.423

13

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

edited Jul 06 '12 at 12:21

answered Jul 05 '12 at 13:13

Thomas Stringer

42,224
9
117
154

Decent (?) page life expectancy - 4234. I need to research the buffer cache hit ratio and look into the other parts. – JHFB Jul 05 '12 at 13:21
That PLE is not showing memory pressure at all. Anything over 1000 as a rule of thumb (of course, there's always "it depends") is acceptable. – Thomas Stringer Jul 05 '12 at 13:26
Buffer cache hit ratio can be very misleading, or out and out useless. See Great SQL Server Debates: Buffer Cache Hit Ratio by @JonathanKehayias. – Mark Storey-Smith Jul 05 '12 at 13:26
@MarkStorey-Smith Noted, thanks for the pointer! – Thomas Stringer Jul 05 '12 at 13:33
@JHFB Let's take a step back. What makes you think you are having memory pressure? – Thomas Stringer Jul 05 '12 at 13:33
I'm going to formulate another question, since the specific question I asked here has been addressed. Thanks, Shark! – JHFB Jul 05 '12 at 13:35
@JHFB Ok, glad to help! I'll check out that other question and see if I have any helpful input on that as well. – Thomas Stringer Jul 05 '12 at 13:37

score 7 · Answer 2 · answered Jul 05 '12 at 13:17

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.

Do indexes consume memory?

2 Answers2

Linked