5 ms·
I was not at all clear, but sorry I was speaking of temporary tables, or basically anything intended to be ephemeral. Back in the 6.5 days you could flag tempdb
by corresation 13y ago
I was not at all clear, but sorry I was speaking of temporary tables, or basically anything intended to be ephemeral. Back in the 6.5 days you could flag tempdb as being RAM resident automatically - http://support.microsoft.com/kb/115050 http://support.microsoft.com/kb/115050. Load up some global temp tables on db startup and rock and roll.
It made tremendous sense because that's often an area of enormous churn (I saw a ServerFault post or something where someone did this and saw 30 iops to it, wondering why it didn't improve their system. That is nothing. One product I worked on saw literally hundreds of thousands of IOPS, and everything that sped tempdb sped the world).
- chris_wot 13y agoAh, 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.