Buscar este blog

lunes, 12 de junio de 2023

Checklist DBA - 15 sugerencias que considerar al planificar las bases de datos en cualquier proyecto.

 

Si formás parte de la etapa inicial de cualquier proyecto que incluya el uso de bases de datos, esta publicación es de tu interés.

Por medio de la misma tendrás un listado de consideraciones y sugerencias relacionadas con Bases de Datos que debés tener en cuenta para que tus bases de datos cumplan su función de modo efectivo seguro y perfomante.

 

 

Identificar las necesidades del negocio

Estar seguros de comprender las necesidades y requisitos específicos del negocio para asegurarse de que la base de datos cumpla con ellos.

 

Seleccionar la plataforma de base de datos adecuada

Seleccionar la plataforma de base de datos adecuada.

Funcionalidad, escalabilidad, seguridad y costo son ítems para ponderar previo a esta selección.

 

Diseñar la arquitectura de la base de datos

Crear un diseño lógico y físico de la base de datos, incluyendo la estructura de tablas, relaciones, claves primarias y secundarias, y otros elementos necesarios.

Se deberán aplicar principios de normalización para evitar redundancia de datos y mantener la integridad de la información.

Para lograr objetos de base de datos bien definidos y performantes, es mandatorio relevar y analizar toda la data que vaya a formar parte de nuestro sistema, solicitando al cliente, de ser posible, un modelo real de cada dato, flujogramas y DER (si existiesen estos dos últimos).

Sólo una vez analizada en detalle la información recolectada, estaremos en condiciones de crear los objetos de bases de datos óptimos en su estructura, definición, relación, normalización, y tipo de dato apropiado.

 

Considerar el rendimiento y la performance

Diseñar la base de datos teniendo en cuenta el rendimiento, optimizando consultas, índices y configuraciones para garantizar una buena respuesta y escalabilidad

 

Establecer estándares de codificación y nomenclatura

Establecer estándares de codificación y nomenclatura para mantener la consistencia y facilitar el mantenimiento de la base de datos a largo plazo.

 

Definir la versión y edición del motor de base de datos

Sql server 2019 es hoy en día la versión homologada por GYF sugerida por ser la más sólida y avanzada del motor de base de datos de Microsoft.

Otras versiones alternativas son SQL 2017 y SQL 2016, si bien debe tenerse en cuenta que el soporte de Microsoft se extiende sólo por 10 años. En relación a la edición a utilizar yo recomiendo como mínimo la “Standard”

 

Documentar la base de datos

Crear documentación detallada sobre la estructura de la base de datos, diagramas ER, diccionario de datos y cualquier otra información relevante.

 

Establecer un plan de migración de datos existentes

Si hay datos existentes que deben ser migrados a la nueva base de datos, planificar y ejecutar el proceso de migración de datos de manera efectiva y precisa.

 

Solicitar el servidor de base de datos teniendo en cuenta:

De modo óptimo es aconsejable solicitar al cliente un servidor “dedicado”, (de uso exclusivo), para nuestra aplicación.

Si no es posible contar con un servidor dedicado, solicitar una instancia exclusiva del motor de base de datos con el objetivo de disponer del uso único del procesador, la ram y demás componentes asignados al mismo.

Si debemos compartir servidor y/o instancia de motor de base de datos, hemos de tener en cuenta que los componentes críticos de los cuales dependerán una parte importante de la performance de nuestros aplicativos serán, esencialmente, la memoria y los discos.

La memoria RAM a solicitar será de entre 64 gb y 128 gb para instancias de servidores de base de datos compartidos, variando estos valores a 32 gb y 64 gb para instancias dedicadas.

En lo que hace a disco sugerimos requerir al menos 3 unidades adicionales a la unidad ROOT (“C:”), con el objetivo de distribuir convenientemente la lectoescritura sobre los archivos de datos, log y TempDb.  

Es muy importante adicionalmente contar con el detalle del tipo y velocidad de transferencia de los discos productivos ofrecidos por el cliente, sumando al mismo el detalle del procesador. Estos datos deberán ser validados por nuestro Líder IT.

 

Considerar la capacidad de crecimiento

Prever el crecimiento futuro de la base de datos y asegurarse que la estructura y configuración sean escalables para soportar nuevas necesidades.

Se aconseja relevar la información que permita estimar la cantidad de espacio que habrán de utilizar nuestras bases de datos en un espacio de tiempo no menor a dos años.

Para ello deberá ponderarse la suma del peso de los registros de las tablas principales del sistema multiplicada por la cantidad de registros diarios previstos y luego extrapolar esa cifra a 24 meses.

 

Planificar una estrategia de pasaje a histórico de datos

La planificación de la estrategia de pasaje a histórico de la data correspondiente a las estructuras transaccionales principales debería ser ejecutada en esta etapa. De este modo se garantizará la sustentabilidad operativa del sistema y su buena performance conforme al paso de los años.

Si bien hay distintas estrategias para la tarea, el particionamiento de las tablas principales transaccionales del sistema debiera ser considerado como la estrategia de mejor resultado y facilidad de implementación.

 

Disponer de un ambiente dedicado a consultas

El diseño de las bases de datos debería contemplar desde su génesis objetos, (base de datos, tablas, vistas, sps), exclusivamente dedicados a proveer a las consultas del sistema.

Así objetos transaccionales no debieran ser origen de consultas o listados asegurando en consecuencia la correcta operatividad transaccional y la buena performance de las consultas y listados del sistema.

 

Establecer un plan de mantenimiento regular

La performance de un sistema suele ser el talón de Aquiles de este y el issue que más horas de soporte correctivo requiere.

Para asegurar un sistema performante más allá del paso del tiempo, es vital exigir al cliente la implementación de una rutina de mantenimiento de índice y estadísticas.

La inevitable degradación de ambos componentes relacionados estrechamente con la performance de nuestros procesos puede ser reducida al mínimo con la implementación temprana de las mencionadas rutinas.

 

Definir políticas claras de seguridad

Se deben establecer y consensuar con el cliente las políticas de seguridad para proteger la base de datos y los datos almacenados en ella, incluyendo el acceso a usuarios, roles y permisos.

 

Planificar una estrategia de respaldo y recuperación de datos

Se deberá definir y consensuar con el cliente un plan de respaldo regular de la base de datos y un proceso de recuperación en caso de fallos o pérdida de datos.

viernes, 27 de mayo de 2022

¿ Qué debemos tener en cuenta antes de encarar una tarea de indexación?

 

*

     Buenas tardes mis querido amigos. Hoy y como respuesta a variadas consultas que he recibido en mi casilla de correo, me gustaría brevemente hablarles sobre aquellas consideraciones que debiéramos tener en cuenta antes de encarar una tarea de indexación. Espero que disfruten del artículo... Como siempre quedo a disposición de Uds. para contestar vuestras dudas.


Antes de encarar una tarea de indexación de tablas es fundamental tener en cuenta lo siguiente:

 

    ü  Un índice bien creado tiene cosas a favor y en contra veamos:

 

o   A favor:  mejoraremos los tiempos de respuesta a consultas evitando costosos escaneos, prolongados lockeos, etc. Es decir, será una acción fundamental para lograr un sistema más ágil y performante.

 

o   En contra: Las operaciones de insert/update/delete tendrán una mayor contención (demora), debido a que el motor deberá actualizar, además de los datos de las tablas, los correspondientes a los índices que la mismas contengan.

 

      Hemos aprendido que los índices tienen “pros” y “cons” es hora entonces pensar en:

 

 

ü  ¿Qué características tiene el tipo de sistema sobre el cual voy a trabajar?

 

Aquí las respuestas pueden ser tres:

 

o   Transaccional (oltp): son ambientes con operaciones de ABM constantes que en general no han de beneficiarse con indexaciones. Por el contrario, serán más lentos cuando más índices existan.

 

o   De consultas (olap): al contrario de los sistemas transaccionales, permiten indexar s/necesidad sin preocuparse por posibles problemas de contención que pudiesen provocar los índices. Por lo general este tipo de sistemas son réplicas de sistemas transaccionales o bien concebidos bajo estructuras de datos desnormalizadas que son actualizadas por batch.

 

o   Mixtos: son sistemas transaccionales que a la vez proveen consultas. Los mismos requieren de un especial equilibrio a la hora de ser indexados. Se trata de sistemas cuyos índices deben proveer la performance necesaria sin dañar la transaccionalidad. Se trata del tipo de sistemas donde con mayor equilibrio debemos trabajar.

              

ü  ¿Qué frecuencia de ejecución y en qué horario se ejecutará el proceso sobre el cual estoy trabajando?

                                    

o   Frecuencia: si el proceso se ejecuta como parte de un batch una vez al día tal vez no merezca la pena indexar la tabla si su tiempo de respuesta es aceptable, (ejemplo un batch nocturno).  Si por el contrario es un proceso que se ejecuta frecuentemente habrá que trabajar con mayor fuerza en indexación sobre el mismo, (ejemplo una consulta de saldo frecuente).

o   Horario: si el proceso es ejecutado en horario de baja concurrencia podremos trazar una analogía con respecto al proceso que es ejecutado con poca frecuencia, es decir, no valdrá la pena tal vez emplear tantos índices. Si por el contrario es ejecutado en un horario de mucha concurrencia hemos de trabajar fuertemente en indexación.

Aquí la regla es, a mayor concurrencia, mayor necesidad de liberar rápidamente una tabla, por lo tanto, mayor necesidad de velocidad a la hora de resolver una consulta.

 

 

ü  ¿Hay ya creado sobre la tabla un índice que pueda ser utilizado por mi proceso?

                                    

o   Es fundamental tener una visión global como la que estamos proponiendo en este repaso previo antes de crear un índice. Si hay un índice ya credo sobre la tabla podemos aprovechar el mismo adaptando nuestra query o eventualmente modificando su estructura, midiendo antes el impacto sobre todo el ecosistema de procesos del sistema que utilizan el índice. La idea es no duplicar índices.

 

 

ü  ¿Es el campo por el cual quiero indexar lo suficientemente selectivo?

                                    

o   La “selectividad” de un campo es una métrica básica que nos permitirá saber si el índice será tenido en cuenta o no por el optimizer a la hora de crear un plan de ejecución. Si un índice esta creado sobre un campo selectivo, por ejemplo, un valor numérico incremental, será tenido en cuenta por el optimizer. De lo contrario, si es definido sobre un campo poco selectivo, no será tenido en cuenta, (por ejemplo, un índice sobre un campo que identifique el género de las personas).

 

 

ü  ¿Cuál es la cantidad de registros que tiene la tabla a indexar?

                                    

o   Debemos tener en consideración que el optimizer elegirá utilizar el índice que hayamos creado si la tabla tiene una cantidad de registros que así lo amerite, de lo contrario es muy probable que prefiera hacer un scan de la misma. Como regla que yo empleo, ninguna tabla paramétrica que tenga menos de 100 registros merece ser indexada. Los invito a dar forma a vuestra propia regla generando índices y estudiando en el plan de ejecución si son o no utilizados.

 


ü  ¿Cuáles campos de una query deberían ser indexados?

                                    

o   En principio aquellos campos que formen parte de queries que vayan a ser validados luego de las siguientes cláusulas:

 

-          Where

-          Joins

-          Group By

-          Having

-          Order By

            

 

o   También sería apropiado incluir los campos que formen parten del Select de una query cuya frecuencia de utilización e importancia amerite la creación de un “índices cubierto”.

 

 

ü  ¿Sobre qué tipo de campos es conveniente indexar?

                                    

La efectividad de un índice será mayor cuando más “chico” sea el campo, es decir cuanto menor sea su peso en bytes.  

 

o   Debemos pues privilegiar los campos numéricos por sobre el resto, siempre buscando el campo de menor peso dentro de este grupo. Ejemplo un tinyint por sobre un integer o un decimal.

o       Luego, en orden de efectividad, hemos de considerar los campos de tipo fecha.

o   Por último, los campos de tipo alfanumérico o char, lo cuales ofrecen el menor nivel de efectividad a la hora de ser indexados.



miércoles, 3 de marzo de 2021

Orden Lógico de Procesamiento de Queries

Estimados colegas y amigos, estoy una vez más en contacto con ustedes...

Esta vez para tratar de echar luz sobre un tema que, habida cuenta de la cantidad de consultas a mi correo que ha generado,  entiendo que bien merece que le dediquemos un posteo. 

Se trata del orden lógico en el que el SQL Server procesa nuestras queries independientemente de la forma en la que las mismas deben ser escritas por sintaxis.

En efecto, en el siguiente ejemplo veremos una query con el orden normal en el que debe ser escrita y, en entre paréntesis, el orden en que realmente es procesada lógicamente por el SQL Server:

SELECT (5) / DISTINCT (5.2) /  TOP (7)

FIELDS (5.1)

FROM     (1)

WHERE    (2)

GROUP BY (3)

HAVING   (4)

ORDER BY (6)

 Y la pregunta cae de madura. Por qué lógicamente al procesar la query  el motor no sigue el mismo orden que exige la sintaxis? .

 La respuesta es sencilla…; Porque el SQL imita la forma en la que serían dadas las instrucciones para obtener algo para definir el orden de las sintaxis.

Ejemplo...

Supongamos que queremos solicitarle a un familiar que nos alcance unos libros del cuarto.

Estas serían las instrucciones que le daríamos  y su orden.. 

“Traéme (Select), estos  libros (Fields) desde mi cuarto (From) cuyo autor sea Borges (Where) y haslo Ordenados por Título (Order By)” - orden de solicitud-

Pero en verdad nuestro interlocutor tendría que, (en el sgte orden):

“Ir al cuarto (From), separar sólo los libros de Borges (Where), tomar dichos libros  (Select) y traer los libros ordenados por Título (Order By)” -orden lógico real de las acciones -

Así pues debemos tener en claro que el sql server tiene el siguiente orden lógico para ejecutar una query. 

El tenerlo en cuenta es vital para poder escribir una query performante.

Veamos:

1) FROM (joins)
2) WHERE 
3) GROUP BY 
4) HAVING 
5) SELECT
    5.1 SELECT fields
    5.2 DISTINCT
6) ORDER BY 

7) TOP 

Para terminar de desarrollar el tema veamos un ejemplo de cómo  un desconocimiento del orden lógico de ejeución de queries puede llevarnos a tomar decisiones incorrectas.
Supongamos que tenemos una tabla "Clientes" con 10 millones de registros, y, nos solicitan el armar una consulta para listar aquellos 10 clientes que posean el mayor saldo en su haber.
Si no supieramos lo aprendido en relación al orden lógico de ejeución de las queries tal vez nuestro razonamiento podría ser.

“Ok..la query es fácil entonces… ni me hago problema por los filtros del where ni por ser preciso con los campos a seleccionar... Yo hago un Select top 10 * from la tabla ordenado descendente y listo, si total sólo voy a trabajar con 10 registros

Les parece correcto el  razonamiento? A muchos tal vez si verdad?

La query que harían sería esta?


Select top 10 *

