In-Memory OLTP – ORDER BY Behavior Clause using range index

Posted in: Microsoft SQL Server, Technical Track

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.

hash_index
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.

range_index
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’

table_info
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’

index_info
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

plan_1

 

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

plan_2

 

sort_operation

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!

 

plan_comparison

 

 

email

Author

Interested in working with Luan? Schedule a tech call.

About the Author

Based out of Brazil, Luan Moreno credits his ability to solve problems to thinking critically before acting. He values working with a team because he and his clients can benefit from various perspectives and collaboration, particularly when faced with difficult issues. When Luan first became interested in technology, he didn’t have a mentor, so he made a commitment to teaching others in the community and sharing his knowledge through blogging, speaking engagements, and more. When he isn’t working, Luan can be found studying various topics and reading biographies.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *