Can’t I just add a bunch of memory to my SQL Server instance and have the same performance as using in memory tables? It turns out that the answer is no. Adding memory, if your database can use it, will often help performance. Everything SQL Server does to a database file is created initially in memory, and then written to disk. So, there is a good probability that adding memory can gain performance. But, no matter how much memory you add, even if your whole database can fit in memory, the performance will not be the same as using In Memory Tables.
The reason for this is straight forward. Table data cached in memory is restricted to the organizational structures of the data as it is stored on the physical disk. In addition, locks are used to enforce ACID transactions, isolating change from one user to another. In memory tables use a completely different data structure. It maintains multiple instances of the same data for a record concurrently, and grants access to the appropriate instance for different connections, thus eliminating the need for locking records. ACID transactions are still provided without the overhead of locking.
Because you are allocating disk space for database tables, objects have to fit into pages and extents, etc. which provide no value in a memory data structure, when all data is readily accessed. It doesn’t require segmenting of data to match the physical aspect of reading and writing to a drive. In Memory tables use a different, unique data structure where the first record and last record of a table are readily available without a lot of gymnastics walking to pointers to the next page or extent.
I was thinking you could even use the old DBCC Pin Table command to force data to be retained in Memory from a physical table, just to be sure you are getting a desired boost. I came across a Paul Randall blog saying that he disabled the DBCC Pin Table command in SQL Server 2005. It returns a message of success, but doesn’t actually do anything anymore.
If Microsoft brings the In Memory Table capability down to the standard version it’s likely to be considered more often. For now, it remains for those with the big budget.
Cheers,
Ben