From Clientes

order by saldo desc

La respuesta no es correcta dado que , como hemos aprendido, por orden lógico de ejecución de queries, no estaremos seleccionando sólo 10 registros sino que se estarán seleccionando 10 millones de registros, (no hay where en la query), y luego, recién luego, se estarán listando los 10 registros originalmente solicitados...
Ya saben entonces.. a tener muy en cuenta el orden lógico de procesamiento de las queries =)
Amigos es todo por ahora les dejo mis saludos desde Argentina y quedo a vuestra disposición como siempre.







martes, 23 de febrero de 2021

Función Isnull - Su innecesario y perjudicial uso en sobre campos numéricos -

 

Estimados amigos, el placer de saludarlos nuevamente...

   Trabajando en tunning  sobre unos scripts y encontrando una vez más un pequeño pero perjudicial y repetido  error,  aprovecho para compartirles como trato de hacer asiduamente la experiencia,  con el objetivo de  mejorar la performance de nuestros códigos en general.

Ante todo supongo que todos estamos de acuerdo con el que el uso de funciones en campos donde es muy importante el uso de un índice invalida totalmente la utilización por parte del motor de base de datos del mismo.

 

Ejemplo:

Tenemos una tabla “CONSUMO”  de la cual tenemos que listar los campos “Id”  “FechaPresentacion” cuando la fecha sea mayor al '2020-05-20 23:59:59'

Si existe  un índice que cubra el campo  “FechaPresentación” tendremos una excelente performance a partir de la plena utilización del índice:



  Si, por el contrario, aplicamos algún tipo de función sobre el campo indexado “FechaPresentacion” vamos a dejar sin chances de ser utlizado al índice veamos:

 


Habiendo repasado lo perjudicial que son el uso de funciones en determinados campos, (sobre todo de filtro y ordenamiento, vayamos al uso de la función ISNULL

 

Es muy común ver en queries el uso de esta función ante la necesidad de validar un campo numérico que puede contener valores null

 

Ejemplo:

 

Dada la misma tabla CONSUMO, debemos listar aquellos “id” cuyo consumo,  (campo “importe” de tipo Integer),  es mayor a 5000 pesos.  (hay registros  que pueden tener a la fecha importes con valor NULL)

 

Así  es común ver este tipo de queries:

 

El uso de la función Isnull sobre el campo “Importe” inutiliza el índice sobre dicho campo…

 

Pero .. es necesario el uso de la función Isnull para evitar errores de comparación de campos numéricos con posibles valores nulos? Evitamos de ese modo un posible error?

La respuesta es “no”.   

Cualquier campo numérico null será tomado por el sql server como “0” no siendo necesario utilizar la función Isnull y así inutilizar el eventual índice dispuesto sobre dicho campo.



Utilizaremos el idx y el resultado será el mismo.

Bueno gente me despido esperando que haya sido de utlilidad el tip.

miércoles, 3 de febrero de 2021

Problemas con "Huecos" o "Gaps" en Campos Identity - Posibles Motivos y Soluciones

Estimados colegas, el placer de saludarlos nuevamente.

  Cuántas veces nos hemos encontrado con el problema de "saltos" o "gaps" en el valor numérico de columnas definidas como "identity" en tablas de nuestras bases de datos?.

  Seguramente en más de una oportunidad hemos sido alertados por el equipo de desarrollo o por alertas propias sobre esta incidencia que le quita secuencialidad a nuestras tablas.

  Sobre este inconveniente trata el post del día de hoy. Asumo que todos sabemos lo que un campo "identity" signfica en una tabla y su funcionalidad y por que no, que también fue utilizado en una o más tablas de vuestros sistemas con la función de contar con un valor Unico y Secuencial, delegando estas característiscas en el motor de base de datos.

   Es así entonces que surje el primer punto a tener en cuenta y es el siguiente:

  "Un campo definido como identity NO GARANTIZA LA SECUENCIALIDAD de los valores del mismo"

 Así es colegas, tal como lo leen. Esto está expresado explícitamente por Microsoft en su documentación oficial. 

Si uds. requieren   por regla de negocios, generar secuencialidad. Por ejemplo si el campo identity sería el punto de partida de la numeración de la facturación de una empresa, deben recurrir a otros métodos alternativos de generación de Ids tales como Sequence no cacheado, (https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15),  u otros generados a través de programación (eg guardar un id en una tabla isolada y sumarle 1 ante cada inserción).

  Pero por qué no podemos confiar la secuencialidad en un campo identity?

  Sencillamente, y aquí viene el primer motivo de un posible "gap" en una numeración identity, porque ante un rollback, los ids rollbackeados "se perderán" y pasarán a formar parte de un hueco de ids ya no disponibles para futuras inserciones..   Así pues, si las inserciones de dos ids cualquiera, digamos el  23 y 24,  se rollbackea, dichos ids estarán faltantes en nuestra numeración de facturas.

 Ok, pero uds. tal vez llegaron a este post buscando un motivo por el cual registraron un gap en la numeración identity y están seguros de que no se debió a un rollback....  Entonces  surge la pregunta...

 Hay otro motivo por el cual se pueden registrar gaps en una columna Identity?

 La respuesta es SI

 Si estás trabajando con un motor 2012 o superior y no has modificado el comportamiento por default del cacheo de identities en SQL Server, pueden ocurrir gaps muy grandes y no tan mensurables como el de un rollback de "x registros", ante estas situaciones:

  • Caídas en General del Servicio de Motor de Base de Datos no Planificadas
  • Failovers
 Efectivamente, y sin entrar en detalles técnicos, la gente de Microsoft con el noble fin de ganar performance comenzó, a partir de SQL 2012, a "cachear" los Identity como comportamiento por default de motor. 
Ventajas? ... supuestamente algunas de performance...(no comprobadas por mi nunca).
Desventajas?  las que devienen cacheados estos valores "core" en muchos sistemas de bases de datos cuyas lógicas de negocio dependen en muchos cosos de los mismos.

Cacheados = sin persistencia ante los eventos mencionados = Pérdida de correlatividad y Gaps a veces muy significativos.

En mi opinión esta fue una muy mala jugada de Microsoft quien no avisó con la debida firmeza sobre un cambio que complicó y sigue complicando la vida de muchos de quienes trabajan MSqlSrvr. Aún cuando se puede discutir sobre la mala praxys que implica el confiar la correlatividad y otros puntos importantes que hacen a la regla de negocios sobre este tipo de campos.

Pero.. hay modo de evitar este comportamiento que por defecto tiene el Sql Sever para que deje de cachear los Ids?

 Si lo hay veamos las opciones:

  • Modificando el Identiy Cache a nivel Instancia de Motor de Base de Datos 
   Es es la única opción disponible si trabajás con las versiones 2012/2014 y 2016 
 
   Se trata de agregar el "TRACE flag 272" como parámetro de star-up 

   Los pasos son:

   1)  Ingresar al SQL Server Configuration Manager y editar properties



  2)  Agregar, (ver botón add), el flag "-T272" en la solapa "startup parameters" y aplicar cambios.

  


