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:
- Why would this infrequently used index be taking up so much space in the buffer pool?
- 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?
- Should a field with such low cardinality be indexed in the first place?
- 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 ;)