Database Reference
In-Depth Information
Figure 32-16.
Page merging: Third (final) step
Indexing considerations for range indexes are similar to those for on-disk nonclustered indexes. You should
remember, however, that in-memory OLTP uses binary sorting for the indexes, which is case- and accent-sensitive.
Finally, the
sys.dm_db_xtp_index_stats
view returns statistics for the indexes defined on memory-optimized
tables. Indexes on memory-optimized tables are recreated when SQL Server loads data into memory; therefore, the
statistics are collected and kept since that time. Some of the output columns are:
scans_started
shows the number of times that row chains in the index were scanned. Due
to the nature of the index, every operation, such as
SELECT
,
INSERT
,
UPDATE
, and
DELETE
requires SQL Server to scan a row chain and increment this column.
rows_returned
represents the cumulative number of rows returned to a client.
rows_touched
represents the cumulative number of rows accessed in the index.
rows_expired
shows the number of detected stale rows. We will discuss this in greater
detail in the “Garbage Collection” section.
rows_expired_removed
returns the number of stale rows that have been unlinked from
the index row chains. We will also discuss this in more detail in the “Garbage Collection”
section.
■
you can read more about the
sys.dm_db_xtp_index_stats
view at:
Note
Statistics on Memory-Optimized Tables
Even though SQL Server creates index- and column-level statistics on memory-optimized tables, it does not update
the statistics automatically. This behavior leads to a very interesting situation: indexes on memory-optimized tables
are created with the tables and, therefore, the statistics are created at the time when the table is empty and are never
updated automatically afterwards.
You need to keep this behavior in mind while designing a statistics maintenance strategy in the system. You
should update statistics after data is loaded into the table when SQL Server or the database restarts. Moreover, if the
data in a memory-optimized table is volatile, which is usually the case, you should manually update statistics on a
regular basis.
