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