It's probably debatable in this case but from what I know of Postgres (as one example) the general thinking seems to be "throw as much (relatively cheap) RAM at it as you can", tune some of the default (conservative) memory consumption params, and let Postgres eat the RAM.
See the various parameters here[0] - it's complicated but from my understanding you can pretty quickly run into performance issues depending on some not-exactly obvious variables in dataset size, specific queries, etc.
Of course Postgres != SQL Server but the concepts are likely similar. That said you won't catch me every researching this because I've never used SQL Server and never will :).