Range Index–O Problema do ORDER BY

Posted on maio 30, 2014

0


Alguns clientes estão começando a pensar em migrar algumas suas tabelas para In-Memory, com isso hã vários paradigmas a se quebrar assim como alguns conceitos que precisam ser explicados com muito cuidado, irei mostrar um ponto que devemos ter muita atenção quando estamos trabalhando com tabelas in-memory, nesse caso especificamente com o RANGE INDEX.

Hash Index – (Ótimo para Equalidade)

hashindex

http://sqlblog.com/blogs/kalen_delaney/archive/2013/10/20/sql-server-2014-in-memory-oltp-hekaton-whitepaper-for-ctp2.aspx

Range Index (Ótima para Range)

image

http://sqlblog.com/blogs/kalen_delaney/archive/2013/10/20/sql-server-2014-in-memory-oltp-hekaton-whitepaper-for-ctp2.aspx

Quando realizamos a criação de um índice range um ponto muito importante é pensar em qual ordenação devemos criá-lo. Quando criamos um índice ON-DISK – Cluster ou Não-Cluster a ordenação com que o índice é criado não deve ser um ponto de preocupação *, pois as páginas são duplamente encadeadas assim ir de frente para trás ou de trás para frente DEVE ser sempre o mesmo custo, será mesmo ?

* http://sqlmag.com/t-sql/descending-indexes 

Iremos analisar a criação de uma tabela em memória, e verificar o que está acontecendo em relação a ordenação na consulta.

USE HktDB

go

 

sp_help ‘inmem_DadosAtendimentoClientes’

Screen Shot 2014-05-30 at 11.12.25 AM
(Figura 1 – idxNCL_DataCadastro – Range Index)

 

CREATE TABLE [dbo].[inmem_DadosAtendimentoClientes]

(

INDEX [idxNCL_DataCadastro] NONCLUSTERED

(

            [DataCadastro] ASC

)

)

(Figura 2 – Criação do Range Index – ASC)

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’

Screen Shot 2014-05-30 at 11.24.45 AM
(Figura 3 – idxNCL_DataCadastro – Range Index – Dynamic Management View – DMV)

Execução 1 – ORDENAÇÃO 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

Screen Shot 2014-05-30 at 11.27.22 AM

(Figura 4 – Plano de Execução – Utilização do Index Seek – Range Index – idxNCL_DataCadastro – ASC)

Screen Shot 2014-05-30 at 11.28.32 AM

Execução 2 – ORDENAÇÃO 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

 

Screen Shot 2014-05-30 at 11.37.17 AM

(Figura 6 – Plano de Execução – Utilização do Index Seek – Range Index – idxNCL_DataCadastro – DESC)

Screen Shot 2014-05-30 at 11.38.53 AM

SORT de 80% ?

Screen Shot 2014-05-30 at 11.52.20 AM

Analisando o XML do Plano de Execução da Consulta

<OrderBy>

    <OrderByColumn Ascending="false">

    <ColumnReference Database="[HktDB]" Schema="[dbo]" Table="[inmem_DadosAtendimentoClientes]" Column="DataCadastro" />

    </OrderByColumn>

</OrderBy>

 

What Happens ?

Quando executamos uma ORDENAÇÃO em um RANGE INDEX a mesma deve ser da mesma forma da criação da tabela, se ela for ASC então as pesquisas devem ser executadas de forma ASC, isso acontece proque o armazanamento dos registros em mémoria não são armazenados em páginas por isso uma vez criado a ordernação não é possível mudar. (BY DESIGN)

 

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).”

Recomendação !

Semrpe realize a criação de um índice RANGE com a ordernação CORRETA, na maioria das vezes a ordenação DESC é a mais utilizada, geralmente os relatórios e as aplicações desejam pesquisar pelos últimos registros, transações e assim por diante, até porque se você não gostaria de presenciar isso em seu ambiente………….

Screen Shot 2014-05-30 at 12.08.45 PM

(Figura 7 – Comparação das Consultas e a influência do ORDER BY DESC)

 

Sempre tome cuidado quanto está trabalhando com tabelas In-Memory, cada detalhe fará total diferença…….