sqld-_-ba (7/26/2016)
SQL 2012 SP1 (11.0.5343), VM + SAN , 16 CPU cores, 96GB RAM, 16 tempdb files 2GB equally sized, TF 1118 set, MAXDOP 1, size of databases ~100GBThere is heavy tempdb activity and CPU is at 100% - http://i.imgur.com/6mxez1v.jpg
We hot-added RAM from 64 to 96GB and reconfigured after changing max mem.
I've usually see SQL gobble up all memory available to it. And assumed adding RAM would alleviate tempdb disk usage a bit.
But memory use of sql is still only ~62 GB (same as it was when OS RAM was 64GB). And sustained ~300MBps of disk activity (50% reads, 50% writes).
Wait stats :
wait_typewait_time_secpercentSOS_SCHEDULER_YIELD127030416
CXPACKET102162913
PAGELATCH_UP6316418
Kind of a naive question... Why does SQL not use all RAM available to it, and alleviate tempdb usage ?
1) Most likely cause is you are on Standard Edition of SQL Server. And on 2012 and below the limit is 64GB. If you upgrade to 2014 or 2016 you can do 128GB of RAM for Standard Edition. This is the single most important win for upgrading IMNSHO.
2) Why maxdop 1?
3) I would be willing to bet you don't need 16 files for tempdb. One-per-CPU-core is bad advice for most systems, despite the fact that it comes from Microsoft (among others).
4) Having done SQL Server performance tuning professionally for 20 years or so I am also absolutely certain that you need to tune your database application(s) to get the CPU down. And there are probably quite a number of instant-win and low-hanging-fruit items that will give you a BIG pop in performance. Find bad stuff, fix, iterate...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
