SQL Server: CMEMTHREAD, High Wait Values and Solution

Wait stats is the first place when where we start analyzing health of a production database server. Recently, we have found that a new database production server is not performing up to mark and queries response getting slower and slower in peak hours.

On executing, well known query by Paul Randal to get wait stats, we have found that a time for strange wait “CMEMTHREAD” is too high for said server.

According to BOL “CMEMTHREAD, occurs when a task is waiting on a thread-safe memory object. The wait time might increase when there is contention caused by multiple tasks trying to allocate memory from the same memory object.”

On trying a lot, but totally in vain, I thought asking Paul Randal would be better, as his blog on SQLSkills is one of the big resources from where I have learned about waits and wait types. Paul replied that “My guess is ad hoc plans being inserted into the plan cache. Try turning on 'optimize for ad hoc workloads'.” (That’s what we have already tried)

Skimming through articles and forums, I came a across to Microsoft support team article http://support.microsoft.com/kb/2492381/en-us. Which stats that, it could be occurring due to a bug in SQL Server 2008 R2. On said production server, we have found that NO service pack is installed and it still contains RTM.

Without any second thought we have created a ticket for upgradation to ServicePack2 and after that we have found that problem is resolved and server start working normally.