SQL Server - Cómo identificar, monitorear y prevenir overflow en columnas con IDENTITY y SEQUENCE de tablas con muchos registros

Hola, chicos,
¡Buenas tardes!

En este post te demostraré cómo prevenir un tipo de problema en SQL Server que puede causar mucha interrupción en la vida de un DBA, toma mucho tiempo en resolverse y puede evitarse y monitorearse fácilmente, que es cuando una columna IDENTIDAD termina alcanzando el valor límite de su tipo de datos y al intentar insertar nuevos registros en la tabla, verás un mensaje de error como este:

Mensaje 8115, Nivel 16, Estado 1, Línea 18
Error de desbordamiento aritmético al convertir IDENTIDAD al tipo de datos tinyint.
Se produjo un desbordamiento aritmético.

Este error ocurre porque cada tipo de datos tiene un valor máximo que puede alcanzar. El DBA siempre debe monitorear si este valor no se acerca al límite, porque cuando esto sucede, no se escriben nuevos registros.

En un escenario de producción, esto es un desastre para la empresa e incluso si el DBA intenta actuar rápidamente, una operación de cambio de tipo, especialmente cuando la tabla desborda el INT, tarda mucho en realizarse y esto en pleno horario comercial, es realmente un problema muy grave.

Los tipos de datos más utilizados en columnas con IDENTIDAD y los valores permitidos son:

tipo de datos	Intervalo
diminuto	0 a 255 (1 byte)
pequeño	-32.768 a 32.767 (2 bytes)
entero	-2.147.483.648 a 2.147.483.647 (4 bytes)
bigint	-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807 (8 bytes)

Aunque solo enumeré los tipos anteriores, también puedes usar NUMÉRICO y DECIMAL para formar tu IDENTIDAD, aunque no es muy común. Sin embargo, el tamaño máximo de NUMERIC y DECIMAL dependerá de la escala y precisión que declare en la columna.

En este post también monitorearemos las SECUENCIAS, que se implementaron a partir de SQL Server 2012, y que también pueden terminar llegando al límite y generando serios problemas en producción. Si no está familiarizado con la función SECUENCIA de SQL Server, lea la publicación Trabajar con secuencias en SQL Server.

Una forma práctica de simular este problema es ejecutando los siguientes comandos:

CREATE TABLE #TesteTinyInt (
    Id TINYINT IDENTITY(1, 1),
    Nome VARCHAR(50)
)

DECLARE @Contador INT = 1, @Total INT = 255

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO #TesteTinyInt ( Nome )
    VALUES ( ' Teste ' + CAST(@Contador AS VARCHAR(10)) )

    SET @Contador += 1
    
END

-- Aqui vai dar erro
INSERT INTO #TesteTinyInt ( Nome )
VALUES ( ' Teste Overflow ' )

Si desea crear un monitoreo para este tipo de situación o simplemente ver la situación actual de las bases de datos de su instancia, simplemente ejecute la siguiente consulta (también monitorea el valor máximo de SECUENCIAS):
Ver código fuente

IF (OBJECT_ID('tempdb..#Valor_Maximo_Tipo_Dado') IS NOT NULL) DROP TABLE #Valor_Maximo_Tipo_Dado
CREATE TABLE #Valor_Maximo_Tipo_Dado (
    Ds_Tipo_Dado VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AI, 
    Vl_Maximo BIGINT
)

INSERT INTO #Valor_Maximo_Tipo_Dado 
VALUES 
   ('tinyint' , 255),
   ('smallint' , 32767),
   ('int' , 2147483647),
   ('bigint' , 9223372036854775807)



---------------------------------------------------------------------------------------------------
-- TAMANHO MÁXIMO DE IDENTITY
---------------------------------------------------------------------------------------------------
   
