We are in the process of migrating our databases to Enterprise Edition from Standard Edition. We are hosted on AWS and currently use instance types that have 128 gigs of memory (max usable with Standard Edition). As part of this migration, we will be moving to instance types that have 244 gigs of RAM.
The argument I've heard against creating a RAM drive for tempdb before is that you are better off letting SQL have as much memory as possible which will reduce the reliance on tempdb. But there are two things that make me question that...
- We use snapshot isolation mode which puts a heavy load on tempdb irrespective of server memory.
- When I query the buffer pools, it seems that we have more memory than SQL needs (buffer pool usage is about 120 gigs).
Has anyone done this with good (or bad) results. Are my arguments misguided?
Thanks,
Craig