100 Errores en SQL Server y Cómo Evitarlos

-- ✅ Índice que "cubre" toda la query (sin lookups)
CREATE NONCLUSTERED INDEX IX_Email_IncludeNames
ON dbo.CustomerContacts (CustomerContactEmail)
INCLUDE (CustomerContactFirstName, CustomerContactLastName);

-- Esta query usará el índice sin tocar la tabla base
SELECT
    CustomerContactFirstName,
    CustomerContactLastName,
    CustomerContactEmail
FROM dbo.CustomerContacts
ORDER BY CustomerContactEmail;

-- ✅ Solo reconstruye lo necesario (no todo indiscriminadamente)
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = (
    SELECT
        'ALTER INDEX ' + i.name +
        ' ON ' + s.name + '.' + o.name +
        ' REBUILD WITH (ONLINE = ON, MAXDOP = 4); '
    FROM sys.dm_db_index_physical_stats(
        DB_ID(), NULL, NULL, NULL, 'DETAILED'
    ) ips
    INNER JOIN sys.indexes i
        ON i.object_id = ips.object_id
        AND i.index_id = ips.index_id
    INNER JOIN sys.objects o
        ON i.object_id = o.object_id
    INNER JOIN sys.schemas s
        ON s.schema_id = o.schema_id
    WHERE i.type_desc <> 'HEAP'
      AND o.type_desc = 'USER_TABLE'
      AND ips.avg_fragmentation_in_percent > 20
      AND ips.page_count > 1000
      AND ips.index_level = 0
    FOR XML PATH('')
);

EXEC sp_executesql @SQL;

-- ❌ Antipatrón: cursor para iterar tablas
DECLARE Tables CURSOR READ_ONLY FOR
    SELECT name FROM sys.tables;
-- ... 20+ líneas de FETCH/EXEC ...

-- ✅ Set-based con XML PATH (mucho más rápido)
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = (
    SELECT 'ALTER INDEX ALL ON ' + QUOTENAME(name) + ' REBUILD; '
    FROM sys.tables
    FOR XML PATH('')
);

EXEC sp_executesql @SQL;

-- ✅ Snapshot ANTES de un cambio crítico
CREATE DATABASE Marketing_Snapshot ON
(
    NAME = Marketing,
    FILENAME = 'D:\Snapshots\Marketing_PreDeploy.ss'
)
AS SNAPSHOT OF Marketing;

-- Si algo sale mal, revertir es casi instantáneo:
USE master;
RESTORE DATABASE Marketing
FROM DATABASE_SNAPSHOT = 'Marketing_Snapshot';

-- ⚠️ Recuerda: NO sustituye a un backup
-- (depende de la BD origen)
DROP DATABASE Marketing_Snapshot;

-- ✅ Insertar o actualizar agregaciones diarias
MERGE INTO reporting.ImpressionAggregates AS Target
USING (
    SELECT
        CampaignID,
        CountryCode,
        AVG(BidPrice) AS AvgBid,
        AVG(CostPerMille) AS AvgCPM,
        CAST(EventTime AS DATE) AS EventDate
    FROM marketing.Impressions
    WHERE EventTime >= DATEADD(DAY, -1, GETDATE())
    GROUP BY CampaignID, CountryCode, CAST(EventTime AS DATE)
) AS Source
ON Source.CampaignID = Target.CampaignID
   AND Source.CountryCode = Target.CountryCode
   AND Source.EventDate = Target.EventDate
WHEN MATCHED THEN
    UPDATE SET
        AvgBidPrice = Source.AvgBid,
        AvgCostPerMille = Source.AvgCPM
WHEN NOT MATCHED THEN
    INSERT (CampaignID, CountryCode, EventDate, AvgBidPrice, AvgCostPerMille)
    VALUES (Source.CampaignID, Source.CountryCode, Source.EventDate,
            Source.AvgBid, Source.AvgCPM);

DECLARE @json NVARCHAR(MAX) = N'{
  "EmployeeAddress": [{
    "EmployeeID": 1,
    "Address": {
      "Line1": "5331 Rexford Court",
      "City": "Montgomery",
      "State": "AL",
      "ZipCode": "36116"
    }
  }]
}';

