5 ms·
> I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)... http://msdn.microsoft.com/en-us/library/cc645
by BrentOzar 13y ago
> I assume that the linked blog intended to link to http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120)... http://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).....
Great catch - fixed, thanks.
> greatest benefit being pushed for SQL Server 2014 is in memory tables (which is something that SQL had back in the 6.5/7.0 era, but then had removed)
Sorta kinda - back then, we had DBCC PINTABLE, and it would cache the data, but it would still require that the transaction logs and data pages be written to disk. Now with Hekaton, you can specify that a table never hits disk, ever. (Don't get me wrong, I don't think people are going to adopt that particular feature due to other limitations around datatypes and supported syntax.)
- corresation 13y agoI 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.