miércoles, 8 de mayo de 2013

PARTICIONAR TABLAS EN 5 PASOS

Amigos,

Hoy quiero hablarles de un tema vigente desde el SQL Server 2005, especialmente útil cuando necesitamos mejorar los tiempos de respuesta en tablas de varios millones de registros en ambientes de reporting productivos.

Hasta el SQL Server 2000 no existía la posibilidad de particionar tablas. Toda la info de una tabla debía recaer en un mismo datafile provocando esto enormes problemas a la hora de consultar o insertar información en la tabla cuya manipulación, (por su tamaño), se hacía casi imposible.

A partir del SQL Server 2005 Microsoft introdujo el concepto de "Partición de Tablas", lo cual nos permite "dividir" nuestras tablas en porciones, las cuales serán ubicadas en varios archivos (tantos como determinemos) y alocados en más de una unidad de disco.

De este modo, podremos sacarle el máximo provecho a una de las grandes ventajas que tiene el motor de base de datos de Microsoft, la posibilidad de realizar lecto escrituras paralelas (con toda la ganancia de tiempo y performance que ello signifca).

El particionar una tabla no es difícil y es especialmente útil cuando ud dispone de una tabla de > de 15 millones de registros y al menos más de una unidad de disco disponible para alocar las futuras particiones.

Para ello debemos seguir los siguientes 5  pasos (vamos a la práctica).

Supongamos que tenemos una tabla llamada ventas la cual tiene 20 millones de registros correspondientes a los años 2011 y 2012, con la siguiente estructura:


CREATE TABLE [dbo].[ventas](
[id] [int] NULL,
[fecha] [datetime] NULL, ---> idx_fecha (clustered)
[idproducto] [int] NULL,
[cantidad] [int] NULL)


Pues bien, llegó el momento de determinar un criterio de partición. Yo creo que en este caso, por la característica de la data y la cantidad de registros, sería conveniente generar 4 datafiles de 5 millones de registros aprox. cada uno. Es decir,  generar cuatro archivos con un semestre cada uno (de los dos años que hoy contiene la tabla)

Primer Paso : Agregar Filegroups Para Contener los Files Semestrales


Alter Database Prueba
Add FileGroup [Fg_Ventas_20011A]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20011B]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20012A]
Alter Database Prueba
Add FileGroup [Fg_Ventas_20012B]

Segundo Paso : Agregar Files Para  los Filegroups Creados


