1) What build of sql server are you on?

2) How, EXACTLY, are you determining how much memory the SQL Server windows service is using?

3) Have you run dbcc memorystatus and...

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...

15-20 on CTFP

Number of PHYSICAL cores in each NUMA node for MAXDOP (or lower). If it is a pure OLAP box you may benefit from higher.

Remember you can override...

Synchronous Database Mirroring is your option on SQL 2014 Standard Edition.

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...

I have come across a few clients over the years that had built their own "column-based" auditing. In every case the overhead of such systems (regardless of how they did...

Maybe this?

UPDATE ... SET field = @string1 + cast('' as varchar(8000)) + @string2

Your max memory setting is too high for a 64GB box. BUT there is NO formula that can be right for all servers. Different people have different stuff running on...

do a web search for: itzik ben-gan gaps and islands

1) Never, EVER use SSMS to do ANY form of DDL activity!!! You can use it to get things set the way you think you want them, but then you...

Grant Fritchey (4/26/2016)

I doubt it, but...

The engine has a WAITFOR DELAY '00:02:23' built into it that...

Thanks for your answer, one last question i would like to know is, if SQL Server like to consume whole available memory then why it is not consuming whole RAM...

Is TF 8649 supported now? If not (or even if so) it could be better to use Adam Machanic's spiffy high-CPU-estimate trick: http://sqlblog.com/blogs/adam_machanic/archive/2013/07/11/next-level-parallel-plan-porcing.aspx

Glad to hear it was a win all around. I just wanted to point out that is often isn't. 🙂