IF (OBJECT_ID('tempdb..#Tamanho_Maximo_Identity') IS NOT NULL) DROP TABLE #Tamanho_Maximo_Identity
CREATE TABLE #Tamanho_Maximo_Identity (
    Ds_Database sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Tabela sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Coluna sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Tipo_Dado sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Vl_Maximo bigint NULL,
    Vl_Inicial bigint NULL,
    Vl_Incremento bigint NULL,
    Vl_Ultimo_Valor bigint NULL,
    Qt_Linhas bigint NULL,
    Pr_Atingimento decimal (18, 2) NULL
)


EXEC master.dbo.sp_MSforeachdb '
IF (''?'' NOT IN (''msdb'', ''master'', ''model'', ''tempdb''))
BEGIN

    INSERT INTO #Tamanho_Maximo_Identity
    SELECT DISTINCT
        ''?'' AS Ds_Database,
        B.name AS Ds_Tabela,
        A.name AS Ds_Coluna,
        C.name AS Ds_Tipo_Dado,
        D.Vl_Maximo,
        CONVERT(VARCHAR(20), A.seed_value) AS Vl_Inicial,
        CONVERT(VARCHAR(20), A.increment_value) AS Vl_Incremento, 
        CONVERT(VARCHAR(20), A.last_value) AS Vl_Ultimo_Valor,
        E.row_count AS Qt_Linhas,
        (CONVERT(FLOAT, CONVERT(VARCHAR(20), A.last_value)) * 100 / D.Vl_Maximo) AS Pr_Atingimento
    FROM 
        [?].sys.identity_columns                A   WITH(NOLOCK)
        JOIN [?].sys.tables                     B   WITH(NOLOCK)    ON  A.[object_id] = B.[object_id]
        JOIN [?].sys.types                      C   WITH(NOLOCK)    ON  A.system_type_id = C.system_type_id
        JOIN #Valor_Maximo_Tipo_Dado            D   WITH(NOLOCK)    ON  C.name COLLATE SQL_Latin1_General_CP1_CI_AI = D.Ds_Tipo_Dado
        JOIN [?].sys.dm_db_partition_stats      E   WITH(NOLOCK)    ON  E.[object_id] = A.[object_id]
        JOIN [?].sys.indexes                    F   WITH(NOLOCK)    ON  E.index_id = F.index_id
    WHERE 
        E.row_count > 0


END'


SELECT * 
FROM #Tamanho_Maximo_Identity
WHERE Pr_Atingimento > 50
ORDER BY Pr_Atingimento DESC



---------------------------------------------------------------------------------------------------
-- TAMANHO MÁXIMO DE SEQUENCES (Se você estiver antes do SQL 2012, vai dar erro)
---------------------------------------------------------------------------------------------------

IF (OBJECT_ID('tempdb..#Tamanho_Maximo_Sequence') IS NOT NULL) DROP TABLE #Tamanho_Maximo_Sequence
CREATE TABLE #Tamanho_Maximo_Sequence (
    Ds_Database sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Sequence sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Ds_Tipo_Dado sysname COLLATE SQL_Latin1_General_CP1_CI_AI NULL,
    Vl_Maximo_Tipo_Dado bigint NULL,
    Vl_Inicial bigint NULL,
    Vl_Incremento bigint NULL,
    Vl_Minimo bigint NULL,
    Vl_Maximo bigint NULL,
    Vl_Ultimo_Valor bigint NULL,
    Pr_Atingimento_Tipo decimal (18, 2) NULL,
    Pr_Atingimento decimal (18, 2) NULL
)

