nomenclatura - Orden de clasificación de un índice agrupado de SQL Server 2008+

indices en sql server 2014

Question

¿El orden de clasificación de un índice agrupado de SQL Server 2008+ afecta el rendimiento de la inserción?

El tipo de datos en el caso específico es integer y los valores insertados son ascendentes ( Identity ). Por lo tanto, el orden de clasificación del índice sería opuesto al orden de clasificación de los valores que se insertarán.

Mi conjetura es que tendrá un impacto, pero no sé, tal vez SQL Server tenga algunas optimizaciones para este caso o su formato de almacenamiento de datos interno sea indiferente a esto.

Tenga en cuenta que la pregunta es sobre el rendimiento INSERT , no SELECT .

Actualizar
Para ser más claros acerca de la pregunta: ¿Qué sucede cuando los valores que se insertarán ( integer ) están en orden inverso ( ASC ) al ordenamiento del índice agrupado ( DESC )?

Answer 1

De acuerdo con el código a continuación, la inserción de datos en una columna de identidad con un índice agrupado ordenado es más intensiva en recursos cuando los datos seleccionados se ordenan en la dirección opuesta al índice agrupado ordenado.

En este ejemplo, las lecturas lógicas son casi el doble.

Después de 10 ejecuciones, las lecturas lógicas ascendentes ordenadas promedian 2284 y las lecturas lógicas descendentes ordenadas promedian 4301.

--Drop Table Destination;
Create Table Destination (MyId INT IDENTITY(1,1))

Create Clustered Index ClIndex On Destination(MyId ASC)

set identity_insert destination on 
Insert into Destination (MyId)
SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
FROM sys.all_objects 
ORDER BY n


set identity_insert destination on 
Insert into Destination (MyId)
SELECT TOP (1000) n = ROW_NUMBER() OVER (ORDER BY [object_id]) 
FROM sys.all_objects 
ORDER BY n desc;

Más sobre lecturas lógicas si está interesado: https://www.brentozar.com/archive/2012/06/tsql-measure-performance-improvements/

Answer 2

El orden de los valores insertados en un índice agrupado ciertamente afecta el rendimiento del índice, al crear potencialmente mucha fragmentación, y también afecta el rendimiento del inserto en sí mismo.

He construido un banco de pruebas para ver qué pasa:

USE tempdb;

CREATE TABLE dbo.TestSort
(
    Sorted INT NOT NULL
        CONSTRAINT PK_TestSort
        PRIMARY KEY CLUSTERED
    , SomeData VARCHAR(2048) NOT NULL
);

INSERT INTO dbo.TestSort (Sorted, SomeData)
VALUES  (1797604285, CRYPT_GEN_RANDOM(1024))
    , (1530768597, CRYPT_GEN_RANDOM(1024))
    , (1274169954, CRYPT_GEN_RANDOM(1024))
    , (-1972758125, CRYPT_GEN_RANDOM(1024))
    , (1768931454, CRYPT_GEN_RANDOM(1024))
    , (-1180422587, CRYPT_GEN_RANDOM(1024))
    , (-1373873804, CRYPT_GEN_RANDOM(1024))
    , (293442810, CRYPT_GEN_RANDOM(1024))
    , (-2126229859, CRYPT_GEN_RANDOM(1024))
    , (715871545, CRYPT_GEN_RANDOM(1024))
    , (-1163940131, CRYPT_GEN_RANDOM(1024))
    , (566332020, CRYPT_GEN_RANDOM(1024))
    , (1880249597, CRYPT_GEN_RANDOM(1024))
    , (-1213257849, CRYPT_GEN_RANDOM(1024))
    , (-155893134, CRYPT_GEN_RANDOM(1024))
    , (976883931, CRYPT_GEN_RANDOM(1024))
    , (-1424958821, CRYPT_GEN_RANDOM(1024))
    , (-279093766, CRYPT_GEN_RANDOM(1024))
    , (-903956376, CRYPT_GEN_RANDOM(1024))
    , (181119720, CRYPT_GEN_RANDOM(1024))
    , (-422397654, CRYPT_GEN_RANDOM(1024))
    , (-560438983, CRYPT_GEN_RANDOM(1024))
    , (968519165, CRYPT_GEN_RANDOM(1024))
    , (1820871210, CRYPT_GEN_RANDOM(1024))
    , (-1348787729, CRYPT_GEN_RANDOM(1024))
    , (-1869809700, CRYPT_GEN_RANDOM(1024))
    , (423340320, CRYPT_GEN_RANDOM(1024))
    , (125852107, CRYPT_GEN_RANDOM(1024))
    , (-1690550622, CRYPT_GEN_RANDOM(1024))
    , (570776311, CRYPT_GEN_RANDOM(1024))
    , (2120766755, CRYPT_GEN_RANDOM(1024))
    , (1123596784, CRYPT_GEN_RANDOM(1024))
    , (496886282, CRYPT_GEN_RANDOM(1024))
    , (-571192016, CRYPT_GEN_RANDOM(1024))
    , (1036877128, CRYPT_GEN_RANDOM(1024))
    , (1518056151, CRYPT_GEN_RANDOM(1024))
    , (1617326587, CRYPT_GEN_RANDOM(1024))
    , (410892484, CRYPT_GEN_RANDOM(1024))
    , (1826927956, CRYPT_GEN_RANDOM(1024))
    , (-1898916773, CRYPT_GEN_RANDOM(1024))
    , (245592851, CRYPT_GEN_RANDOM(1024))
    , (1826773413, CRYPT_GEN_RANDOM(1024))
    , (1451000899, CRYPT_GEN_RANDOM(1024))
    , (1234288293, CRYPT_GEN_RANDOM(1024))
    , (1433618321, CRYPT_GEN_RANDOM(1024))
    , (-1584291587, CRYPT_GEN_RANDOM(1024))
    , (-554159323, CRYPT_GEN_RANDOM(1024))
    , (-1478814392, CRYPT_GEN_RANDOM(1024))
    , (1326124163, CRYPT_GEN_RANDOM(1024))
    , (701812459, CRYPT_GEN_RANDOM(1024));

