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.

   

 

SQL Server Profiler - Scripteando Un Traceo en el Servidor - (Server Side Trace)

Amigos, cuando estudiamos problemas de performance en un server, sin lugar a dudas uno de nuestros aliados es el PROFILER.

   Esta herramienta nos permite recolectar distintos valores e incluso sentencias que están corriendo en nuestro server.
     Ahora bien, qué ocurre si queremos tracear una base de datos por espacio prolongado en horas?  Seguramente trataremos en primera instancia el hacerlo utilizando la vía más fácil, es decir, la Consola Sql Profiler desde fuera del server.
     El gran problema del uso de la consola del Profiler en estos casos es que,  corrida desde la consola a nivel de usuario contra el servidor, es pasible de sufrir interrupciones ante la pérdida de conexión con el server.  Estas interrupciones nos van a impedir la colección de datos necesarios para atacar el problema que estamos buscando.

  Cuál sería la solución al problema si debemos tracear un servidor por unas cuantas horas?

  La solución, y a la vez el objetivo de este post, es  generar un script que nos permita hacer el mismo traceo que con el Profiler, pero del lado del server. En inglés se lo conoce como “Server Side Trace”y consta de los siguientes comandos, veamos:

sp_trace_create : Crea la definición del nuevo traceo que se inicializará como “stopeado”

Su sintaxis es la siguiente:
sp_trace_create @TraceID OUTPUT, @Options, @fileName, @maxFileSize, @Stoptime, @FileCount  

Vemos los parámetros a definir:

@TraceID = es el nº de id que el sql le da por default al traceo. Sirve para identificar al mismo posteriormente. Se recomienda utilizar una variable integer y dejarla nula (ver en el ejemplo)

@Options =  Aquí podemos setear un 2 como valor en cuyo caso cuando se alcance el máximo tamaño por archivo se generará un nuevo sin borrar el anterior con el mismo nombre y un integer adosado al mismo…

                  … O un 4, que especifica que , ante una falla que no le permita al trace escribir sobre el archivo sql server se apagará. Opción útil para traces en traceos de seguridad.

@FileName =  Donde debe definirse la ruta y el nombre del file donde se va a grabar el trace. Ejemplo 'C:\filetrace’. Aquí debemos indicar que el file será acompañador con una extensión .trc que el sql le adosará en forma automática y que el mismo NO DEBE UTILIZAR EL CARÁCTER “UDERSCORE” es decir nunca utilizar un nombre como este “my_trace”.

