Some clients are beginning to thinking about the possibilities of migrating some disk tables to In-Memory tables — this process is not so simple. Migrating tables requires a new mindset as some things changed in the SQLOS and SQL Serve architecture.
It is extremely important to know about the differences, the scenarios that can be applied, and the non-supported aspects. First, take a look at the official link for the In-Memory Tables documentation – https://msdn.microsoft.com/en-us/library/dn133186%28v=sql.120%29.aspx
One of the things that we need to know about in In-Memory tables, is that this feature comes with two new types of indexes known as HASH INDEX and RANGE INDEX.
1. Hash Index – The Hash Index is perfect to use for equality purposes. For example, to search for a specific number and character.
Figure 1 – Hash Index Structure
2. Range Index – Basically the Range Index is perfect for range purposes, So for example search for a range of values and normally is more applied when used with date ranges – DATE, DATETIME and DATETIME2.
Figure 2 – Range Index Structure
When we realize the range index creation, a very important aspect is the ORDER of the data sort (ASC or DESC). Creating In-Disk Tables (Conventional Tables) the ORDER BY is not normally a big point of concern because the data pages are double linked — this difference becomes significant when you have more than one column in two different directions.
Another interesting aspect of the ORDER BY clause in In-Disk tables is the ORDERING creation and Parallelism usage, and you can check this interesting behavior here sqlmag.com/t-sql/descending-indexes
Now, let’s analyze the ORDER BY clause in In-Memory tables using the RANGE INDEX and check the behavior and the differences when using the ASC and DESC order in queries.
USE HktDB
go
sp_help ‘inmem_DadosAtendimentoClientes’
Figure 3 – Range Index = idxNCL_DataCadastro
Creating an In-Memory table with a NONCLUSTERED Range Index in Ascending Order.
CREATE TABLE [dbo].[inmem_DadosAtendimentoClientes]
(
INDEX [idxNCL_DataCadastro] NONCLUSTERED
(
[DataCadastro] ASC)
)
Grabbing information’s about RANGE INDEX
SELECT SIS.name AS IndexName,
SIS.type_desc AS IndexType,
XIS.scans_started,
XIS.rows_returned,
XIS.rows_touched
FROM sys.dm_db_xtp_index_stats AS XIS
INNER JOIN sys.indexes AS SIS
ON XIS.object_id = SIS.object_id
AND XIS.index_id = SIS.index_id
WHERE XIS.object_id = 50099219
AND SIS.type_desc = ‘NONCLUSTERED’
Figure 4 – Range Index Information
Execution 1 – Ordering using the ORDER BY ASC
SELECT *
FROM inmem_DadosAtendimentoClientes
WHERE DataCadastro BETWEEN ‘2005-02-15 18:58:48.000? AND ‘2005-02-25 18:58:48.000?
ORDER BY DataCadastro ASC
Execution 2 – Ordering using the ORDER BY DESC
SELECT *
FROM inmem_DadosAtendimentoClientes
WHERE DataCadastro BETWEEN ‘2005-02-15 18:58:48.000? AND ‘2005-02-25 18:58:48.000?
ORDER BY DataCadastro DESC
Sort of 80% in this query, Why ?
Analyzing the XML of the Execution Plan…
<OrderBy>
<OrderByColumn Ascending=”false”>
<ColumnReference Database=”[HktDB]” Schema=”[dbo]” Table=”[inmem_DadosAtendimentoClientes]” Column=”DataCadastro” />
</OrderByColumn>
</OrderBy>
What happened?
When we execute a query with the ORDER BY clause in Range Index column, we need to verify the order that was created – ASC or DESC. This happened because is this case I created the column ‘DataCadastro‘ with ASC order, this way the data is ordered in the ascending way and not in descending way, and talking about In-Memory tables the order MATTERS a lot, You just can benefit of the ORDER if the order that you searched is the same that the order that you created the tables, this happens because the data is stored in another way in-memory, this is a BY DESIGN consideration.
MSDN – “Nonclustered indexes (not hash indexes) support everything that hash indexes supports plus seek operations on inequality predicates such as greater than or less than, as well as sort order. Rows can be retrieved according to the order specified with index creation. If the sort order of the index matches the sort order required for a particular query, for example if the index key matches the ORDER BY clause, there is no need to sort the rows as part of query execution. Memory-optimized nonclustered indexes are unidirectional; they do not support retrieving rows in a sort order that is the reverse of the sort order of the index. For example, for an index specified as (c1 ASC), it is not possible to scan the index in reverse order, as (c1 DESC).”
Recommendation
Always realize the creation of the RANGE Index in the correct ordination that you want, in the most of times the most common ORDER pattern is the DESC, because normally you want to visualize and search the most recent data of your application or search for last transaction that you had in an specific date. You should always be careful about this because if you want to change the ORDER BY is necessary to DROP and CREATE again the table, In-Memory tables don’t enable the ALTER clause option.
You don’t want to see this in your environment, ever!
No comments