La primera columna es la clave principal y, como puede ver, los valores se enumeran en orden aleatorio (ish). Listado de los valores en orden aleatorio debe hacer que SQL Server:

1. Ordenar los datos, preinserte
2. No ordenar los datos, lo que resulta en una tabla fragmentada.

La función CRYPT_GEN_RANDOM() se utiliza para generar 1024 bytes de datos aleatorios por fila, para permitir que esta tabla consuma varias páginas, lo que a su vez nos permite ver los efectos de inserciones fragmentadas.

Una vez que ejecute el inserto anterior, puede verificar la fragmentación de la siguiente manera:

SELECT * 
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(''TestSort''), 1, 0, ''SAMPLED'') ips;

Ejecutar esto en mi instancia de SQL Server 2012 Developer Edition muestra una fragmentación promedio del 90%, lo que indica que SQL Server no se clasificó durante la inserción.

Es probable que la moraleja de esta historia en particular sea ​​"en caso de duda, si será beneficiosa". Dicho esto, agregar una cláusula ORDER BY a una declaración de inserción no garantiza que las inserciones se produzcan en ese orden. Considere lo que sucede si la inserción va paralela, como ejemplo.

En sistemas que no sean de producción, puede usar el indicador de traza 2332 como una opción en la declaración de inserción para "forzar" a SQL Server a ordenar la entrada antes de insertarla. @PaulWhite tiene un artículo interesante, Optimizando consultas T-SQL que cambian los datos que cubren eso, y otros detalles. Tenga en cuenta que la marca de rastreo no es compatible y NO se debe usar en sistemas de producción, ya que esto podría invalidar su garantía. En un sistema que no sea de producción, para su propia educación, puede intentar agregar esto al final de la declaración INSERT :

OPTION (QUERYTRACEON 2332);

Una vez que haya agregado eso al inserto, eche un vistazo al plan, verá una clasificación explícita:

Sería genial si Microsoft hiciera de esto una marca de rastreo compatible.

Paul White me hizo saber que SQL Server introduce automáticamente un operador de clasificación en el plan cuando cree que será útil. Para la consulta de muestra anterior, si ejecuto la inserción con 250 elementos en la cláusula de values , no se implementa ninguna clasificación automáticamente. Sin embargo, con 251 elementos, SQL Server ordena automáticamente los valores antes de la inserción. Por qué el corte es de 250/251 filas sigue siendo un misterio para mí, aparte de que parece ser un código duro. Si reduzco el tamaño de los datos insertados en la columna SomeData a solo un byte, el límite es de 250/251 filas, aunque el tamaño de la tabla en ambos casos es solo una página. Curiosamente, mirando el inserto con SET STATISTICS IO, TIME ON; muestra las inserciones con un solo byte. SomeData valor de SomeData toma el doble de tiempo cuando se ordena.

Sin la clasificación (es decir, 250 filas insertadas):

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 16 ms, elapsed time = 16 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. Table ''TestSort''. Scan count 0, logical reads 501, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (250 row(s) affected) (1 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 11 ms.

Con la clasificación (es decir, 251 filas insertadas):

SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. SQL Server parse and compile time: CPU time = 15 ms, elapsed time = 17 ms. SQL Server parse and compile time: CPU time = 0 ms, elapsed time = 0 ms. Table ''TestSort''. Scan count 0, logical reads 503, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table ''Worktable''. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (251 row(s) affected) (1 row(s) affected) SQL Server Execution Times: CPU time = 16 ms, elapsed time = 21 ms.

Una vez que comience a aumentar el tamaño de la fila, la versión ordenada sin duda se volverá más eficiente. Cuando SomeData 4096 bytes en SomeData , la inserción ordenada es casi dos veces más rápida en mi equipo de pruebas que la inserción sin clasificar.

Como nota al margen, en caso de que esté interesado, VALUES (...) cláusula VALUES (...) usando este T-SQL:

;WITH s AS (
    SELECT v.Item
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Item)
)
, v AS (
    SELECT Num = CONVERT(int, CRYPT_GEN_RANDOM(10), 0)
)
, o AS (
    SELECT v.Num
        , rn = ROW_NUMBER() OVER (PARTITION BY v.Num ORDER BY NEWID())
    FROM s s1
        CROSS JOIN s s2
        CROSS JOIN s s3
        CROSS JOIN v 
)
SELECT TOP(50) '', ('' 
        + REPLACE(CONVERT(varchar(11), o.Num), ''*'', ''0'') 
        + '', CRYPT_GEN_RANDOM(1024))''