-- ✅ Extraer un valor escalar
SELECT JSON_VALUE(@json, '$.EmployeeAddress[0].EmployeeID');

-- ✅ Convertir JSON → tabla relacional
SELECT *
FROM OPENJSON(@json, '$.EmployeeAddress[0]')
WITH (
    EmployeeID SMALLINT '$.EmployeeID',
    Line1      NVARCHAR(64)  '$.Address.Line1',
    City       NVARCHAR(64)  '$.Address.City',
    [State]    NCHAR(2)      '$.Address.State',
    ZipCode    NVARCHAR(10)  '$.Address.ZipCode'
);

-- ✅ Generar JSON desde resultado
SELECT
    1 AS 'EmployeeID',
    '5331 Rexford Court' AS 'Address.Line1',
    'Montgomery' AS 'Address.City'
FOR JSON PATH, ROOT('EmployeeAddress');

-- ✅ 1. Estimar antes de aplicar
EXEC sp_estimate_data_compression_savings
    @schema_name      = 'dbo',
    @object_name      = 'ImpressionsArchive',
    @index_id         = NULL,    -- Todos los índices
    @partition_number = NULL,
    @data_compression = 'PAGE';  -- Probar también 'ROW'

-- ✅ 2. Aplicar si la ratio compensa la CPU extra
ALTER TABLE dbo.ImpressionsArchive
REBUILD WITH (
    DATA_COMPRESSION = PAGE,
    ONLINE = ON,
    MAXDOP = 2
);

-- ✅ 3. Verificar el ahorro real
SELECT
    OBJECT_NAME(object_id) AS Tabla,
    in_row_used_page_count AS Paginas,
    (in_row_used_page_count * 8) / 1024 AS MB
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('dbo.ImpressionsArchive')
  AND index_id = 1;

-- ✅ 1. Crear el audit (escribe al Security Log de Windows)
USE master;
CREATE SERVER AUDIT AdminActivityAudit
    TO SECURITY_LOG
    WITH (ON_FAILURE = CONTINUE);

-- ✅ 2. Especificar QUÉ auditar
CREATE SERVER AUDIT SPECIFICATION AdminActivitySpec
FOR SERVER AUDIT AdminActivityAudit
    ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
    ADD (AUDIT_CHANGE_GROUP),
    ADD (DBCC_GROUP),
    ADD (SERVER_PERMISSION_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_CHANGE_GROUP),
    ADD (SERVER_PRINCIPAL_IMPERSONATION_GROUP),
    ADD (SERVER_OBJECT_CHANGE_GROUP),
    ADD (SERVER_STATE_CHANGE_GROUP);

-- ✅ 3. Activar
ALTER SERVER AUDIT AdminActivityAudit
    WITH (STATE = ON);
ALTER SERVER AUDIT SPECIFICATION AdminActivitySpec
    WITH (STATE = ON);

-- ✅ Tamaño actual y espacio libre por archivo
SELECT
    DB_NAME(database_id)                 AS DataBase_Name,
    Name                                  AS Logical_Name,
    Physical_Name                         AS Physical_Name,
    (size * 8) / 1024                     AS TotalSpace_MB,
    (FILEPROPERTY(name, 'SpaceUsed') * 8) / 1024 AS UsedSpace_MB,
    ((size - FILEPROPERTY(name, 'SpaceUsed')) * 8) / 1024 AS FreeSpace_MB,
    growth                                AS Growth_Setting,
    is_percent_growth                     AS Is_Percent_Growth
FROM sys.master_files
WHERE database_id = DB_ID()
ORDER BY DataBase_Name, Logical_Name;

-- ✅ VLFs activos en el log (detectar fragmentación)
SELECT
    total_vlf_count,
    active_vlf_count,
    log_truncation_holdup_reason
FROM sys.dm_db_log_stats(DB_ID());

