Buscar este blog

jueves, 23 de mayo de 2013

Borrar Distribution Database

Amigos,

   Esta vez les traigo un script muy chiquito pero eficiente.

   Si están trabajando en ambiente de test con Replication seguramente se han encontrado con el problema de no poder borrar manualmente la base de datos del sistema llamada Distribution, una vez que finalizaron las pruebas.

   Pues bien, a no enloquecer, aquí les traigo la solución:

    use master
    go
    alter database distribution set offline;
    drop database distribution;

 
   Sencillo verdad?

   Pues eso es todo por hoy, saludos a su disposición.

   Gustavo Herrera para Sql Server Para Todos.

jueves, 9 de mayo de 2013

Mantener Particiones de Tablas (Split y Merge de Particiones)

Amigos,

Hemos aprendido la importancia de particionar tablas, y también bajo que condiciones es conveniente optar por hacerlo...
.
Hemos dado 5 pasos para la partición de una tabla http://gherrerasqlserver.blogspot.com.ar/2013/05/particionar-tablas-en-5-pasos.html

Pues bien, siguiendo con el mismo ejemplo hemos de entender cómo mantener la partición creada.

Recordemos que en nuestro ejemplo habíamos particionado la tabla llamada "VENTAS".


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


Esta tabla contenía 20 millones de registros, lo cuales fueron reasignados a 4 data files distintos acorde al siguiente Partition Function:


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


Y que habíamos mapeado los registros a los filegroups creados acorde al siguiente Partition Scheme:


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



Pues bien amigos, la pregunta es...

Qué pasa cuando comienzo el primer semestre del año 2013?, Dónde serán sincronizados los registros?

Si no hacemos un trabajo de mantenimiento de la partición, todos los registros del primer semestre del 2013 serán asignados al Filegroup [Fg_Ventas_20012B] , por exceder el último límite de nuestro Partition Function (2012-06-30 23:59:59' ).

Esto es muy malo, pues el datafile contenido en el filegroup [Fg_Ventas_2012B] comenzará a crecer desbalanceando nuestra tabla particionada y, por consiguiente, provocando lentos pero progresivos problemas de performance a medida que vaya creciendo el archivo mencionado.

Vamos entonces a tomar cartas en el asunto. (recomiendo, como siempre, hacer este tipo de trabajos con la Base de Datos en modo "Single User"):


Primer Paso : Agregar Un Nuevo Filegroup Para Contener los Datos el File Con los Datos del Primer Semestre del 2013
Alter Database Prueba
Add FileGroup [Fg_Ventas_20013A]



Segundo Paso : Agregar Files Para  los Filegroups Creados
Alter Database Prueba
Add File
(Name = 'ventas_2013A',
 Filename = 'H:\Data\ventas2013A.ndf',
 Size = 25000MB,
 Maxsize = 100000MB)
 To Filegroup [Fg_Ventas_20013A]


Tercer Paso : Moficar El Partition Scheme Para Mapear al Mismo el Nuevo FileGroup Creado en el primer paso
Alter Partition Scheme [PS_Ventas]

Next Used [Fg_Ventas_20013A]


Cuarto Paso : Moficar El Partition Function Agregando un Nuevo Limite 

Alter Partition Function   [PF_Ventas]
Split Range ('2012-12-31 23:59:59')

(**)  En este cuarto paso los datos del 2013 son reasignados a la partición nueva. Esto hará crecer nuestro archivo de log y demorará unos minutos (proporcionales a la cantidad de registros del año 2013 que ya hubiese ingresado a nuestra tabla)

LISTO! Nuestra Tabla Particionada ya Está Preparada Para Recibir los Datos del 2013 !!!





Nota:  es recomendable que no nos dejemos "llegar el agua al cuello" y , siguiendo con este ejemplo, hagamos este trabajo de mantenimiento de nuestra tabla particionada, antes de la finalización del año 2012.


Esto sería todo amigos... pero... Qué pasa si borramos todos los datos correspondientes al primer semestre del año 2011 (ya que de contaduría nos indican que ya no es necesario tenerlos en línea)?

La respuesta sería..." Mmm pues bien, he borrado los datos, y ahora me sobra una partición (la del primer semestre del 2011)."

Por suerte Microsoft pensó en nosotros y nos dió la oportunidad de hacer algo llamado "Merge" para tal fin, Se implementa el Merge siguiendo estos pasos: (suponiendo que ya hemos borrado de la tabla Ventas todos los registros del primer semestre del 2011)

Primer Paso : Se hace un Merge de la Partición más Vieja con La Partición Inmediatamente Superior  (deja de existir la partición 2011A, la cual es absorbida por la 2011B)

Alter Partition Function  PF_Ventas()
Merge Range ('2011-06-30 23:59:59')


Segundo  Paso : Ya podemos borrar el File y el FileGroup en Desuso...


Alter Database [Prueba]  Remove File [Ventas_2011A]
Alter Database [Prueba]  Remove FileGroup  [Fg_Ventas_20011A]





Y bien amigos, ahora si hemos llegado al fin de este capítulo.

Espero haber sido claro y quedo a vuestra entera disposición.

Un placer tenerlos del otro lado :)

ss Gustavo Herrera.

Otro Artículo Recomendado por el Autor

"Mantenimiento de Estadíasticas 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


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