February 18, 2015 at 4:49 am
I'm just beginning to experiment with memory optimised tables.
I have two sets of near identical tables - one set normal, the other set memory optimised with DURABILITY=SCHEMA_ONLY - and am running test queries against these. When I say that the two sets are "near identical", I mean that they are the same except for the primary keys: for the normal tables these are defined as PRIMARY KEY CLUSTERED whereas for the memory-optimed ones they are defined as PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=nnnn) as per the requirements for such tables.
I then run a pair of test queries, again identical but one referencing the normal tables and the other referencing the memory optimised ones.
(The query uses an inner join on three tables with row counts of approx 3m rows, 100000 rows and 5000 rows.)
Surprisingly - well it surprised me! - the query against the normal tables runs noticeably faster than that against the memory optimised ones. To try to find out why, I examined the execution plans. the plan for the memory optimised query suggests that I have a missing index: but of course I can't create this againsty a memory optimised table. Is this a bug or am I missing something? Are there any other pointers as to why the performance betwen the two should be so different?
February 18, 2015 at 7:14 am
I'd love to see the plan that indicates a missing index. I've had a hard time getting interesting plans out of the memory-optimized queries.
You can create indexes. There are non-clustered indexes available to the memory optimized tables. You do have to create them when you create the table since you can't use an ALTER statement.
I've seen queries that do run slower with memory optimized tables. They're not a panacea that fixes all ills. Further, the real speed improvements comes when you use compiled procedures against the memory optimized tables.
For the most part, I see real use for the memory optimized tables in two places. If you have excessive IO_LATCH waits, you will benefit from moving tables into memory. Also, if you need to work with table variables, creating these as memory optimized (which you can do) will give you substantial performance benefits. Other than that, I'm not sure that, at this time, it's worth it for any other workload.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply