3 ms·

All tables in sqlserver will be memory resident if there is available ram. But it happens dynamically, or is non-deterministic if you like. I assume the in me

• BrentOzar 13y ago
  > All tables in sqlserver will be memory resident if there is available ram. Right, but Hekaton introduces the option of never requiring your tables to hit the disk if you don't want them to. Think data warehouse staging tables, web session state, or data marts that are easily recreated from source data.
• corresation 13y ago
  For sure, and a common strategy when you have large amounts of RAM is to pre-emptively prime the cache by doing unnecessary selects of all of the principal data and index sets. However the issue I'm talking about is primarily churn -- the writing to disk that is often the weak link in larger data systems. Many completely banal queries generate potentially enormous loads on the tempdb, without the user ever knowing otherwise. And if you're using temporary working tables for large-scale processes (this is common in financial data where based upon the requested data you need to build up from the base data, climbing the mountain until you yield the final rendered resultset. In between there may be hundreds of GBs of churn to tempdb). And then there's just generally ephemeral data that never, ever needs to live through a server restart, and that you never want fighting for the probably very limited IO.