3 ms·
Ah, temporary tables. Tell me, does SQL Server still have a single shared tempdb that is used by ALL databases on the server? If so, I can't understand why. Ha
by chris_wot 13y ago
Ah, temporary tables. Tell me, does SQL Server still have a single shared tempdb that is used by ALL databases on the server?
If so, I can't understand why. Hash Joins? Often uses tempdb. Snapshot Isolation (otherwise known as multi version consistency by every othe major database vendor)? Uses tempdb. Reindexing? Uses tempdb. Temp tables, cursors or user objects used across your databases? Uses tempdb.
I can't for the life of me understand why Microsoft built in this bottleneck into their database server!
- hobs 13y agoYep, though you can add additional data files to reduce SGAM contention. (Thank you Brent Ozar)
- BrentOzar 13y agoHAHAHA, you're welcome, sir.
- chris_wot 13y agoDon't mean to shoot the messenger here, but if SGAM contention happens due to high tempdb usage, surely that's an architectural problem? From my reading this only happens when a lot of user objects are allocated - again in the tempdb! I'd love to know why Microsoft do so many things in a shared resource. In Oracle you can setup multiple temporary tablespaces, curious to see what Postgres does.