Post reply

CMEMTHREAD Waits

  • May 13, 2020 at 12:12 am

    #3751019

    There seems to be very little information around this wait type, besides the basic description, there's almost nothing about what actually causes it, and what can be done to resolve/reduce it. The server is SQL Server 2014 SP3 CU4, Enterprise Edition, and has 32 cores (possibly hyperthreaded, but I think not) and 265GB RAM, and the main database is 3.9TB.

    So far I've found the Trace Flag 8048 which splits memory allocation from NUMA to CPU, which the client has already implemented based on Microsoft's recommendation. They have also gone to MAXDOP = 1 which provides some help, but they are still seeing a massive amount of CMEMTHREAD waits.

    My evaluation of the server is there is insufficient memory which is exacerbated by a number of other problems. Potentially it could cope with this amount of RAM if the problems below were resolved.

    • Massive Index Fragmentation (400+ tables over 50% fragmented, some very large. A lot of tables 80%+ fragmented)
    • Statistics either very out of date or with poor sampling rates (153 Million row table being sampled at 2%, some stats are 6 months old) They aren't using TF2371.
    • Wasted space in memory - I analysed the dm_os_buffer_descriptors DMV for free_space_in_bytes and found some indexes were consuming 1.8GB of RAM, but 1.6GB was free space (this was the worst, but others were also significant). My understanding is this is a result of the high fragmentation.
    • Low Page Life Expectancy at 900 seconds.
    • Very high SQL Page Reads/sec (8,100+)
    • Very high SQL Page Writes /sec (1,100+)
    • High Lazy Writes /sec (88+)
    • MAX SQL Server Memory may be a bit high. While I'm reticent to reduce the buffer size, they have allocated 245,760MB of RAM to SQL, leaving only 16GB to tho OS based processes. 16GB seems a bit low for this environment.
    • Poor indexes and index use. Lock Requests per sec are in the 300,000 range, against 600 batch requests per second.
    • They use Service Broker which i think the vendor has not configured very well (SB Threads vs Batch sizes)

    I don't consider this a very busy server at 400 - 800 user connections and 600 BR/Sec.

    Besides the points I've raised above, what else should I be looking at to identify the cause of the CMEMTHREAD waits? And are the points above even valid causes of this problem? The current DBA insists that the CMEMTHREAD Waits are the cause of their problems, but I'm trying to explain that waits are actually a symptom of other problems before they become a cause of further issues.

    Any and all input and criticism welcome.

    Leo
    Nothing in life is ever so complicated that with a little work it can't be made more complicated.

  • May 13, 2020 at 7:45 am

    #3751072

    have a look at the following links - they contain some good information even if not exactly what you are asking for.

    and you may have seen some of them already.

    https://sqlonice.com/unsung-sqlos-the-sos_unfairmutexpair-behind-cmemthread-waits/

    http://andreas-wolter.com/en/where-is-that-preemptive-wait-coming-from/

    https://www.sqlshack.com/sql-server-wait-type-cmemthread/

    https://www.brentozar.com/blitz/poison-wait-detected/

    Memory may be ok on that server - but only if statistics are up to date and if indexes/code is looked at

    MAXDOP = 1... again same thing - if everything else is OK there is no need to set it to 1 for the majority of cases.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply