partitioned - SQL Server-Tablas particionadas vs. Índice Clustered?

partitioned tables sql server (6)

Supongamos que tiene una tabla masiva con tres columnas como se muestra a continuación:

[id] INT NOT NULL, [date] SMALLDATETIME NOT NULL, [sales] FLOAT NULL

Supongamos también que está limitado a un disco físico y un grupo de archivos (PRIMARIO). Usted espera que esta tabla mantenga ventas por más de 10,000,000 de identificaciones, en cientos de fechas (fácilmente registros de 1B +).

Al igual que con muchos escenarios de almacenamiento de datos, los datos generalmente crecerán secuencialmente por fecha (es decir, cada vez que realice una carga de datos, estará insertando nuevas fechas y tal vez actualizando algunas de las fechas más recientes de datos). Para fines analíticos, los datos a menudo serán consultados y agregados para un conjunto aleatorio de ~ 10,000 identificadores que se especificarán mediante una combinación con otra tabla. A menudo, estas consultas no especifican rangos de fechas, o especifican rangos de fechas muy amplios, lo que me lleva a mi pregunta: ¿Cuál es la mejor manera de indexar / particionar esta tabla?

He pensado en esto por un tiempo, pero estoy atascado con soluciones contradictorias:

Opción n.º 1: dado que los datos se cargarán secuencialmente por fecha, defina el índice agrupado (y la clave principal) como [fecha], [id]. Cree también una función / esquema de particionado de "ventana deslizante" en la fecha que permita el movimiento rápido de nuevos datos dentro y fuera de la tabla. Potencialmente, cree un índice no agrupado en la identificación para ayudar con las consultas.

Resultado esperado n. ° 1: esta configuración será muy rápida para la carga de datos, pero subóptima cuando se trata de lecturas analíticas, en el peor de los casos (sin limitaciones por fechas, mala suerte con el conjunto de id. Consultados), 100% de las páginas de datos pueden leerse.

Opción n.º 2: como los datos se consultarán solo para un pequeño subconjunto de identificadores a la vez, defina el índice agrupado (y la clave principal) como [id], [fecha]. No te molestes en crear una tabla particionada.

Resultado esperado n. ° 2: Se espera un gran rendimiento cuando se trata de cargar datos, ya que no podemos limitarlo por fecha. Se espera un gran beneficio de rendimiento en lo que respecta a mis consultas analíticas, ya que minimizará la cantidad de páginas de datos leídas.

Opción n. ° 3: Clúster (y clave principal) de la siguiente manera: [id], [date]; función / esquema de partición "ventana deslizante" en la fecha.

Resultado esperado n. ° 3: No estoy seguro de qué esperar. Dado que la primera columna del índice agrupado es [id] y, por lo tanto (entiendo) que los datos están organizados por ID, esperaría un buen rendimiento de mis consultas analíticas. Sin embargo, los datos están divididos por fecha, lo cual es contrario a la definición del índice agrupado (pero aún alineado como la fecha es parte del índice). No he encontrado mucha documentación que se refiera a este escenario y qué beneficios de rendimiento obtendré, si es que los obtengo, lo cual me lleva a mi última pregunta adicional:

Si estoy creando una tabla en un grupo de archivos en un disco, con un índice agrupado en una columna, ¿hay algún beneficio (además del cambio de partición al cargar los datos) que proviene de definir una partición en la misma columna?

El índice agrupado en la columna de fecha no es bueno si tiene inserciones que se insertarán más rápido que la resolución de fecha y hora de 3,33 ms. si lo hace obtendrá 2 claves con el mismo valor y su índice tendrá que obtener otro uniquifier interno que aumentará su tamaño.

iría con el # 2 de tus opciones.

Esta tabla es asombrosamente estrecha. Si la tabla real será tan estrecha, debería estar contento de tener escaneos de tabla en lugar de indexar búsquedas.

Yo haría esto:

