3

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:

tempDB

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:

3
  • What is your Max Server Memory set to for the instance?
    – rvsc48
    Jan 30 '19 at 20:24
  • 1
    Could you add some benchmarking queries and if possible the execution plans? You could add them to pastetheplan.com. Tempdb is not specifically used when under memory pressure, it could be used if bad estimates where made by the optimizer, temp objects are used, row versioning, ... All of these will use tempdb, even if you aren't under any memory pressure whatsoever. It is as people call it the public toilet of SQL Server, and many a things might get dumped in there :). Jan 30 '19 at 20:25
  • added examples. More information about this benchmark here: hammerdb.com/docs/ch09s01.html
    – Cpt.Whale
    Jan 30 '19 at 21:37
2

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.

1
  • sp_BlitzCache gave me the query (query #9 in the link) that was causing 99% of the tempdb read/write. I removed it and everything performs MUCH better.
    – Cpt.Whale
    Jan 31 '19 at 14:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.