SQL Server 2016 with high tempdb usage and low RAM usage

Question

I've got a small instance of SQL Server Standard that I'm running HammerDB TPC-H (1 GB) benchmarks on. It's got some configuration issue though where it's writing everything into tempdb on disk, and only tempdb which seems to be bottlnecking me as my physical storage is pretty slow:

The database by itself is about 3.5 GB, and the server has 16 GB RAM.

My understanding is that tempdb is only used when the server is under memory pressure, but it never even hits 30% usage. Physical_memory_in_use_kb is never much more than 3 GB, though it steadily grows over time. The VMware host is not having any obvious trouble either.

sp_blitz results warn about high CPU usage (~75-85%) and not much else.

This is an almost default installation of SQL Server Standard in a cluster - Is this behavior of writing to tempdb normal in the case of these longer-running benchmark queries? If not, where can I start troubleshooting?

In response to Comments:

• Max memory is set to 14336 GB in Server Properties > Memory
• Relevant hammerdb code with tcph queries: https://pastebin.com/AwJ000Qp
• Example planfile (query #18 above): https://www.brentozar.com/pastetheplan/?id=B1CkC9kE4

Answer 1

My understanding is that tempdb is only used when the server is under memory pressure

That's not exactly true. There are lots of things that can cause tempdb usage:

• the most obvious would be explicitly writing data to local or global temporary tables (#tempTableName or ##tempTableName). I imagine this is not your problem though
• version store activity (for instance because of queries using snapshot isolation or RCSI)
• there are a few execution plan operators that can "spill" to tempdb

It's likely that your problem is spills. For some good general information, and links to specific troubleshooting steps, check out this answer from Paul White to the question What frequency of hash/sort spills into tempdb is concerning?

You could also run sp_BlitzCache @SortOrder = 'spills' (from the First Responder Kit) to find some of the most problematic queries if they are still in your plan cache.

As far as dealing with the spills, you could post separate questions about the bad queries and probably get help on resolving whatever is causing the spills (bad cardinality estimates, small memory grants, etc).

Memory pressure can lead to low memory grants, which can lead to tempdb spills - which is why I said your initial assertion about memory pressure is not exactly true - because it is true sometimes!

---

The execution plan you provided is an estimated execution plan, so it doesn't indicate if any operators spilled to tempdb. But it does have multiple memory-consuming operators (sorts in particular), so it has the potential to generate tempdb activity.