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
Best Answer
That's not exactly true. There are lots of things that can cause tempdb usage:
#tempTableName
or##tempTableName
). I imagine this is not your problem thoughIt'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.