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 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 ;)

share|improve this question
1  
How much memory is in your server? What is the page life expectancy of the server? –  Max Vernon Feb 18 at 23:28
1  
The server has 72 GB of total memory with the max memory setting for SQL Server set to 64 GB. The size of all the databases running on the instance totals up to ~350GB and the workload is very moderate for the amount of memory being consumed it seems. Average page life expectancy for the DB in question is 7815 using the sys.dm_os_performance_counters DMV –  TechDawg270 Feb 19 at 15:20

2 Answers 2

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

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.

share|improve this answer
    
A co-worker seems to think it would be best to restart the service but doesn't that have a lot of pitfalls? If we restart the service to remedy problems then we effectively clear the plan cache and flush the metadata collected for DMV’s –  TechDawg270 Feb 20 at 17:08
1  
@TechDawg270 Restarting the service should be a very very last option. It will flush out buffer pool, plan cache and reset DMV data. Why cant you rebuild the index as I suggested. Rebuild - will drop and recreate the index and reorder the pages. If you have enterprise edition, then you can use REBUILD WITH ONLINE as well. Do it during your maintenance window. –  Kin Feb 20 at 17:21
    
I agree with rebuilding the index. It is just hard around here because a bunch of developers think they are capable DBAs, and that isn't the case for any of us –  TechDawg270 Feb 20 at 20:28

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..

share|improve this answer

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.