July 26, 2016 at 10:40 am
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 ~100GB
There 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_secpercent
SOS_SCHEDULER_YIELD127030416
CXPACKET102162913
PAGELATCH_UP6316418
Kind of a naive question... Why does SQL not use all RAM available to it, and alleviate tempdb usage ?
July 26, 2016 at 11:27 am
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
July 26, 2016 at 1:39 pm
1. should have mentioned, we use Enterpise (have SA with M$)
2. oltp box, see cxpacket waits, maxdop was 2 before, wasn't giving up much performance gains anyways, so changed to 1
3. used to have 8 tempdb files per many article's recommendations, until i saw very heavy contention GAM/PFS on tempdb.. increasing to 16 files removed contention instantly.
4. unfortunately, this is 3rd party software hosted on our site. we have recommended many optimization fixes in past for e.g. all those queries use table variables which perform badly for large no. of records vs temp tables. but we cannot change code.
July 26, 2016 at 2:03 pm
sqld-_-ba (7/26/2016)
1. should have mentioned, we use Enterpise (have SA with M$)2. oltp box, see cxpacket waits, maxdop was 2 before, wasn't giving up much performance gains anyways, so changed to 1
3. used to have 8 tempdb files per many article's recommendations, until i saw very heavy contention GAM/PFS on tempdb.. increasing to 16 files removed contention instantly.
4. unfortunately, this is 3rd party software hosted on our site. we have recommended many optimization fixes in past for e.g. all those queries use table variables which perform badly for large no. of records vs temp tables. but we cannot change code.
A) Sad that you have to deal with crappy ISV. Not the first and not the last to have that issue. 🙁 I have had a few wins getting them to pull their heads out of their butts over the years, one of them spectacularly so.
B) Too bad we don't have an option with a plan guide to switch declared table vars to temp tables!! I would pay good money for that. I can't count the number of times I have made that switch in client code and it been a magic bullet for performance.
C) I have often used NC indexing on 3rd party crapola to great effect. Yeah, it likely breaks your support agreement and you have to be willing to accept that. But with a script that can create and drop the ones you make you can just clean up before patching or making a support call and carry on. YMMV
D) If SQL Server isn't using extra memory then a) your VM is mis-configured and it really doesn't have it or it simply isn't needed for the data size. Temp objects will not really go to memory if they get a bajillion rows estimated to be put in them. Straight to disk is my understanding. So more RAM didn't help you out here with all those explicit table vars getting lots of rows. Try giving 200GB to the VM and see if that makes a difference.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 26, 2016 at 2:06 pm
Quick question, you have TF-1118, why not also TRACE FLAG 1117 – equal growth of files in a filegroup?
😎
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