CREATE TABLE Narrow ( [id] INT NOT NULL, [date] SMALLDATETIME NOT NULL, [sales] FLOAT NULL, PRIMARY KEY(id, date) --EDIT, just noticed your id is not unique. ) CREATE INDEX CoveringNarrow ON Narrow(date, id, sales)

Esto maneja consultas puntuales con búsquedas y consultas de amplio rango con escaneos limitados contra criterios de fecha y criterios de id. No hay búsqueda por registro desde el índice. Sí, dupliqué el tiempo de escritura (y el espacio utilizado), pero está bien, yo también.

Si hay alguna necesidad de una pieza específica de datos (¡y esa necesidad se demuestra con el perfil !), Crearía una vista agrupada que apunte a esa sección de la tabla.

CREATE VIEW Narrow200801 AS SELECT * FROM Narrow WHERE ''2008-01-01'' <= [date] AND [date] < ''2008-02-01'' --There is some command that I don''t have at my finger tips to make this a clustered view.

Las vistas en clúster se pueden usar en consultas por nombre, o el optimizador elegirá usar las vistas en clúster cuando las cláusulas FROM y WHERE sean apropiadas. Por ejemplo, esta consulta usará la vista agrupada. Tenga en cuenta que se hace referencia a la tabla base en la consulta.

SELECT SUM(sales) FROM Narrow WHERE ''2008-01-01'' <= [date] AND [date] < ''2008-02-01''

Como el índice le permite hacer columnas específicas convenientemente accesibles ... La vista agrupada le permite hacer que las filas específicas sean accesibles.

Partición de la tabla por fecha. Varias particiones horizontales serán más efectivas que una tabla grande con tantas filas.

Si está utilizando las particiones en las instrucciones de selección, entonces puede ganar cierta velocidad.

Si no lo está usando, solo selecciona "estándar", entonces no tiene ningún beneficio.

En su problema original: recomendaría su opción n. ° 1 con el índice no agrupado en la identificación incluida.

Yo haría lo siguiente:

Índice no agrupado en [Id]
Índice agrupado el [Fecha]
Convierta el tipo de datos [sales] en numérico en lugar de flotar

Un índice agrupado le dará beneficios de rendimiento para las consultas al localizar la E / S. La fecha es una estrategia de partición tradicional ya que muchas consultas D / W miran los movimientos por fecha.

Una regla empírica para una tabla particionada sugiere que las particiones deben tener un tamaño de alrededor de 10 m.

Sería algo inusual ver una gran ganancia de rendimiento de un índice agrupado en una carga de trabajo analítica diversa. El optimizador de consultas usará una técnica llamada ''Intersección del índice'' para seleccionar filas sin siquiera golpear la tabla de hechos. Vea aquí una publicación que hice sobre otra pregunta que explica esto con más profundidad con algunos enlaces. Un índice agrupado puede o no participar en la intersección del índice, por lo que puede descubrir que le gana relativamente poco en una carga de trabajo de consulta general.

Puede encontrar circunstancias en la carga donde los índices agrupados le dan alguna ganancia, particularmente si tiene cálculos derivados (como Earned Premium ) que se computan dentro del proceso ETL. En este caso, puede obtener algunos beneficios. Si tiene una consulta específica que sabe que se ejecutará todo el tiempo, podría tener sentido utilizar índices agrupados para esto. Las opciones n. ° 2 y n. ° 3 solo lo beneficiarán significativamente si espera que este tipo de consulta sea la abrumadora mayoría del trabajo realizado por la aplicación.

Para un sistema flexible, una partición simple de intervalo de fechas con un índice en el ID (y la fecha si las particiones contienen un rango probablemente le proporcionará un rendimiento tan bueno como el que tiene. Puede obtener algún beneficio al agrupar las circunstancias de índice limitado. también obtenga un poco de kilometraje al construir un cubo sobre los datos y asegurarse de que las agregaciones estén configuradas correctamente para esta consulta.