FROM o
WHERE rn = 1
ORDER BY NEWID();

Esto genera 1,000 valores aleatorios, seleccionando solo las 50 filas superiores con valores únicos en la primera columna. Copié y pegué la salida en la INSERT anterior.

Answer 3

Hay una diferencia. La inserción fuera del orden de clúster provoca una fragmentación masiva.

Cuando ejecuta el siguiente código, el índice agrupado de DESC está generando operaciones de ACTUALIZACIÓN adicionales en el nivel NONLEAF.

CREATE TABLE dbo.TEST_ASC(ID INT IDENTITY(1,1) 
                            ,RandNo FLOAT
                            );
GO
CREATE CLUSTERED INDEX cidx ON dbo.TEST_ASC(ID ASC);
GO

CREATE TABLE dbo.TEST_DESC(ID INT IDENTITY(1,1) 
                            ,RandNo FLOAT
                            );
GO
CREATE CLUSTERED INDEX cidx ON dbo.TEST_DESC(ID DESC);
GO

INSERT INTO dbo.TEST_ASC VALUES(RAND());
GO 100000

INSERT INTO dbo.TEST_DESC VALUES(RAND());
GO 100000

Las dos declaraciones de inserción producen exactamente el mismo plan de ejecución, pero al observar las estadísticas operativas, las diferencias se muestran en contra de [nonleaf_update_count].

SELECT 
OBJECT_NAME(object_id)
,* 
FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(''TEST_ASC''),null,null)
UNION
SELECT 
OBJECT_NAME(object_id)
,* 
FROM sys.dm_db_index_operational_stats(DB_ID(),OBJECT_ID(''TEST_DESC''),null,null)

Hay una operación adicional bajo el capó que se está ejecutando cuando SQL está trabajando con el índice DESC que se ejecuta en contra de la IDENTIDAD. Esto se debe a que la tabla DESC se está fragmentando (filas insertadas al comienzo de la página) y se producen actualizaciones adicionales para mantener la estructura del árbol B.

Lo más notable de este ejemplo es que el índice agrupado de DESC se fragmenta en más del 99%. Esto está recreando el mismo mal comportamiento que usa un GUID aleatorio para un índice agrupado. El siguiente código muestra la fragmentación.

SELECT 
OBJECT_NAME(object_id)
,* 
FROM sys.dm_db_index_physical_stats  (DB_ID(), OBJECT_ID(''dbo.TEST_ASC''), NULL, NULL ,NULL) 
UNION
SELECT 
OBJECT_NAME(object_id)
,* 
FROM sys.dm_db_index_physical_stats  (DB_ID(), OBJECT_ID(''dbo.TEST_DESC''), NULL, NULL ,NULL) 

ACTUALIZAR:

En algunos entornos de prueba también veo que la tabla de DESC está sujeta a más WAITS con un aumento en [page_io_latch_wait_count] y [page_io_latch_wait_in_ms]

ACTUALIZAR:

Ha surgido una discusión acerca de cuál es el punto de un Índice descendente cuando SQL puede realizar análisis hacia atrás. Lea este artículo acerca de las limitaciones de los análisis hacia atrás .

Answer 4

Siempre que los datos vengan ordenados por el índice agrupado (independientemente de si es ascendente o descendente), no debería haber ningún impacto en el rendimiento del inserto. El razonamiento detrás de esto es que a SQL no le importa el orden físico de las filas en una página para el índice agrupado. El orden de las filas se mantiene en lo que se denomina una "Matriz de compensación de registros", que es la única que se debe volver a escribir para una nueva fila (que de todos modos se habría hecho independientemente del orden). Las filas de datos reales solo se escribirán una después de la otra.

A nivel de registro de transacciones, las entradas deben ser idénticas independientemente de la dirección, por lo que esto no generará ningún impacto adicional en el rendimiento. Por lo general, el registro de transacciones es el que genera la mayoría de los problemas de rendimiento, pero en este caso no habrá ninguno.

Puede encontrar una buena explicación sobre la estructura física de una página / fila aquí https://www.simple-talk.com/sql/database-administration/sql-server-storage-internals-101/ .

Básicamente, siempre que sus inserciones no generen divisiones de página (y si los datos vienen en el orden del índice agrupado, independientemente de su orden, no lo harán), sus inserciones serán insignificantes si su impacto en el desempeño de la plaquita.