Sql-server – SQL Server Buffer Pool Utilization under VMWare

As I see, you're using a 32 bit system, so please follow the information from this MSDN article: Do I have to assign the Lock Pages in Memory privilege for Local System?. It's from MS's own CSS team.

Quote from it: "If you are using 32bit systems, the messages and algorithm are slightly different. The message you should look for in the ERRORLOG is the following:

Address Windowing Extensions is enabled

If the privilege was not set correctly, you will see this message:

Could not use Address Windowing Extensions because the 'lock pages in memory' privilege was not granted.

These messages will NOT show up for 32bit systems unless the configuration value 'awe enabled' is set to 1. The second message can show up if the "Lock Pages in Memory" privilge was changed after successfully configuring AWE and then the privilige was cleared OR you used RECONFIGURE WITH OVERRIDE and the privilige was not set. "

Another detailed article regarding "Lock pages in memory" is from well known sql writer Glenn Berry: SQL Server and the “Lock pages in memory” Right in Windows Server.

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.