3) Restartear el servicio y para aplicar los cambios
 



  • Modificando el Identiy a Nivel Base de Datos

     

 Esta opción está disponible a partir del SQL Server 2017 y es mi favorita puesto que pemite ser selectivo con la Base de Datos en la cual se desea implementar el setting y adicionalmente no requiere  del reinicio del motor una vez implementado el cambio.

   La sintaxys se debe aplicar en cada base de datos y es la siguiente:

    Use database;

  go

  ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;


Amigos, esto es todo por ahora. Espero que el post les haya sido de utilidad y les prometo para un post adicional próximo una serie de scripts que les faciilitará el trabajo cuando de trabajar y controlar con tablas con campos identity se trate.

 Saludos cordiales desde Argentina, sigo esperando sus preguntas, sólo tengan paciencia y serán respondidas.



   
 


domingo, 25 de octubre de 2020

Generación de ID Único para una Tabla - Distintos Métodos -

 Buenas tardes,


    Aquí estoy luego de un largo período de tiempo en el que no le daba feed al blog. Varios fueron los motivos. Básicamente nuevas responsabilidades profesionales que requirieron de mi dedicación absoluta y cierto agotamiento que me llevó a este marcado paréntesis.

    Quiero agradecerles a todos y cada uno de Uds. los distintos mensajes, correos y consultas recibidos. Me han hecho ver que el blog sin dudas cumple con su objetivo, que no es otro que el de poder retroalimentar con mis pequeños aportes esta inmensa red profesional  de colegas  con ganas de aprender y  a la vez aportar sus conocimientos.

     Sin más introducciones vamos a este nuevo posteo

Hoy les traigo un tema con el que seguramente han tenido que lidiar en más de una oportunidad...

Se trata de aquel que tiene que ver con la necesidad de generar  un ID único que identifique los registros de una tabla “cuore” del sistema.

Cuando hablamos de tabla “cuore” hablamos de aquellas tablas bases del sistema que suelen ser referenciadas por varias tablas secundarias precisamente a partir de su ID.

Pensemos por ejemplo en un ID de una tabla de “Operaciones” donde registramos todas las transacciones de los clientes de un banco. Dicho Id tendrá luego que sincronizarse como parte del registro de las operaciones en otras tablas secundarias.

El desafío, y de ahí el origen de este post, es que, en la horas “pico”, donde la tabla de “Operaciones” está siendo objeto de numerosas inserciones casi paralelas, la generación del nuevo ID.

1)  No produzca contención por demoras en el proceso de inserción.

2)  No genere intentos de inserción de registros duplicados cuando dicho Id es insertado en tablas secundarias.

 

Es así que les propongo transitar distintos métodos con sus pros y sus contras.

 

METODO 1.  Generación de ID Autoincremental a partir campo Indentity

Es el método tal vez más utilizado.

Se define una tabla con un campo Id que tendrá un valor único al ser definido como “Identity”. Dicho campo  incrementará su valor cada vez que la tabla es insertada.

Luego, se recupera el valor del ID insertado por medio de la función del sistema @@IDENTITY  para su eventual sincronización en tablas secundarias.

 

Ejemplo:

 

--  1) Creación Tabla con "Id" tipo identity --

Create Table Operaciones

(Id int NOT NULL IDENTITY(1, 1),

 Detalle varchar(50) NULL)

  

-- 2) Inserción de registro en Tabla  ---

Insert Into Operaciones( Detalle)

Values ( 'Articulo 234-e venta')

 

-- 3) Obtención del Id para insertar en tablas secundarias ---

Declare @Id integer

Select @Id = @@identity

 

Ventajas

La facilidad de implementación. Tal vez acorde a ello sea la solución más implementada.

Desventajas

Eventualmente puede causar varios problemas veamos:

 

Ø “Problema de Inserción en la Última Página“ 

He podido observar a lo largo de mi trayectoria como DBA un problema  que se repite una y otra vez en tablas “cuore” definidas bajo esta modalidad en sistemas con una alta carga transaccional.

Este problema deviene de la definición de la Primary Key sobre el campo “Id” identiy.

Pero por qué habría de representar un problema la definición de la Pk sobre dicho campo?

Porque normalmente para la definición de dicho PK se utiliza la interface del Sql Server la cual termina generando un Indice Clustered sobre el campo Id por default.

  

Ejemplo seteo de Pk por Consola:








Código que se implementa en la práctica:

Alter Table [dbo].[Operaciones]

Add  Constraint [PK_Operaciones] Primary Key Clustered

([Id] ASC)

GO

Es así pues, que nuestra tabla “Operaciones”, pasa a tener un campo ID con las siguientes características (yo la llamo la “tripleta del problema”):

-         Identity

-         Primary Key

-         Clustered

 

Contando con estas tres características la tabla es firme candidata a sufrir lo que se conoce como “Problema de Inserción en la Ultima Página

Este problema afecta a tablas que están siendo intensamente insertadas con hilos de procesamiento paralelos debido a que todas las inserciones son dirigidas hacia el mismo sector de la tabla produciéndose entonces una fuerte contención que se traduce en notorias demoras en los procesos de inserción de registros en horarios pico.

La solución sería definir la Pk como nonclustered si las reglas de negocio así lo permiten.

 

Ø Eventual duplicación de IDs  

Otro inconveniente en este tipo de esquemas tiene que ver con aquellos escenarios en los que el nivel de inserciones es muy significativo y el ID recuperado por medio de la función @@identity,  y almacenado en la variable @Id,  se duplica al no llegar a refrescarse la variable @Id por la extrema concurrencia del hilos de inserción, casi paralelos en horas pico.

 Como consecuencia de esta eventual incidencia inmediatamente nos encontraremos con intentos de inserción fallidos por intento de violación de PK de tablas secundarias.

Hay una solución para evitar duplicaciones?

Si, el incluir la inserción en la tabla y la obtención de @ID en una transacción.

Veamos:

Begin Transaction

   ---  Inserción de registro en Tabla  ---

   Insert Into Operaciones( Detalle)

   Values ( 'Articulo 234-e venta')

 

   --- Obtención del @Id ---

   Declare @Id integer

   Select @Id = @@identity

 

   -- Si todo ok Commiteo Transacción/else Rollback  --

   If @@ERROR = 0

     Commit Transaction

   Else

     Rollback Transaction

Pero está la solución de incluir en una transacción el proceso de insertado en la tabla cuore es una alternativa exenta de eventuales inconvenientes?

Pues no … veamos dos posibles consecuencias adicionales:


Ø Huecos en la Correlatividad de IDs

En caso de ROLLBACK,  el id correspondiente a la transacción “rolbackeada” se perderá,  generando un hueco en la correlatividad de IDs que debemos solucionar con un costo adicional asociado a ello…

 

Ø Problemas de Contención

Adicionalmente y volviendo a las horas pico donde varios hilos intentan insertar registros en nuestra tabla de Operaciones, encontraremos que dichos hilos se serializan esperando por el cierre de transacción precedente. Por lo tanto, los lockeos se prolongan más de lo tolerable,  pudiendo producirse bloqueos y hasta deadlocks….

 

Utilidad

Diría que este método es el más útil y sencillo de implementar en escenarios donde la concurrencia es escasa a moderada y los volúmenes de la tablas no son tan importantes. Siempre teniendo en cuenta las precauciones que hemos descripto a la largo de la explicación del mismo.

 

METODO 2.    Generación de ID por Cálculo

Se trata de una solución que no recomiendo en absoluto pero que he visto en muchos sistemas aún productivos.

El método  consiste en tomar el máximo ID de la tabla y sumarle un dígito… para así generar el ID para el nuevo registro.

Ejemplo:

 

--  1) Creación Tabla --

CREATE TABLE Operaciones

(Id int NOT NULL,

 Detalle varchar(50) NULL)

 

-- 2) Seteo de la Pk al campo "Id" --

ALTER TABLE [dbo].[Operaciones]

ADD  CONSTRAINT [PK_Operaciones] PRIMARY KEY CLUSTERED

([Id] ASC)

GO

 

 -- 3) Cálculo del @id e Inserción en Tabla  --

  Begin Transaction

     Declare @Id integer

    ---  Obtención de id por cálculo --

   Select  @Id =  coalesce (max(id),0) + 1 from Operaciones

    ---  Inserción de registro en Tabla  ---

   Insert into Operaciones(id,  Detalle)

   values (@Id,  'Articulo 234-e venta')


Ventajas

No encuentro ventaja alguna  más allá de cierta facilidad en el grado de implementación y comprensión  de la rutina en programadores menos experimentados.


Desventajas

Ø Alto grado de contención / Bloqueos / Deadlocks

En términos de performance esta elección es por lejos la peor debido a la baja performance que resulta de la utilización constante de la función MAX().

A esto se le debe sumar que la contención complementaria por estar esta solución embebida en una transacción en lo referente a la obtención del id y su inserción. 

 

Utilidad

---

 

METODO 3.   Generación de ID mediante un GUID  (Global Unique Id)

Ante todo recordemos que un GUID es un tipo de datos binario de 16 bytes globalmente único en tablas, bases de datos y servidores.

EL método consiste en generar el ID de tipo uniqueidentifier e insertarlo como nuevo Id de la tabla.

Ejemplo:

 

--  1) Creación de Tabla --

Create Table Operaciones

(Id uniqueidentifier NOT NULL,

 Detalle varchar(50) NULL)

 

-- 2) Obtención @Id por medio de función --

Declare @Id uniqueidentifier

Select  @id =  NEWID()

 

-- 3) Inserción del registro en la tabla --

Insert into Operaciones(id,  Detalle)

values (@id,  'Articulo 234-e venta')

 

(*) Existe una opción de generar el id utilizando como valor por default el NEWID() o NEWSEQUENTIALID() , pero  no es considerada en este contexto en el cual debemos recuperar el id recién insertado para poder seguir insertando el mismo en otras tablas.

 

Ventajas

Se trata de un  método que nos garantiza la unicidad del ID generado para todos los objetos de nuestra base de datos.

Al ser el @Id único, no hace falta embeber el proceso de inserción dentro de una transacción evitando desde ese lado contención.

 

Desventajas

Ø Tipo de Datos poco performante y no apto para ciertas lógicas de negocio

El tipo de datos uniqueidentifier con sus 16 bytes definitivamente es un problema de performance en puerta para la buena performance de un sistema fuertemente transaccional.

Por las características típicas de este tipo de datos es poco amigable con la mayoría de las lógicas de negocio, al menos con las que uno está acostumbrado a ver.

Al tratarse de datos “amplios” necesitaremos mayor almacenamiento y mayores recursos. Recordemos también que cuando más “pesado” sea un registro menos de este tipo de registros cabrán en memoria..con las consecuencias de performance…

 

Utilidad

Es el único método que  garantiza unicidad de código en toda la base de datos.

 

 

METODO 4.   Generación de ID “on the fly”

Se trata de un método similar al anterior en el cual se obtiene un ID que, se pretende, sea único por su concepción. Se calcula a partir del uso de funciones del sistema.

Se trabaja para ello con una variable de tipo Numeric  amplia la cual permite jugar en este caso hasta con los microsegundos de la fecha del sistema para evitar de ese modo posibles duplicaciones.

 

Ejemplo:

--  1) Creación Tabla  --

Create Table Operaciones

(Id NUMERIC(19,0) NOT NULL,

 Detalle varchar(50) NULL)


-- 2) Seteo de la Pk al campo "Id"--

Alter Table [dbo].[Operaciones]

Add  Constraint [PK_Operaciones] Primary Key Clustered

([Id] ASC)

GO

 --3) Obtención de @Id “On the Fly” -–

Declare @id numeric(19,0)

Select @id = (select cast((((datediff(SECOND,'1970-01-01', getdate())

+datepart(ms,getdate())/1000.0)*1000)+datepart(mcs,getdate())/1000.2)*1000

as numeric(16,0)))


-- 4) Inserción del registro en la tabla --

Insert into Operaciones(id,  Detalle)

Values (@id,  'Articulo 234-e venta')

 

Ventajas

Es un método que obtiene muy buena performance en la obtención del ID y permite asegurar la unicidad del mismo. Recordemos que al trabajar con precisión de microsegundos es imposible que se repita el Id .

No hace falta embeber la inserción dentro de una transacción hecho que permite asegurar la buena performance.


Desventajas

Ø Tipo de Datos poco performante y no apto para ciertas lógicas de negocio

El tipo de datos tiene que tener una importante amplitud para poder asegurar la unicidad  creciendo en bytes… La performance, si bien al trabajar con un dato de tipo numérico será marcadamente mejor que la del caso anterior, no será igual de buena que trabajando con tipo de datos más pequeños.

Adicionalmente la lógica de negocios tendría que permitirnos trabajar con datos de esta amplitud.

Por último al igual que en el método anterior tendremos registros más pesados y “grandes”, malgastando recursos y reduciendo el espacio disponible en RAM.

 

Utilidad

Es un método válido para entornos con alta carga transaccional teniendo en cuenta sus “desventajas”

 

 

METODO 5.   Generación de ID en  “Dummy Table”