Alter Database Prueba
Add File
(Name = 'ventas_2011A',
 Filename = 'D:\Data\ventas2011A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20011A]


Alter Database Prueba
Add File
(Name = 'ventas_2011B,
 Filename = 'E:\Data\ventas2011B.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20011B]


Alter Database Prueba
Add File
(Name = 'ventas_2012A',
 Filename = 'F:\Data\ventas2012A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20012A]




Alter Database Prueba
Add File
(Name = 'ventas_2012B',
 Filename = 'G:\Data\ventas2012B.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup Fg_Ventas_20012B]


Tercer Paso :  Generar una Partition Function (la cual determinará los rangos a partir de los cuales particionaremos la tabla, - en nuestro caso por fecha -)


Create Partition Function [PF_Ventas](datetime)
As Range Right For Values
 ('2011-06-30 23:59:59', '2011-12-31 23:59:59','2012-06-30 23:59:59' )


Cuarto Paso :  Generar una Partition Scheme (el cual mapeará las particiones a los filegroups creados en el primer paso)


Create Partition Scheme [PS_Ventas] As Partition [PF_Ventas]
To ([Fg_Ventas_20011A], [Fg_Ventas_20011B] , [Fg_Ventas_20012A], [Fg_Ventas_20012B] )


Quinto Paso:  Distribuir Físicamente los Registros de la Tabla Ventas en los Files Creados Mediante el Dropeado y la Creación de Indice Cluster Sobre el Partiton Scheme

5.1    Drop Index [ix_fecha] On [dbo].[ventas]
         With( Online = Off )


5.2    Create Clustered Index [ix_fecha] On [dbo].[Ventas] (fecha)
          on [PS_Ventas] (fecha)


Listo! Ya tenemos nuestra tabla particionada y con una mejora en la performance que los invito a probarla

(*) IMPORTANTE: Si nuestra tabla tenía índices nonclustered adicionales, los mismos deben ser recreados sobre la partición, tal cual lo hecho en el paso 5, para lograr un óptima performance.


Les dejo por último un script hecho en base a metadata que nos provee el SQL Server que tiene como objetivo el verificar que la particiones creadas hayan sido efectivamente bien "pobladas" de datos, acorde a los trabajos realizados.

SELECT 
t.name AS TableName, 
i.name AS field, 
p.partition_number,
r.value AS BoundaryValue ,
rows
From
Sys.Tables AS t 
Join Sys.Indexes AS i
On t.object_id = i.object_id
Join sys.partitions AS p
On i.object_id = p.object_id And
       i.index_id = p.index_id 
Join  sys.partition_schemes AS s 
On i.data_space_id = s.data_space_id
 Join sys.partition_functions AS f 
ON s.function_id = f.function_id
Left Joing sys.partition_range_values AS r 
On  f.function_id = r.function_id and 
      r.boundary_id = p.partition_number
Where
t.name = 'Ventas' and
i.type <= 1
Order By p.partition_number;


(**) En un próximo post abordaré la manutención de las particiones, algo que no es problemático, pero que si requiere de un trabajo que debe ser tenido en cuenta a la hora de determinar si vale la pena o no particionar una tabla (costo/beneficio). 

Eso es todo amigos, como siempre estoy para ayudarles. No dejen de escribirme que siempre estoy dispuesto a darles una mano.

GUSTAVO HERRERA para "SQL SERVER PARA TODOS"

Otro Post recomendado por el Autor

Mantenimiento de Estadísticas Para una Performance Optima de Nuestra BD:

https://www.blogger.com/blogger.g?blogID=4841087034568585749#editor/target=post;postID=5281491474180357583;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname






33 comentarios:

  1. Saludos, podrias darme unas asesorias, con esto de las particiones, tengo una base de datos, donde en una tabla se crean mas de un millon de registros diarios. Gracias.

    ResponderEliminar
  2. Muy buen aporte, una vez que ya hice los pasos de arriba,
    Como consulto las particiones??

    ResponderEliminar
  3. Buenas tardes, por favor corré el script que forma parte de la nota

    SELECT
    t.name AS TableName,
    i.name AS field,
    p.partition_number,
    r.value AS BoundaryValue ,
    rows
    From
    Sys.Tables AS t
    Join Sys.Indexes AS i
    On t.object_id = i.object_id
    Join sys.partitions AS p
    On i.object_id = p.object_id And
    i.index_id = p.index_id
    Join sys.partition_schemes AS s
    On i.data_space_id = s.data_space_id
    Join sys.partition_functions AS f
    ON s.function_id = f.function_id
    Left Joing sys.partition_range_values AS r
    On f.function_id = r.function_id and
    r.boundary_id = p.partition_number
    Where
    t.name = 'Ventas' and
    i.type <= 1
    Order By p.partition_number;

    ResponderEliminar
  4. Hola Gustavo gusto poder saludarte, y agradecerte por compartir tus buenos oficios. Tengo entendido que para crear particionamiento de tablas e indices, la tabla debe estar vacía al menos en el SQL Server 2005 Microsoft es así, corrígeme por favor, si esto es correcto y por favor según tu tiempo profundiza mas en el tema que es interesante este punto para el manejo de millones de registros. Muchas gracias.

    ResponderEliminar
  5. Que tal Juan, el placer el mio.
    Siempre para particionar una tabla es necesario primero generar otra estructura sobre una partición (es decir otra tabla igual a la que quieres particionar ya particionada), para luego transferir los datos de una a otra. Siempre ten en cuenta de crear no solo el idx cluster sobre la partición, sino también los idx non clustered, para obtener óptimos resultados.
    Sdos desde Argentina colega

    ResponderEliminar
  6. Hola Gustavo, tengo una duda con respecto al tema: Si particiono una tabla en 4 pero utilizo el mismo disco, ayudaria?

    ResponderEliminar
  7. Buenas tardes Edwin.
    Ayudaría, lo único que perderías sería la velocidad de cualquier proceso de lectura de datos de una tabla particionada en distintos discos, lo cual es algo muy atractivo en si mismo. Sdos.

    ResponderEliminar
  8. Gustavo, gracias por contestar.
    Pero no se supone que lo que se busca es la velocidad en la consulta?.
    Mi pregunta directa es: Me lo recomiendas para una tabla que recibe 100.000 registros diarios? Solo tengo un disco.
    Saludos.

    ResponderEliminar
  9. Edwin,
    Ante todo te recomiendo exigir al menos una segunda unidad de disco físico si quieren en tu empresa tener una mayor velocidad en sus bases de datos. Ya no solo de las tablas particionadas, sino también de las tablas no particionadas. Con algo tan sencillo como separando en distintos discos los datafiles de índices de los datafiles de datos obtendrías enormes ventajas en términos de performance.

    Si vamos a tu pregunta concreta la respuesta es, si te lo recomiendo, más allá de ser una tabla que está en un entorno aparentemente OLTP y el fuerte de las tablas particionadas pasa por el rendimiento en entornos OLAP.

    Algunos de los beneficios que podrás obtener a pesar de tener todo en un disco son:

    1) Una mejor administración. Por ejemplo regenerando o reorganizando los índices solo en aquellas particiones que tu veas fragmentadas
    2) Puedes disponer el nivel de bloqueo a nivel de partición para evitar el bloquear la totalidad de la tabla
    3) Si no backupeas full puedes restaurar o backupear solo aquella partición de la tabla que esté sobre el filegroup que tu desees backupear en lugar de la totalidad de la misma.
    4) Ganarás también, a pesar de no tener las particiones en distintos discos en velocidad de lectura y escritura ya que los planes de ejecución de sql server disponen de operadores específicos para tablas particionadas que hacen a una mejora general en operaciones de lectura o escritura.
    Como ves, mi respuesta es si, particiona la tabla.
    De todos modos sería bueno conocer tu entorno, tengo muy pocos datos como para darte una respuesta con fundamentos más precisos.
    Un abrazo y suerte.

    ResponderEliminar
  10. Muchas gracias Gustavo, es suficiente la información.
    Totalmente decidido a particionar y veré que puedo hacer con el tema de los discos.
    Mil gracias por el apoyo.

    ResponderEliminar
  11. Buenas Tardes Gustavo mi duda es si para particionar las tablas en la tabla tiene que estar la fecha que fue ingresado el dato

    ResponderEliminar
  12. Gracias Gustavo por tu aporte, si tengo un cluster con win 2008 con dos nodos, el particionamiento se puede hacer ?

    ResponderEliminar
    Respuestas
    1. Si Mario, no tendrías que tener ningún problema con esa configuración.

      Eliminar
  13. Que pasa cuando la tabla es parte de una replicación, en mi caso es merge replication, habría que re configurar todos los servidores o se replica la partición?

    ResponderEliminar
  14. Excelente Artículo!!!
    La consulta sobre la replicación esta bien acertada, que pasa en los ambientes en donde se maneja algún tipo de replicación? Existe algún inconveniente con el publicador o los subscriptores?

    ResponderEliminar
  15. Muy buen artículo, muy bien explicado.
    Una consulta, me llama la atención lo que comentas de como colocar los datafiles de data e indices en diferentes unidades puede significar una mejora en el rendimiento de las consultas, sin embargo, quisiera consultarte, cómo se podría implementar esto si en el servidor que manejo posee un raid 0 para los discos?

    ResponderEliminar
    Respuestas
    1. Que tal, gracias por tus comentarios. Si posees un RAID 0, al menos tienes dos unidades físicas de disco.

      En tal caso yo armaría un esquema de partición sobre datafiles que estén repartidos en ambos discos, luego "alinearía" todos sus idx sobre la partición. Es decir crearía los idx no clustered sobre la partición. Esto hará más performante el esquema. Sdos.

      Eliminar
    2. Gracias por tu respuesta.

      Eliminar
  16. Muchas gracias por el articulo muy bueno!!, mi inquietud es que tengo indices Clustered asociados a constraint en algunas tablas que quiero dividir, que diferencia existe si genero un indice nonclustered para particionar la tabla de esta manera.

    Create NONCLUSTERED Index [ix_fecha] On [dbo].[Detalles_ventas] (fecha_modifica)
    on [PS_DV] (fecha_modifica)

    Gracias!.

    ResponderEliminar
    Respuestas
    1. Gracias por tus comentarios. La partición tiene que ser hecha sobre un Indice Cluster ya que este determina el orden físico de la misma.
      De hecho si tu quieres mover una tabla que no sea particionada hacia otro datafile el camino correcto es simplemente rehaciendo el índice cluster sobre el datafile de destino.
      Pasando en limpio, no vas a lograr la partición física si trabajás sobre el índice no cluster. Te recomiendo hacerlo sobre el cluster y luego "alinear" los idx no cluster sobre la misma partición (es decir recrear cada uno de los idx sobre la partición creada). Saludos y suerte

      Eliminar
    2. Gracias Gustavo de echo no he podido avanzar en mi ambiente de pruebas elimine el indice pero se ha vuelto muy lento el servidor esto es normal?.

      Eliminar
    3. Dime que tipo de idx has intentado borrar. Si es el cluster es normal puesto que regeneras toda la estructura física de la tabla enviándola al datafile por default y creando una tabla del tipo heap. Algo muy poco aconsejable y muy poco performante.

      Eliminar
    4. estoy ejecutando esto ALTER TABLE [dbo].[detalles_ventas] DROP CONSTRAINT [PK_detalles_ventas]

      y lo vuelvo a generar asi.

      ALTER TABLE [dbo].[detalles_ventas] ADD CONSTRAINT [PK_detalles_ventas] PRIMARY KEY CLUSTERED
      (
      [id_sucursal] ASC,
      [id_sesion] ASC,
      [id_venta] ASC,
      [linea] ASC,
      [fecha_modifica]


      ) on [PS_dv] (fecha_modifica)

      que opinas?.

      Eliminar
  17. La syntaxis es perfecta, solamente tené en cuenta que estás regenerando la tabla completa. La estás moviendo del datafile original hacia los distintos datafiles sobre los cuales tengas definida la partición. Esto, dependiendo de la cantidad de registros, puede demorarte mucho tiempo. Pero vas bien. No te olvides luego de general los idx no clustered tb sobre la partición. Abzo.

    ResponderEliminar
    Respuestas
    1. Excelente comentario Gustavo gracias por tu apoyo, de hecho tiene 315 millones de registros.

      Saludos.

      Eliminar
  18. Muy buen aporte, si embargo solo funciona para la versión enterprise de SQL Server 2005.

    ResponderEliminar
  19. Hola Gustavo saludos, quisiera consultarte acerca de lo siguiente:
    Particione un tabla que conta de 400 millones de registro aproximadamente, buscando un alto performance en las lecturas, la columna de partición es año, distribui en varios files
    pero todo quedo en el mismo disco ya que solo dispongo de uno, en la db he conservado la tabla original sin particionar y
    la nueva tabla particionada, pero he comparado el rendimiento de los queries y no mejora con respecto a la nueva tabla particionada... es más
    en muchos casos el rendimiento es mejor en la tabla sin particionar, es problema de tener los files en un mismo disco ?,

    el Primary key es (id,año), único y cluster, he creado otros idx como non Cluster y alineados a la partición, estoy usando sqlsever 2012 R2 enterprise.

    Gracias por la ayuda que puedas brindarme.

    ResponderEliminar
  20. Es transparente al momento de recuperar los datos? O al hacer una consulta debería de forma explícita indicar que se realice la búsqueda en todas las particiones de la tabla?

    ResponderEliminar
  21. Hola Gustavo.

    Tengo una duda, generé mis archivos donde se almacenaran mis particiones, pero hay alguna forma de que cuando llegue al tamaño máximo de mi archivo archivo, se genere uno nuevo y se siga almacenando la misma partición?.

    Espero poder contar con tu ayuda.

    Saludos.

    ResponderEliminar
  22. Buenos días:

    Tengo la siguiente consulta:
    De que me sirve saber los tamaños de los archivos cuando se realiza el particionamiento de la tabla?

    Estoy usando SQL 2016 con una BD que una de sus tablas contiene 1200 000 0000 de transacciones.

    ResponderEliminar
  23. Hola...me gustaria saber cuando una tabla es candidata para ser particionada y cuando particionarla traeria mas problemas que beneficios.

    Gracias

    ResponderEliminar