Mejores prácticas: SQL Server

De Vocalcom.latam Wiki
Saltar a: navegación, buscar

Para mantener la base de datos con un buen rendimiento siempre se deben desfragmentar índices y páginas. Las páginas fragmentadas ocupan más espacio y aumenta el tiempo de procesamiento de las consultas.

Cuando desfragmentar una base de datos?
Ejecutar DBCC SHOWCONTIG, del informe arrojado:

  • Si "Fragmentación de exploración lógica" < 10% no hacen nada
  • Si "Fragmentación de exploración lógica" > 10% y < 30% DBCC INDEXDEFRAG
  • Si "Fragmentación de exploración lógica" > 30% = también DBCC DBREINDEX


Recordar que no siempre podemos hacer el siguiente procedimiento, sólo si es necesario, debido a que el procedimiento hace a las consultas rápidas, pero una base muy desfragmentada hace lento los insert. Por lo tanto, no podemos hacer un Job que se ejecuta siempre, se debe analizar si hay necesidad de ser ejecutarlo o no.

Esto todavía no es parte del trabajo, es sólo para entender cómo funciona el algoritmo inteligente.

AoZqfNm.jpg


Crear un Job inteligente

Crea una tabla temporal, interpreta el resultado de la consulta cargada en la variable y decide si debe hacer la desfragmentación de la base, Reindex o nada.

Script 1 SQL

-- DBreindex fragmentados indexes - Script reconstrução do índice
-- SQL Server REBUILD indexes - Reconstruir todos os índices
USE HN_Ondata_BKP;
GO
--Criar tabela temporária para armazenar informações de dados de meta sobre índices
CREATE TABLE #IndexFragmentation (
  ObjectName CHAR(255),
  ObjectId INT,
  IndexName CHAR(255),
  IndexId INT,
  Lvl INT,
  CountPages INT,
  CountRows INT,
  MinRecSize INT,
  MaxRecSize INT,
  AvgRecSize INT,
  ForRecCount INT,
  Extents INT,
  ExtentSwitches INT,
  AvgFreeBytes INT, 
  AvgPageDensity INT,
  ScanDensity DECIMAL,
  BestCount INT,
  ActualCount INT,
  LogicalFrag DECIMAL,
  ExtentFrag DECIMAL)

INSERT #IndexFragmentation
EXEC( 'DBCC SHOWCONTIG WITH TABLERESULTS , ALL_INDEXES')

DELETE #IndexFragmentation
WHERE left(ObjectName,3) = 'sys'

DECLARE @Table SYSNAME,
        @DynamicSQL VARCHAR(512)

DECLARE @objectid INT,
        @indexid INT

DECLARE @Fragmentation DECIMAL,
        @MaxFragmentation DECIMAL

SET @MaxFragmentation = 35.0

DECLARE curIndexFrag CURSOR FOR
SELECT ObjectName,
       LogicalFrag = max(LogicalFrag)
FROM #IndexFragmentation
WHERE LogicalFrag >= @MaxFragmentation
  AND indexid != 0
  AND indexid != 255
GROUP BY ObjectName

OPEN curIndexFrag

FETCH NEXT FROM curIndexFrag
INTO @Table,
     @Fragmentation
WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @DynamicSQL = 'DBCC DBREINDEX (' + QUOTENAME(RTRIM(@Table)) + ', '''', 80)'

    PRINT @DynamicSQL

    EXEC( @DynamicSQL)
    
    FETCH NEXT FROM curIndexFrag
    INTO @Table,
         @Fragmentation
  END

CLOSE curIndexFrag

DEALLOCATE curIndexFrag

GO

DROP TABLE #IndexFragmentation

GO

Creación del Job

1 - Parte 1

CjxqoGJ.png

OlyQnyi.png

FOJOkRq.png

Insertamos el código del script anterior.

C74ELzG.png

BvUCARA.png

EnIzmKS.png

A continuación, haga clic en Aceptar> Aceptar> Aceptar para confirmar todas las operaciones.

Ahora, después de hacer la reconstrucción de índices y desfragmentar la base haremos una actualización de Stats para cada base. De forma predeterminada SQL 2005 ya lo hace, pero podemos forzar esta tarea todos los días.

Crear un Job como el anterior en el Agente SQL, en el paso de que debe ingresar el código haga clic en Crear y escriba el siguiente comando SQL

EXEC sp_updatestats

IEoamMU.png

A continuación, haga clic en Aceptar> Aceptar> Aceptar para confirmar todas las operaciones. Agendarlo para todos los días a las 4 a.m.

AJSJYe6.png

Crear un Job como el anterior en el Agente SQL, en el paso de que debe ingresar el código haga clic en Crear y escriba el siguiente comando SQL

--Limpa la base Backup
DBCC SHRINKDATABASE (HN_Ondata_BKP, 0) with no_infomsgs

--Limpa la base Ondata de producción
DBCC SHRINKDATABASE (HN_ondata, 0) with no_infomsgs

--Limpa la base de Administración
DBCC SHRINKDATABASE (HN_ADMIN, 0) with no_infomsgs

--Limpa la base de Reportes
DBCC SHRINKDATABASE (HN_REPORTING, 0) with no_infomsgs

--Limpa la base temporal
DBCC SHRINKDATABASE (TEMPdb, 0) with no_infomsgs

JK4JrD3.png

Programar para ejecutar cada fin de semana los domingos a las 15h.

DpU2Yo9.png

Ahora vamos al paso 4, borrar y liberar la memoria caché que el SQL dejó en la memoria, el comando DBCC Freeproccache tiene como finalidad eliminar la información referente a los planes de ejecución compilados y almacenado por el SQL Server durante la execución de sus transacciones. Esta se puede consultar con el select a continuación:

/*============Consulta Memoria Cache================*/
Select * from sys.dm_exec_cached_plans@@

rear un Job como el anterior en el Agente SQL, en el paso de que debe ingresar el código haga clic en Crear y escriba el siguiente comando SQL


@@ sql use HN_ADMIN_IMPORT
DBCC FREEPROCCACHE
go

use HN_Agenda
DBCC FREEPROCCACHE
go

use HN_ADMIN
DBCC FREEPROCCACHE
go

use HN_Ondata
DBCC FREEPROCCACHE
go

use HN_MEDIASERVER
DBCC FREEPROCCACHE
go

use HN_Ondata_BKP
DBCC FREEPROCCACHE
go

use HN_REPORTING
DBCC FREEPROCCACHE
go

use HN_SCRIPTER
DBCC FREEPROCCACHE
go

use HN_SUPERVISION
DBCC FREEPROCCACHE
go

use HN_VMC
DBCC FREEPROCCACHE


D9ssIGO.png

Programar para ejecutarse sólo los Domingos

I8KweR0.png

Mantenimiento.txt