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 recoverability