-- ✅ Activar Query Store (gratis, low-overhead)
ALTER DATABASE Marketing
SET QUERY_STORE = ON (
    OPERATION_MODE      = READ_WRITE,
    CLEANUP_POLICY      = (STALE_QUERY_THRESHOLD_DAYS = 30),
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    MAX_STORAGE_SIZE_MB = 1000,
    INTERVAL_LENGTH_MINUTES = 60
);

-- ✅ Activar feedback automático de paralelismo (SQL 2022+)
ALTER DATABASE SCOPED CONFIGURATION
    SET DOP_FEEDBACK = ON;

-- ✅ Top 10 queries más costosas en CPU
SELECT TOP 10
    qsq.query_id,
    qsqt.query_sql_text,
    SUM(qsrs.count_executions)     AS executions,
    AVG(qsrs.avg_duration / 1000.0) AS avg_duration_ms,
    AVG(qsrs.avg_cpu_time / 1000.0) AS avg_cpu_ms
FROM sys.query_store_query qsq
INNER JOIN sys.query_store_query_text qsqt
    ON qsq.query_text_id = qsqt.query_text_id
INNER JOIN sys.query_store_plan qsp
    ON qsq.query_id = qsp.query_id
INNER JOIN sys.query_store_runtime_stats qsrs
    ON qsp.plan_id = qsrs.plan_id
GROUP BY qsq.query_id, qsqt.query_sql_text
ORDER BY AVG(qsrs.avg_cpu_time) DESC;

-- ✅ CHECKSUM en cada página al escribirla
ALTER DATABASE Marketing SET PAGE_VERIFY CHECKSUM;

-- Verificar la configuración actual
SELECT name, page_verify_option_desc
FROM sys.databases
WHERE page_verify_option_desc <> 'CHECKSUM';
-- Cualquier resultado aquí es un problema.

Configuration InstallSql {
    param (
        $SqlInstanceName = 'MSSQLSERVER',
        $Edition
    )

    Import-DscResource -ModuleName SqlServerDsc

    node localhost {
        SqlSetup 'InstallInstance' {
            InstanceName        = $SqlInstanceName
            Features            = 'SQLENGINE'
            SourcePath          = 'C:\SQL Media'
            SQLSysAdminAccounts = @('Administrator')
            ProductKey          = $ProductKey
        }

        SqlConfiguration 'RemoteAccess' {
            InstanceName = $SqlInstanceName
            OptionName   = 'remote access'
            OptionValue  = 0
            DependsOn    = '[SqlSetup]InstallInstance'
        }

        SqlLogin 'AddSqlAdmin' {
            Ensure       = 'Present'
            Name         = 'DOMAIN\sqladmins'
            InstanceName = $SqlInstanceName
            LoginType    = 'WindowsGroup'
            DependsOn    = '[SqlSetup]InstallInstance'
        }
    }
}

# ✅ 1. Crear volumen Docker para persistir datos
sudo docker volume create sqldata

# ✅ 2. Levantar contenedor con volumen montado
sudo docker run \
    -e "ACCEPT_EULA=Y" \
    -e "MSSQL_SA_PASSWORD=Pa\$\$w0rd!" \
    -p 1433:1433 \
    --name production-sql \
    --hostname production-sql \
    -v sqldata:/var/opt/mssql \
    -d mcr.microsoft.com/mssql/server:2022-latest

# ✅ 3. Conectarte
docker exec -it production-sql /opt/mssql-tools/bin/sqlcmd \
    -S localhost -U sa -P 'Pa$$w0rd!'

-- ✅ 1. Crear operador (destinatario)
EXEC msdb.dbo.sp_add_operator
    @name           = 'DBA_Team',
    @enabled        = 1,
    @email_address  = 'dba@empresa.com';

-- ✅ 2. Alerta para errores de severidad alta (>= 19)
EXEC msdb.dbo.sp_add_alert
    @name             = 'High Severity Errors',
    @severity         = 19,
    @enabled          = 1,
    @delay_between_responses = 60,
    @include_event_description_in = 1;

EXEC msdb.dbo.sp_add_notification
    @alert_name         = 'High Severity Errors',
    @operator_name      = 'DBA_Team',
    @notification_method = 1;  -- email

