Why is an entire index that is infrequently used stored in the buffer pool?

Question

I have been looking at the memory usage of a server and noticed something odd when drilling into the details. One index is eating up ~15GB of the buffer pool. That happens to be the size of the entire index. A few more notes on this specific index:

• It is not a compound index. Only one field is included
• That field has relatively low cardinality (55 distinct values)
• The index exist on a table with half a billion records
• The sys.dm_db_index_usage_stats DMV shows 51 combined user_scans and user_seeks
• The sys.dm_db_index_usage_stats DMV also shows 1,159,987 user_updates

Leading to a few questions:

1. Why would this infrequently used index be taking up so much space in the buffer pool?
2. Doesn't the high number of user_updates compared to the low number of scans and seeks make this a candidate for removal due to maintenance overhead?
3. Should a field with such low cardinality be indexed in the first place?
4. Is the only way to clear this from the buffer pool to drop and recreate? Clearing the entire cache or buffer pool is obviously not possible on a production box

I am a developer trying to be a DBA, so bear with me ;)

Answer 1

Don't blindly rely on the sys.dm_db_index_usage_stats as it does not tell you the entire story.

Refer to Joe Sack's

• Exceptions – what sys.dm_db_index_usage_stats doesn’t tell you
• Exceptions–what sys.dm_db_index_usage_stats doesn’t tell you (Part II)

Why would this infrequently used index be taking up so much space in the buffer pool?

In simple terms, SQL Server tends to rely on Buffer Pool to do its normal operations to avoid expensive disk I/O's. If you have enough RAM and have set up MAX Memory appropriately, then all the queries that are ran across your database will CACHE the objects that are required by your queries.

Doesn't the high number of user_updates compared to the low number of scans and seeks make this a candidate for removal due to maintenance overhead?

It can be the case where the index from your perspective is not useful, but there might be queries that require that index or part of that Index.

I agree that caching such a huge index and if it is really not useful - is a waste of Buffer Pool and you might run into performance issues like more I/O's or excessive plan recompilations if the plan cache is too constrained.

Should a field with such low cardinality be indexed in the first place?

Not really as maintaining an index - it costs you for modifications like insert, update and deletes as well as disk space. Also, low cardinality indexes might be ignored by the Query Optimizer as it wont help generate an efficient query plan - its not just selective enough.Try to look into Filtered indexes.

Is the only way to clear this from the buffer pool to drop and recreate? Clearing the entire cache or buffer pool is obviously not possible on a production box.

Unfortunately, DBCC DROPCLEANBUFFERS does not have any parameters for specific database or objects.

Best is to rebuild the index.

Answer 2

SQL Server has some kind of LRU (least recently used) mechanism for dropping stuff from the buffer pool. The thing is, your index is used - it's being updated whenever the data is changed, and that's why it's still in the buffer pool.

Most likely the index is not used by queries because of its low cardinality. Such an index can be justified if it "covers" queries and prevents them from performing a key lookup from the clustered index, but if this is not the case, and assuming you look at a real load, there's a good chance the index can be removed. Of course - drop the index only after a careful check..