EXEC master.dbo.sp_MSforeachdb '
IF (''?'' NOT IN (''msdb'', ''master'', ''model'', ''tempdb''))
BEGIN

    INSERT INTO #Tamanho_Maximo_Sequence
    SELECT DISTINCT
        ''?'' AS Ds_Database,
        A.name AS Ds_Sequence,
        B.name AS Ds_Tipo_Dado,
        C.Vl_Maximo AS Vl_Maximo_Tipo_Dado,
        CONVERT(VARCHAR(20), A.start_value) AS Vl_Inicial,
        CONVERT(VARCHAR(20), A.increment) AS Vl_Incremento, 
        CONVERT(VARCHAR(20), A.minimum_value) AS Vl_Minimo,
        CONVERT(VARCHAR(20), A.maximum_value) AS Vl_Maximo,
        CONVERT(VARCHAR(20), A.current_value) AS Vl_Ultimo_Valor,
        (CONVERT(FLOAT, CONVERT(VARCHAR(20), A.current_value)) * 100 / C.Vl_Maximo) AS Pr_Atingimento_Tipo,
        (CONVERT(FLOAT, CONVERT(VARCHAR(20), A.current_value)) * 100 / CONVERT(BIGINT, CONVERT(VARCHAR(20), A.maximum_value))) AS Pr_Atingimento
    FROM 
        [?].sys.sequences                       A   WITH(NOLOCK)
        JOIN [?].sys.types                      B   WITH(NOLOCK)    ON  A.system_type_id = B.system_type_id
        JOIN #Valor_Maximo_Tipo_Dado            C   WITH(NOLOCK)    ON  B.name COLLATE SQL_Latin1_General_CP1_CI_AI = C.Ds_Tipo_Dado


END'


SELECT * 
FROM #Tamanho_Maximo_Sequence
WHERE Pr_Atingimento > 50
ORDER BY Pr_Atingimento DESC


SELECT * 
FROM #Tamanho_Maximo_Sequence
WHERE Pr_Atingimento_Tipo > 50
ORDER BY Pr_Atingimento_Tipo DESC

Si no tienes una base para realizar esta prueba y quieres crear la tuya propia solo para ver cómo queda el resultado, utiliza esta consulta:
Ver código fuente

IF (OBJECT_ID('dbo.TesteTinyInt') IS NOT NULL) DROP TABLE dbo.TesteTinyInt
CREATE TABLE dbo.TesteTinyInt (
    Id TINYINT IDENTITY(1, 1),
    Nome VARCHAR(50)
)


DECLARE @Contador INT = 1, @Total INT = 255

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO dbo.TesteTinyInt ( Nome )
    VALUES ( ' Teste ' )

    SET @Contador += 1
    
END



IF (OBJECT_ID('dbo.TesteSmallInt') IS NOT NULL) DROP TABLE dbo.TesteSmallInt
CREATE TABLE dbo.TesteSmallInt (
    Id SMALLINT IDENTITY(1, 1),
    Nome VARCHAR(50)
)


SET @Contador = 2
SET @Total = 15

INSERT INTO dbo.TesteSmallInt ( Nome )
VALUES ( ' Teste ' )

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO dbo.TesteSmallInt ( Nome )
    SELECT Nome FROM dbo.TesteSmallInt

    SET @Contador += 1
    
END


-- Cuidado, pois aqui vai demorar um pouco e consumir muita TempDB
IF (OBJECT_ID('dbo.TesteInt') IS NOT NULL) DROP TABLE dbo.TesteInt
CREATE TABLE dbo.TesteInt (
    Id INT IDENTITY(1, 1),
    Nome VARCHAR(50)
)

SET @Contador = 1
SET @Total = 20

INSERT INTO dbo.TesteInt ( Nome )
VALUES ( ' Teste ' )

WHILE(@Contador <= @Total)
BEGIN
    
    INSERT INTO dbo.TesteInt ( Nome )
    SELECT nome FROM dbo.TesteInt

    SET @Contador += 1
    
END


-- As Sequences foram implementadas no SQL Server 2012. 
-- Se você usa uma versão anterior, não rode daqui pra baixo, senão vai dar erro na sua execução.
CREATE SEQUENCE dbo.SEQ01_Teste AS TINYINT
START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 200
GO


SET @Contador = 1
SET @Total = 180

-- Incrementa a Sequence
WHILE(@Contador <= @Total)
BEGIN
    
    SELECT NEXT VALUE FOR dbo.SEQ01_Teste

    SET @Contador += 1
    
END

Resultado de la consulta:

¡Y eso es todo, amigos!
Espero que hayas disfrutado de esta publicación y nos vemos la próxima.

Dirceu Resende

Arquitecto de Bases de Datos y BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Ver mis certificaciones

Comentários (0)

Carregando comentários…