Este método es a mi juicio el más apto para soportar grandes cargas transaccionales con la mejor performance y sin duplición IDs

Se crea para la obtención del ID una tabla “dummy”, la cual tendrá como único objetivo el brindar el último ID secuencial.

 Esta tabla permancerá siempre sin registros. Simplemente se inserta un registro para “ mover” el identity , pasarlo a la variable @Id y borrarlo.

Ejemplo:

 

--  1) Creación Tabla “Operaciones” --

       Create Table Operaciones

       (Id int NOT NULL,

        Detalle varchar(50) NULL)

 -- 2) Seteo de la Pk al campo "Id" --

       Alter Table [dbo].[Operaciones]

       Add  Constraint [PK_Operaciones] Primary Key Clustered

       ([Id] ASC)

       GO

 -- 3) Creación de la "Dummy Table" --

       CREATE TABLE Operaciones_kEYTABLE

       (Id int NOT NULL IDENTITY(1, 1))

 -- 4) Obtención del ID desde la tabla "Dummy Table" --

       Declare @Id int   

       Begin Transaction

          Insert Operaciones_kEYTABLE DEFAULT VALUES;

          Select @Id = SCOPE_IDENTITY();

          Delete From Operaciones_kEYTABLE

       Commit Transaction

 -- 5) Inserción de un registro nuevo en la tabla “Operaciones”--

       Insert into Operaciones(id,  Detalle)

       Values (@id,  'Articulo 234-e venta')

 

Ventajas

Se trata de un  método que nos garantiza la unicidad del ID generado y la mejor performance en el proceso de inserción de la tabla “cuore”.

Al estar embebido en la transacción sólo la obtención del id en la tabla dummy no tiene casi contención.

Al ser la tabla dummy una tabla sin registros la velocidad de obención del Id es la mejor de todos los métodos tratados en este artículo.

 

Desventajas

--

Utilidad

Es el métedo más apropiado para su implementación en ambientes como el descripto al comienzo de este post.


METODO 6.   Generación de ID por medio de Sequence Object

Se trata de un método válido cuando se necesite mantener la secuencialidad de los valores de un id en forma estricta acorde a reglas de negocio. 

Se obtiene un valor para una columna Id a partir un objeto introducido en Sql Server 2012 que permite gestionar el mismo. Con alguna similaridad con la columna "identity" se diferencia de la misma en que el objeto identity no está "atado" a una tabla y por lo tanto tiene sus ventajas y desventajas que luego detallaremos.

No está dentro del scope de este artículo el explicar paso a paso su sintaxys sin embargo veremos un ejemplo extraído de la página https://www.sqlshack.com/sequence-objects-in-sql-server/ - página de un querido colega que desde ya recomiendo -.

Manos a la obra, lo primero que debemos hacer es crear un objeto sequence al que llamaremos "IdCounter", dentro de la base de datos en la que precisemos de id único y secuencial:

Use Padron

CREATE SEQUENCE [dbo].[IdCounter]

AS INT

START WITH 1

INCREMENT BY 1


Ahora procedereos a insertar registros en la tabla Students utilizando el Id provisto por el objeto creado en el paso anterior de la siguiente forma:

1
NEXT VALUE FOR [dbo].[IdCounter]

USE School
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter], 'Sally', 20 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Edward', 36 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Jon', 35)
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Scot', 41 )
INSERT INTO Students VALUES (NEXT VALUE FOR [dbo].[IdCounter],'Ben', 35 )

Luego haremos un select de la tabla y veremos como se han insertado correctamente los ids ssecuenciales generados a partir del objeto "IdCounter"

SELECT * FROM Students


Ventajas

Es un método que BIEN IMPLEMENTADO garantiza la secuencialidad de un Id , algo imposible de ser garantizado con un campo identity.


Desventajas

- Al ser el valor de un sequence updeteable, es una enorme tentación de hacerlo para alguien que tienen los privilegios dentro de la base. Luego vienen los reclamos del tipo"no se qué pasó se perdió la correlatividad"...

- Ante casos de apagados repentinos del motor, y dado que la mayoría suele hacer uso del sequence cacheado, el mismo se pierde. Se vacía el caché y se pierde..

- El sequence, al igual que el identity, no es garantía de unicidad. Por definición no
garantiza la misma y me ha ocurrido de ver duplicaciones en procesos cíclicos de obtención de id secuencial o en updates complejos.

- No se suele usar con criterio el valor default del sequence. Cuando tu vas revisar te sueles encontrar con valores por defecto definidos como integer o biginteger cuando en verdad con un small integer hubiera sido suficiente . Esto trae problemas de peformance y espacio mal utilizado.

Utilidad

Si lo utilizamos con criterio, es decir evitando las malas prácticas que devienen en sus eventuales desventajas, es un excelente método a la hora de cumplir con estrictas reglas de negocio que exigen secuencialidad sin fisuras en el  id de una tabla (ejemplo nº factura).


Amigos, es todo por ahora. Espero como siempre vuestras preguntas, apuntes y todo aquello que nos enriquezca.

Sigamos en contacto, saludos desde Argentina.