Database Reference
In-Depth Information
Figure 23-7.
An improved execution plan taking advantage of nonclustered indexes
As you can see, the nonclustered index was used instead of a table scan to improve performance much as you
would expect from an index on a standard table. However, unlike the standard table, while this query did pull columns
that were not part of nonclustered index, no key lookup was required to retrieve the data from the in-memory table
because each index points directly to the storage location, in memory, of the data necessary. This is yet another, small,
but important improvement over how standard tables behave.
Index Maintenance
There are many fundamental differences between how indexes get created with in-memory tables when compared to
standard tables. But index maintenance is still something you have to take into account. In-memory indexes maintain
statistics that will need to be updated. You'll also want information about the in-memory indexes such as whether
they're being accessed using scans or seeks. While the desire to track all this is the same, the mechanisms for doing so
are different.
You can't actually see the statistics on in-memory indexes. You can run
DBCC SHOW_STATISTICS
against the index,
but the output looks like Figure
23-8
.
Figure 23-8.
The empty output of statistics on an in-memory index
