CMEMTHREAD Wait type - need explanation and solution for this
-
Hi Everyone,
I am having a problem with a wait type in SQL Server i.e. CMEMTHREAD can anyone explain and give a solution for this.
I have only one tempdb datafile in tempdb on production server.
24 Core processors with I/O and Processor Affinity mask set to automatic,
Lot of CXPACKET Waits where MAXDOP is set to default 0. do i need to change this?
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Save a tree... Please don't waste paper unless you really need to!
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
Sorry, don't have time to go into cmemthread waits.
But CTFP of 5 is universally too low on modern SQL Servers. Without additional information I would go with 15 for OLTP box and 40 for OLAP box.
MAXDOP 0 is almost universally wrong. Upper limit should be number of physical cores on each NUMA node. You can override this per query with OPTION (MAXDOP NN) query hint.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (4/29/2016)
Sorry, don't have time to go into cmemthread waits.But CTFP of 5 is universally too low on modern SQL Servers. Without additional information I would go with 15 for OLTP box and 40 for OLAP box.
MAXDOP 0 is almost universally wrong. Upper limit should be number of physical cores on each NUMA node. You can override this per query with OPTION (MAXDOP NN) query hint.
Changing MAXDOP from 0 to the value you have mentioned will make any difference in this case?
-
info.sqldbamail (4/29/2016)
Hi Everyone,I am having a problem with a wait type in SQL Server i.e. CMEMTHREAD can anyone explain and give a solution for this.
I have only one tempdb datafile in tempdb on production server.
24 Core processors with I/O and Processor Affinity mask set to automatic,
Lot of CXPACKET Waits where MAXDOP is set to default 0. do i need to change this?
CMEMTHREAD is basically showing threads are waiting for Memory. Queries\Connections are consuming lot of memory. You can increase TempDB files to 8.
Whats your Min and Max memory settings?
http://www.connectsql.com/2012/11/sql-server-cmemthread-high-wait-values.html
-
we have 64 GB of Physical RAM, we had set 50 GB as Max and 4 GB as Min, and even we are facing an issue that SQL Server is exceeding the Max memory setting and occupying whole memory,
We have set only one tempdb datafile, where it was set by previous DBA, where we have 24 core processors and we have increase tempdb datafile.
I have one doubt do we need to disable autogrowth for tempdb and set equal size of datafiles? can i know the reason please.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
info.sqldbamail (5/1/2016)
where we have 24 core processors and we have increase tempdb datafile.I hope not to 24.
I have one doubt do we need to disable autogrowth for tempdb and set equal size of datafiles? can i know the reason please.
Disable autogrow, no. Set the data files to the same size, yes, so that proportional fill algorithm will fill all files the same way. Set the autogrow amounts to the same size as well, and make sure they and the starting sizes are sensible for the size TempDB reaches.
Not that this has any relevance to your memthread waits.
CMemThread waits are waits for a thread-safe memory object. Last time I saw heavy memthread waits was on SQL 2005 with the (long-since fixed) token store problem.
You've going to have to dig into the memory DMVs and other memory-related monitoring and see if you can identify what the root cause of the memthread waits are.
As for CXPacket, the main cause of those, in my experience, is inefficient queries and/or poor indexing. Go identify the queries which use lots of CPU and tune them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks to every one who answered my questions by taking time.
Okay can you please suggest me how much sizes of tempdb data files(Max = 8) should be added for a 300 GB production database with 24 core processors,
I want to know how much disk drive space should be allocated and how much sizes to data files in SQL server should be allocated with auto growth.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
When life puts into problem don't say 'God Why Me' say:w00t: 'Try Me'
-
info.sqldbamail (5/1/2016)
Okay can you please suggest me how much sizes of tempdb data files(Max = 8) should be added for a 300 GB production database with 24 core processorsAbsolutely no idea.
You need to check your stats and see what size your TempDB database typically grows to under your workload. Use that to determine the starting size for your TempDB files.
As for how many files? Are you seeing allocation contention in TempDB? If not, then you don't necessarily have to add any additional files at all. If you are seeing allocation contention, then try 4, see if there's still allocation contention and increase the file number if there is.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail is spot on as usual.
I have been consulting on SQL Server since the mid-1990s and I can count on two hands the number of times I have seen CMEMTHREAD waits at a client. I love it when I see them because I don't get to see interesting stuff much and it is always fun debugging them! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (5/3/2016)
Gail is spot on as usual.I have been consulting on SQL Server since the mid-1990s and I can count on two hands the number of times I have seen CMEMTHREAD waits at a client. I love it when I see them because I don't get to see interesting stuff much and it is always fun debugging them! 😎
Well in my case, what happened was scalar UDF's in 300 views on a Datawarehouse, running sql 2005.
Scalar functions running on billion row tables :w00t::hehe:
CMEMTHREAD was not an issue until someone started profiler, then everything slowed down to a crawl, CMEMTHREAD became top wait type.
Replaced all Scalar UDF's (300 views), and CMEMTHREAD waits disappeared never to return.
So I first look at code before messing around with settings.
-
MadAdmin (5/3/2016)
TheSQLGuru (5/3/2016)
Gail is spot on as usual.I have been consulting on SQL Server since the mid-1990s and I can count on two hands the number of times I have seen CMEMTHREAD waits at a client. I love it when I see them because I don't get to see interesting stuff much and it is always fun debugging them! 😎
Well in my case, what happened was scalar UDF's in 300 views on a Datawarehouse, running sql 2005.
Scalar functions running on billion row tables :w00t::hehe:
CMEMTHREAD was not an issue until someone started profiler, then everything slowed down to a crawl, CMEMTHREAD became top wait type.
Replaced all Scalar UDF's (300 views), and CMEMTHREAD waits disappeared never to return.
So I first look at code before messing around with settings.
SWEET!! Scans on billion-row fact tables with ONE THREAD due to Scalar UDF's voiding the use of parallelism! Yet another example of reality validating my "Death by UDF" chapter title for the SQL Server MVP Deep Dives 2 book! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply