Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
"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 5 '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 - brentozar.com/archive/2011/09/…). I may find there is no memory issue - I'm following all suggestions provided in these comments and answers! –  JHFB Jul 5 '12 at 13:33
2  
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 5 '12 at 13:55

2 Answers 2

up vote 8 down vote accepted

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
share|improve this answer
    
Decent (?) page life expectancy - 4234. I need to research the buffer cache hit ratio and look into the other parts. –  JHFB Jul 5 '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 5 '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 5 '12 at 13:26
    
@MarkStorey-Smith Noted, thanks for the pointer! –  Thomas Stringer Jul 5 '12 at 13:33
    
@JHFB Let's take a step back. What makes you think you are having memory pressure? –  Thomas Stringer Jul 5 '12 at 13:33

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.

share|improve this answer
    
+1 That Ms Tripp's articles are EPIC on clustering keys... –  Fabricio Araujo Jul 9 '12 at 19:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.