@maxFileSize = donde se especifica el máximo número en mb que un  archivo de traceo puede alcanzar. El default es 5 mb.

 @stoptime= su valor puede ser nulo y es donde se especifica el horario en el cual el trace debe detenerse (este valor tiene precedencia sobre el @maxfilesize cuando no se especifica el valor 2 en options.

@filecount=   puede ser nulo o no. Su valor si se setea debe ser un integer mayor a 1 . Con este parámetro se determinan la máxima cantidad de files que el sql server va a mantener para este traceo antes de borrar el más viejo si la opción @Options = 2

Las posibles respuestas que debemos validar:

Return code            Description

0              No error.

1              Unknown error.

10            Invalid options. Returned when options specified are incompatible.

12            File not created.

13            Out of memory. Returned when there is not enough memory to perform the specified action.

14            Invalid stop time. Returned when the stop time specified has already happened.

15            Invalid parameters. Returned when the user supplied incompatible parameters.

sp_trace_setevent : Permite agregar eventos a tracear al trace previamente creado y stopeado

Su sintaxis es la siguiente:

sp_trace_setevent @TraceID, @EventId, @ColumId, @on

Vemos los parámetros a definir:

@TraceID = es el nº de del trace al cual vamos a agregarle eventos

@EventID = es el id del evento a agregar y puede ser uno a más de los sgtes:

Event number         Event name             Description

10            RPC:Completed       Occurs when a remote procedure call (RPC) has completed.

12            SQL:BatchCompleted              Occurs when a Transact-SQL batch has completed.

15            Audit Logout          Occurs when a user logs out of SQL Server.

16            Attention Occurs when attention events, such as client-interrupt requests or broken client connections, happen.

El listado total de eventos se encuentra en https://msdn.microsoft.com/en-us/library/ms186265.aspx

@ColumnId = es el id de columna a ser agregado para el evento. Los valores:

Column number     Column name         Description

1              TextData Text value dependent on the event class that is captured in the trace.

2              BinaryData              Binary value dependent on the event class captured in the trace.

3              DatabaseID             ID of the database specified by the USE database statement, or the default database if no USE database statement is issued for a given connection.

El listado total de eventos se encuentra en https://msdn.microsoft.com/en-us/library/ms186265.aspx

@On = es un valor de tipo “bit”y puede tener un 0 o un 1

1 = “on” el evento es “prendido”

0 = “off” el evento es “apagado”

sp_trace_filter : Determina los filtros a setear para el trace

Su sintaxis es la siguiente:

sp_trace_filter @TraceID, @ColumnId, @LogicalOperator, @Comparison_Operator, @Value  

Vemos los parámetros a definir:

@TraceID = es el nº de del trace al cual vamos a agregarle eventos

@ColumnId = es el id de columna sobre la que establecemos el filtro

@LogicalOperator = es un integer = 0 si la operación es AND, =1 si es OR

@Comparison Operator = es un integer que puede representar alguna de las siguientes:

Valor   Operador de comparación

0              (Es igual a)

1              (No es igual a)

2              (Mayor que)

3              (Menor que)

4              (Mayor o igual que)

5              (Less Than Or Equal)

6              LIKE

7              NOT LIKE

@Value = valor que se va filtrar (puede incluir % si lo precede un operador lógico)

sp_trace_status : Modifica el estado actual del trace

Su sintaxis es la siguiente:

sp_trace_status @TraceID, @Status

Vemos los parámetros a definir:

@TraceID = es el nº de del trace

@ColumnId = es un integer que determina la acción a tomar acorde a la sgte tabla:

Status  Description

0              Detiene el seguimiento especificado.

1              Inicia el seguimiento especificado.

2              Cierra el seguimiento especificado y elimina su definición del servidor.

Y bien amigos les propongo un script de ejemplo en el cual vamos a tracear el evento "Scan”con el objetivo de entender en que instancias nuestro motor ejecuta esta operación tan poco performante y tomar las acciones correctivas posteriormente.

/***************************************************/

-- Traceo Por Script Desde el Servidor            --

-- Autor: Gustavo Herrera - Sql Server Para Todos --

-- Detalle: Se tracea el evento "Scan: Started"   --

/***************************************************/

-- Declaración de Variables

DECLARE

@Trc INT ,

@TraceID INT,

@MaxFileSize bigint,

@FileName NVARCHAR(128),

@On bit,

@MaxDatetime datetime,

@FileCount int

-- Set variables

SET @MaxFileSize =  500 -- (500 mb)

SET @FileName = 'C:\TrcBuscaScan'  -- Ubicación de los Files

SET @On = 1

SET @FileCount = 2  -- que vaya pisando c/2 files

SET @MaxDatetime = '2017-03-17 23:59:59' -- hora de fin del traceo

-- Definimos el Trace y guardamos el resultado en @Trc

EXEC @Trc = sp_trace_create @TraceID output, 2, @fileName, @maxFileSize, @MaxDatetime, @FileCount

-- Si La Definición Da Error que salgo por error

IF (@Trc != 0) GOTO error

-- Seteo el evento a tracear y las columnas que deseo

EXEC sp_trace_setevent @TraceID, 51,  1, @on  --TextData

EXEC sp_trace_setevent @TraceID, 51, 11, @on  --LoginName

EXEC sp_trace_setevent @TraceID, 51, 12, @on  --Spid

EXEC sp_trace_setevent @TraceID, 51, 13, @on  --Duration

EXEC sp_trace_setevent @TraceID, 51, 14, @on  --StarTime

EXEC sp_trace_setevent @TraceID, 51, 15, @on  --EndTime

EXEC sp_trace_setevent @TraceID, 51, 16, @on  --Reads

EXEC sp_trace_setevent @TraceID, 51, 17, @on  --Writes

EXEC sp_trace_setevent @TraceID, 51, 18, @on  --CPU

-- Seteo Filtros

-- Filtro: solo traceamos sobre la base de datos con iddatabase = 6

EXEC sp_trace_setfilter @TraceID, 3, 1, 0, 6

-- filtro2: excluímos la app SQL Profiler

EXEC sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'

-- Comezamos el traceo

EXEC sp_trace_setstatus @TraceID, 1

-- Muestra en pantalla el trace id 

SELECT TraceID=@TraceID 

GOTO finish 

-- Atrpamos el error

error: 

SELECT ErrorCode=@Trc 

-- exit

finish: 

GO


Eso es todo amigos, espero que les haya sido útil. Saludos desde Argentina.

Extraer Un String De Longitud Variable Desde Dentro de Otro String o Cadena de Caracteres

Amigos,

   Todo DBA ha recibido alguna vez la pregunta de algún desarrollador, (con cara de pánico), del estilo "Che, vos que tenés experiencia en T-SQL.. Cómo hago para extraer un valor (supongamos dinero), que no siempre va a tener la misma longitud, desde dentro de un string?"

   Pues bien, ante todo lo importante es tranqulizar al desarrollador y explicarle las cosas con el siguiente ejemplo:

    1)  En un variable llamada @document,  tenemos un string dentro del cual debemos aislar  y extraer una cifra de longitud variable que se encuentra luego del string "fee=".
    Veamos...

    DECLARE @document varchar(64)

  SELECT @document = '123456;fee=260900;fee_SMT_Bycycle'

       
     La cifra a extraer es "260900" en este caso.

     La cosa no sería compleja si sabemos que siempre la cifra tendrá la misma longitud... (para el ejemplo 6 caractered).  Nos podríamos arreglar en ese caso con esta simple query que combina las funciones Substring y Charindex (cuya sintaxis excede el contenido de este artículo)

      SELECT SUBSTRING (@Document, (CHARINDEX('fee=', @document)+4), 6)

   Resultado  260900

    Pero dijimos que la cifra no siempre va a tener 6 caracteres, puede que tenga más o menos... Uyyyy pero esto es complicado!  No no amigos, a no marearse. La solución pasa por buscar un punto de referencia que nos indique la posición dentro de la cadena donde está el string (en este caso la cifra), que queremos extraer y otra referencia que nos marque la posición dentro de la cadena en donde finaliza dicho string (cifra).

       Ok .. manos a la obra...

       - Posición en donde empieza el string... Pues sabemos que empieza luego del "fee=". Con lo cual podemos obtener su ubicación con un simple charindex a saber:

   SELECT (CHARINDEX('fee=', @document)+4)  -- comienza en la posición 12

      - Posición donde termina el string... Aquí les propongo utilizar como referencia el ';' ubicado ni bien termina la cifra. 
          Y uds. me dirán... "Pero Gustavo, hay más de un punto y coma ';' en el string!!! Cómo haremos para saber la posición del ';' que da finalización a la cifra????" Y aquí es donde el tercer parámetro (opcional),  de la función CHARINDEX nos va dar una enorme mano... Cómo? Indicando que comience a buscar el ';' luego de la posición del string "'fee='. Veamos:

      SELECT CHARINDEX (';', @document, CHARINDEX('fee=', @document))  -- el punto y coma se encuentra en la posición 18

    - Ahora si... sacando la diferencia entre la posición de fin y la posición de comienzo del string a aislar (de la cifra para nuestro ejemplo), podremos extraerlo siemrpe más allá de su longitud. Cómo? Sencillo .. Utilizando la función SUBSTRING sobre la cadena. Marcándole como lugar de comienzo (CHARINDEX('fee=', @document)+4)y como longitud a abstraer aquella que nos da la la diferencia de los dos charindex utilizados es decir...  CHARINDEX (';', @document, CHARINDEX('fee=', @document))- (CHARINDEX('fee=', @document)+4)

     VAMOS YA MISMO LA SOLUCION DEL PROBLEMA PLANTEADO!! CON EL EJEMPLO COMPLETO:

-- String Inicial -- (desafío extraer la cifra de logitud variable luego del "fee="

DECLARE @document varchar(64)

SELECT @document = '123456;fee=260900;fee_SMT_Bycycle'

--Con el uso del Substring y los charindex explicados extraemos la cadena

select SUBSTRING (@document,  (CHARINDEX('fee=', @document)+4) ,  CHARINDEX (';', @document, CHARINDEX('fee=', @document))- (CHARINDEX('fee=', @document)+4))

RESULTADO 260900

      

  Amigos, les animo a que prueben la solución y me hagan las preguntas que crean pertinentes.

  Un saludo cordial desde Argentina.
         

HASH WARNING - qué es y cómo evitarlo -

   Amigos,

   Que tal... Hoy quiero contarles acerca de los "HASH WARNINGS" que uds. muy probablemente hayan encontrado estudiando sus planes de ejecución o bien auditando el SqlSrv a través de la consulta del trace file (ver http://gherrerasqlserver.blogspot.com.ar/2011/09/auditando-sql-server-consultando-el.html) etc

   Qué indica un "Hash Warning"?

   Indica que nuestro motor de base de datos está leyendo o escribiendo datos desde el disco (TempDb), algo muy malo para nuestra performance.

  ORIGEN DEL PROBLEMA

   El Sql Server hace un Hash cuando tiene que joinear dos tablas y se encuentra con que los campos correspondientes al Join no están ordenados por la falta de idx o bien, están indexados pero las estadísticas que subyacen de esos idx están desactualizadas

   El Hash es un algoritmo que utiliza el motor de nuestra base de datos para resolver los joins cuando no encuentra los campos ordenados (indexados). Concretamente separa en pequeños grupos los registros correspondientes a esos campos para poder matchearlos con mayor facilidad. Cuando esos grupos exceden la capacidad de almancenamiento en memoria, el sql los vuelca a la TemDb qenerando el no deseado HASH WARNING.

  SOLUCION DEL PROBLEMA

   -          Revisar que siempre las columnas de un join tengan su correspondiente idx

   -          Tener actualizadas las estadísticas de esos idx (nosotros tenemos un sp que una vez por semana se encarga de eso)

   -          Si el problema persiste ir hacia otro tipo de join, por ejemplo el merge join.


    A tenerlo en cuenta y a poner en sintonía a los desarrolladores que poco saben de los problemas que pueden acarrear sus códigos pocos cuidadosos.

   
      Les saluda Gustavo Herrera desde Argentina.