-- ✅ 3. Alerta para xp_cmdshell (custom error 50001)
EXEC msdb.dbo.sp_add_alert
    @name        = 'xp_cmdshell Activation Attempt',
    @message_id  = 50001,
    @enabled     = 1;

EXEC msdb.dbo.sp_add_notification
    @alert_name         = 'xp_cmdshell Activation Attempt',
    @operator_name      = 'DBA_Team',
    @notification_method = 1;

-- ✅ Sesiones bloqueadas + bloqueador + query SQL
SELECT
    blocking.session_id    AS BlockerSPID,
    blocked.session_id     AS BlockedSPID,
    blocked.wait_type,
    blocked.wait_time / 1000 AS WaitSeconds,
    blocked.last_wait_type,
    bl_text.text           AS BlockerSQL,
    bd_text.text           AS BlockedSQL,
    DB_NAME(blocked.database_id) AS DBName
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions blocker_sess
    ON blocked.blocking_session_id = blocker_sess.session_id
INNER JOIN sys.dm_exec_requests blocking
    ON blocking.session_id = blocker_sess.session_id
CROSS APPLY sys.dm_exec_sql_text(blocking.sql_handle) bl_text
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle)  bd_text
WHERE blocked.blocking_session_id <> 0;

-- ✅ Top 5 wait types desde el último restart
SELECT TOP 5
    wait_type,
    waiting_tasks_count,
    wait_time_ms / 1000 AS wait_seconds,
    (wait_time_ms / waiting_tasks_count) AS avg_wait_ms
FROM sys.dm_os_wait_stats
WHERE waiting_tasks_count > 0
  AND wait_type NOT IN (
      'CLR_AUTO_EVENT', 'WAITFOR', 'SLEEP_TASK',
      'BROKER_TASK_STOP', 'LAZYWRITER_SLEEP'
  )
ORDER BY wait_time_ms DESC;

<!-- ✅ Properties recomendadas en el Data Flow Task -->
<DataFlow>
    <DefaultBufferMaxRows>50000</DefaultBufferMaxRows>
    <AutoAdjustBufferSize>True</AutoAdjustBufferSize>
    <EngineThreads>10</EngineThreads>
</DataFlow>

<!-- ✅ OLE DB Destination Fast Load -->
<OleDbDestination>
    <FastLoadOptions>TABLOCK,CHECK_CONSTRAINTS</FastLoadOptions>
    <MaximumInsertCommitSize>50000</MaximumInsertCommitSize>
    <Rows per batch>50000</Rows per batch>
</OleDbDestination>

<!-- ⚠️ Antes de bulk load: deshabilitar índices NCI -->

# ✅ Generar script de deploy desde DACPAC sin aplicarlo
SqlPackage.exe `
    /Action:Script `
    /SourceFile:"C:\Builds\Marketing.dacpac" `
    /TargetServerName:"SQLPROD01" `
    /TargetDatabaseName:"Marketing" `
    /OutputPath:"C:\Deploy\preview.sql" `
    /p:BlockOnPossibleDataLoss=true `
    /p:GenerateSmartDefaults=true

# ✅ Aplicar tras revisión humana
SqlPackage.exe `
    /Action:Publish `
    /SourceFile:"C:\Builds\Marketing.dacpac" `
    /TargetServerName:"SQLPROD01" `
    /TargetDatabaseName:"Marketing" `
    /p:BlockOnPossibleDataLoss=true

-- ✅ Activar auto-update asíncrono (no bloquea queries)
ALTER DATABASE Marketing SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE Marketing SET AUTO_UPDATE_STATISTICS_ASYNC ON;

-- ❌ NO hagas esto después de un REBUILD:
-- UPDATE STATISTICS ... -- empeora el muestreo (el rebuild ya las tiene al 100%)

-- ✅ Si necesitas actualizar manualmente, solo COLUMNS:
UPDATE STATISTICS marketing.Impressions
WITH FULLSCAN, COLUMNS;

-- ✅ Ver edad de las estadísticas
SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1
ORDER BY sp.last_updated;