locked
Mantenimiento de una BD

    Question

  • Hola a tod@s,

    Bueno cuento un poco por encima...
    Soy bastante novato con el SQL y tenemos una BD que desde hace 2 años que no se hace mantenimiento de dicha BD y cada vez los clientes se quejan mas de que va MUY lento el problema. He estado mirando por ahi el tema de defragmentar la BD ya que seguro q estará muy fragmentado. He encontrado un script para que me defragmenta toda la BD, que es el siguiente:

    /*Perform a 'USE <database name>' to select the database in which to run the script.*/
    -- Declare variables
    SET NOCOUNT ON;
    DECLARE @tablename varchar(255);
    DECLARE @execstr   varchar(400);
    DECLARE @objectid  int;
    DECLARE @indexid   int;
    DECLARE @frag      decimal;
    DECLARE @maxfrag   decimal;

    -- Decide on the maximum fragmentation to allow for.
    SELECT @maxfrag = 30.0;

    -- Declare a cursor.
    DECLARE tables CURSOR FOR
       SELECT TABLE_SCHEMA + '.' + TABLE_NAME
       FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_TYPE = 'BASE TABLE';

    -- Create the table.
    CREATE TABLE #fraglist (
       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);

    -- Open the cursor.
    OPEN tables;

    -- Loop through all the tables in the database.
    FETCH NEXT
       FROM tables
       INTO @tablename;

    WHILE @@FETCH_STATUS = 0
    BEGIN;
    -- Do the showcontig of all indexes of the table
       INSERT INTO #fraglist
       EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
          WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS');
       FETCH NEXT
          FROM tables
          INTO @tablename;
    END;

    -- Close and deallocate the cursor.
    CLOSE tables;
    DEALLOCATE tables;

    -- Declare the cursor for the list of indexes to be defragged.
    DECLARE indexes CURSOR FOR
       SELECT ObjectName, ObjectId, IndexId, LogicalFrag
       FROM #fraglist
       WHERE LogicalFrag >= @maxfrag
          AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;

    -- Open the cursor.
    OPEN indexes;

    -- Loop through the indexes.
    FETCH NEXT
       FROM indexes
       INTO @tablename, @objectid, @indexid, @frag;

    WHILE @@FETCH_STATUS = 0
    BEGIN;
       PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
          ' + RTRIM(@indexid) + ') - fragmentation currently '
           + RTRIM(CONVERT(varchar(15),@frag)) + '%';
       SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
           ' + RTRIM(@indexid) + ')';
       EXEC (@execstr);

       FETCH NEXT
          FROM indexes
          INTO @tablename, @objectid, @indexid, @frag;
    END;

    -- Close and deallocate the cursor.
    CLOSE indexes;
    DEALLOCATE indexes;

    -- Delete the temporary table.
    DROP TABLE #fraglist;
    GO


    Solo con pasarle esta defragmentación ya debería reparase el problema que tenemos?
    He leido también que si la tabla está encima de un % es mejor recontruir el indice?
    Hay algo mas que tanga que tener en cuanta para optimizar la BD?

    Muchas gracias de antemano.


    Un saludo.

    Thursday, December 03, 2009 10:50 AM

Answers

  • Ejecutando ese script lo que conseguirás será reorganizar todos los índices de todas las tablas de esa base de datos. Si el problema que tenéis es justamente que la fragmentación es excesiva, estarás aliviando esa situación. Sin embargo, ¿cómo sabes que es ese el problema? Tal vez el servidor tenga poca memoria, o los discos sean lentos, o de lo que se quejan los clientes es algo puntual, o... hay mucho que auditar dentro de un SQL Server como para poder ser respondido en un foro.

    Es evidente que tener un buen plan de mantenimiento es básico para el correcto funcionamiento de la base de datos. Te recomiendo eches un vistazo a un script que te genera automáticamente planes de mantenimiento (backups, chequeo de integridad y reindexación), todo mediante TSQL (http://ola.hallengren.com/). Creo que es mejor opción que el asistente de creación de planes de mantenimiento de SQL Server.

    Thursday, December 03, 2009 12:32 PM
  • Cuando al cabo de dos años los clientes se quejan de que va muy lento el programa, una causa muy común es que no existan índices óptimos para las consultas que realiza el programa. Al principio, cuando hay pocos datos, el programa va rápido aunque no tenga índices para optimizar las consultas, pero con el paso de los meses va aumentando el número de registros almacenados. Al no disponer de índices adecuados, las consultas se resuelven haciendo barridos completos de las tablas, y cuanto más grandes son las tablas más lento se vuelve el programa.

    Yo sugeriría usar el "Profiler" para capturar una traza de las consultas que envía la aplicación (usando la plantilla de Tuning), y a continuación alimentar con esa carga de trabajo al Tuning Advisor, a ver qué índices recomienda. El propio Tuning Advisor emite al final un informe que indica el porcentaje de mejora que espera conseguir. Si la cifra es razonable, merece la pena implementar las recomendaciones del Tuning Advisor.
    Thursday, December 03, 2009 2:07 PM
  • Coincido con el comentario de Carlos Sacristán en que detectar cuáles son las causas reales del bajo rendimiento percibido por los usuarios requiere un análisis de rendimiento detallado; fragmentación de índices apenas es una posible pequeña parte del problema.

    También coincido con Alberto Población en que hay mucha posibilidad de que los índices no sean los mejores y al crecer con el tiempo la cantidad de datos se empiezan a percibir el mal rendimiento de las consultas.

    Ya que no es posible hacer un análisis organizado del problema (ello requiere la presencia in situ de un experto y la revisión de muchos factores), mi recomendación es ir por posibles índices inadecuados. Recomiendo usar el Asistente de Optimización:

    Tutorial: Asistente para la optimización de motor de base de datos
    http://msdn.microsoft.com/es-es/library/ms166575.aspx


    Gustavo Larriera Sosa | gustavolarriera.public (a) gmail.com | ascii164.blogspot.com | /*Este mensaje se proporciona tal como es, sin garantías de ninguna clase.*/
    Thursday, December 03, 2009 6:15 PM
    Moderator

All replies

  • Ejecutando ese script lo que conseguirás será reorganizar todos los índices de todas las tablas de esa base de datos. Si el problema que tenéis es justamente que la fragmentación es excesiva, estarás aliviando esa situación. Sin embargo, ¿cómo sabes que es ese el problema? Tal vez el servidor tenga poca memoria, o los discos sean lentos, o de lo que se quejan los clientes es algo puntual, o... hay mucho que auditar dentro de un SQL Server como para poder ser respondido en un foro.

    Es evidente que tener un buen plan de mantenimiento es básico para el correcto funcionamiento de la base de datos. Te recomiendo eches un vistazo a un script que te genera automáticamente planes de mantenimiento (backups, chequeo de integridad y reindexación), todo mediante TSQL (http://ola.hallengren.com/). Creo que es mejor opción que el asistente de creación de planes de mantenimiento de SQL Server.

    Thursday, December 03, 2009 12:32 PM
  • Cuando al cabo de dos años los clientes se quejan de que va muy lento el programa, una causa muy común es que no existan índices óptimos para las consultas que realiza el programa. Al principio, cuando hay pocos datos, el programa va rápido aunque no tenga índices para optimizar las consultas, pero con el paso de los meses va aumentando el número de registros almacenados. Al no disponer de índices adecuados, las consultas se resuelven haciendo barridos completos de las tablas, y cuanto más grandes son las tablas más lento se vuelve el programa.

    Yo sugeriría usar el "Profiler" para capturar una traza de las consultas que envía la aplicación (usando la plantilla de Tuning), y a continuación alimentar con esa carga de trabajo al Tuning Advisor, a ver qué índices recomienda. El propio Tuning Advisor emite al final un informe que indica el porcentaje de mejora que espera conseguir. Si la cifra es razonable, merece la pena implementar las recomendaciones del Tuning Advisor.
    Thursday, December 03, 2009 2:07 PM
  • Hola de nuevo,

    Carlos Sacristan, el servidor creo que no sufre ni de procesador ni memoria ni discos (SO(Win 2003 server),(SQL 2005 Server)tiene 8 nucleos de procesador, 4 gb de memoria y los discos son SCASI en raid 1(sistema),1(para el .ldf) y 10(para el .mdf))le he echado una ojeada a la página que has posteado y he visto un script para bajar que supuestamente lo hace todo... lo he editado y tiene muchísimo código escrito... esto en realidad que hace? un mantenimiento general de la BD?

    Alberto Poblacion, no he usado nunca el Profiler, me tendré que documentar un poco sobre el programa y a ver si consigo "tunear" la BD pero por el momento voy a ver si ejecutando un par de scripts solucionamos el problema.

    También quiero reindexar todas las tablas ejecutando este código:

    USE DatabaseName –Enter the name of the database you want to reindex

    DECLARE @TableName varchar(255)

    DECLARE TableCursor CURSOR FOR
    SELECT table_name FROM information_schema.tables
    WHERE table_type = 'base table'

    OPEN TableCursor

    FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    DBCC DBREINDEX(@TableName,' ',90)
    FETCH NEXT FROM TableCursor INTO @TableName
    END

    CLOSE TableCursor

    DEALLOCATE TableCursor


    Y luego tendré que reducir el LOG ya que no para de crecer y estoy viendo por aqui que puedes reducirlo(con el SHRINK Data base), luego hacer Copia de seguridad Completa, y a cada hora hacer una Transaccional truncando, después una diferencial y volver a hacer Transaccionales hasta que llege la hora de hacer la Completa que se hace cada día.

    Que os parece?

    Os pido consejo porque estoy bastante verde en esto y seguro que habrán mejores opciones que lo que he descrito.

    Otra pregunta, ¿si ejecuto lo del defrag, reindexar y el Shrink, los usuarios pueden continuar trabajando en la BD? o sería mejor hacerlo un día que no estén trabajando en la BD?.

    Gracias de antemano.


    Un saludo.

    Thursday, December 03, 2009 5:56 PM
  • Coincido con el comentario de Carlos Sacristán en que detectar cuáles son las causas reales del bajo rendimiento percibido por los usuarios requiere un análisis de rendimiento detallado; fragmentación de índices apenas es una posible pequeña parte del problema.

    También coincido con Alberto Población en que hay mucha posibilidad de que los índices no sean los mejores y al crecer con el tiempo la cantidad de datos se empiezan a percibir el mal rendimiento de las consultas.

    Ya que no es posible hacer un análisis organizado del problema (ello requiere la presencia in situ de un experto y la revisión de muchos factores), mi recomendación es ir por posibles índices inadecuados. Recomiendo usar el Asistente de Optimización:

    Tutorial: Asistente para la optimización de motor de base de datos
    http://msdn.microsoft.com/es-es/library/ms166575.aspx


    Gustavo Larriera Sosa | gustavolarriera.public (a) gmail.com | ascii164.blogspot.com | /*Este mensaje se proporciona tal como es, sin garantías de ninguna clase.*/
    Thursday, December 03, 2009 6:15 PM
    Moderator
  • [...] tendré que reducir el LOG ya que no para de crecer [...]

    ¿El LOG no para de crecer? ¿No será que la base de datos está configurada con el modelo de recuperación completa ("Full"), y no se están haciendo backups del Log? Si no estás haciendo Backups del Log, cambia el modelo de recuperación a "Simple". De lo contrario el Log no parará de crecer aunque le hagas un Shrink.
    Thursday, December 03, 2009 9:29 PM
  • Hola Alberto.

    En realidad tenemos dos BD. En la BD1 he borrado el .ldf y lo he vuelto a crear ya q tenía 108 GB de log del cual no me servía para nada, ahora se ha quedado a 1MB y ya esta creciendo. He planificado copias de seguridad completa, transaccional y diferencial, le he dicho que vaya machacando para que no crezca.
    Tambien le he pasado el shrink a la otra BD2 y el .mdf que me ocupaba 28 gb a pasado a 12 gb, sinembargo el .ldf me ha pasado de 38 gb a 52... pero bueno creo que a esta también le voy a borrar el LOG y hacer lo mismo que en la otra BD para que no crezca.
    En la BD1 no me deja ejecutarle el defrag que he posteado anteriormente, no me acuerdo lo que me deicía pero a los 8 min en ejecución me decía que no se habian podido desfragmentar 2 tablas, no se si decía que estaban protegidas o otra cosa. Hay alguna forma de desprotegerla para desfragmentarla? es recomendeble hacerlo?
    La BD2 me ha dejado defragmentarla sin problemas.

    Gracias de antemano.


    Un saludo.
    Saturday, December 05, 2009 8:44 AM
  • [...]el .ldf tenía 108 GB [...] ya esta creciendo. He planificado copias de seguridad completa, transaccional y diferencial

       108 GB de log es una pasada. Significa que haces 108 GB de transacciones entre copia y copia.
       Es casi seguro que te está ocurriendo algo de lo que yo te decía: tienes el modelo de recuperación en "Full" y por algún motivo las copias del log de transacciones no las estás haciendo con suficiente frecuencia para que te lo vayan vaciando. Ten presente que el archivo no encoge físicamente cuando haces la copia, es decir, que si el log crece hasta cierto tamaño y luego lo vacías con el "Backup Log ...", vuelve a quedar disponible en el archivo el espacio para las nuevas transacciones (con lo que la siguiente vez que haces alguna transacción no vuelve a crecer), pero el fichero se queda ya con el tamaño máximo que alcanzó. Si la BD tiene muchos movimientos, la copia del Log se debe hacer con bastante frecuencia (por ejemplo, cada 15 minutos), tanto por razones de seguridad (que no pierdas más de 15 minutos de trabajo si hay que recurrir a restaurar el backup), como para que no crezca enormemente el archivo .ldf.

    Saturday, December 05, 2009 10:26 AM
  • Estás en lo cierto Alberto, tengo la recuperación en "full" y no tenía programado las copias transacionales, de ahi que el log no paraba de crecer desde hace 2 años, ahora he borrado el log y le he puesto 4 transacionales y la copia completa, ahora en teoría no debe de crecer mucho... Gracias ;)

    Saturday, December 05, 2009 11:07 AM
  • Hola de nuevo,

    Carlos Sacristan, el servidor creo que no sufre ni de procesador ni memoria ni discos (SO(Win 2003 server),(SQL 2005 Server)tiene 8 nucleos de procesador, 4 gb de memoria y los discos son SCASI en raid 1(sistema),1(para el .ldf) y 10(para el .mdf))le he echado una ojeada a la página que has posteado y he visto un script para bajar que supuestamente lo hace todo... lo he editado y tiene muchísimo código escrito... esto en realidad que hace? un mantenimiento general de la BD?

    Eso de que "creo que no sufre ni de procesador ni memoria ni discos"... ¿lo has contrastado con valores del monitor de rendimiento?. El hecho de que aportes esos datos (núcleos, memoria, discos) no aporta mucho. Deberías asegurarte que tu hw es capaz de soportar la carga del servidor.

    Por otro lado, la solución que te comentaba tiene "mucho código" porque es una solución muy trabajada. El "mantenimiento general" que hace es el que está documentado en la propia página del creador, pero básicamente consiste en chequeo de integridad, backups y desfragmentación.

    Echa un vistazo a la documentación, no es muy largo de leer y te quitará muchos quebraderos de cabeza (como lo del log que comentas).

    Por cierto, además de la mala práctica de eliminar el log (algo que ya te han comentado el resto de los compañeros), también lo es el reducir el tamaño de los archivos (de hecho, en versiones posteriores, la propiedad AUTOSHRINK de la base de datos se va a eliminar justamente por esto), ya que genera muchísima fragmentación en el archivo (o la base de datos) sobre el que ejecutes la instrucción.
    Monday, December 07, 2009 9:23 AM
  • Hola Carlos,

    Lo del rendimiento del servidor, solo he visto que de CPU esta casi todo el rato entre el 2 y el 5% teniendo algun pico muy de vez en cuando, de la memoria esta utilizando siempre 2,3 GB el disco duro no lo he testeado, faltaría probarlo.
    De momento estoy haciendo todas estas pruebas en un servidor aparte que he montado pero la BD es la .bak de del Servidor original.
    Os cuento lo que le he hecho al final con servidor de pruebas.

    1- Desfragmentar la BD con el codigo que he posteado
    2- Me he ido a Task-->Shrink-->Data Base (para la reducción del tamaño de la BD)

    3-   Ejecutar script (para borrar el log y reducir su tamaño)
          DBCC OPENTRAN (BD)

          Backup log BD WITH TRUNCATE_ONLY

          DBCC SHRINK DATABASE (BD, TRUNCATEONLY)
    4- Backup completa de la BD.
    5- Programación de las compias Transaccionales y Diferenciales.


     Todo esto se ha completado exitosamente y al final se ha reducido la BD y se ha desfragmentado.
     He probado a ejecutar el script que me linqueas Carlos, lo testea y lo pasa correctamente, modifico donde tiene que hacer el backup y lo ejecuto, pero me saca dos errores en las lines 400 y pico creo. Puede ser porque tenga el Service Pack 1?
     He visto que ya está el Service Pack 3, en principio no pasará nada si lo actualizo verdad? o tengo que tener cosas en cuanta antes de actualizarlo?. 
    Gracias.


     Un saludo.

    Monday, December 07, 2009 3:58 PM
  • Hola Carlos,

    Lo del rendimiento del servidor, solo he visto que de CPU esta casi todo el rato entre el 2 y el 5% teniendo algun pico muy de vez en cuando, de la memoria esta utilizando siempre 2,3 GB el disco duro no lo he testeado, faltaría probarlo.
    De momento estoy haciendo todas estas pruebas en un servidor aparte que he montado pero la BD es la .bak de del Servidor original.
    Os cuento lo que le he hecho al final con servidor de pruebas.

    1- Desfragmentar la BD con el codigo que he posteado
    2- Me he ido a Task-->Shrink-->Data Base (para la reducción del tamaño de la BD)

    3-   Ejecutar script (para borrar el log y reducir su tamaño)
          DBCC OPENTRAN (BD)

          Backup log BD WITH TRUNCATE_ONLY

          DBCC SHRINK DATABASE (BD, TRUNCATEONLY)
    4- Backup completa de la BD.
    5- Programación de las compias Transaccionales y Diferenciales.


     Todo esto se ha completado exitosamente y al final se ha reducido la BD y se ha desfragmentado.
     He probado a ejecutar el script que me linqueas Carlos, lo testea y lo pasa correctamente, modifico donde tiene que hacer el backup y lo ejecuto, pero me saca dos errores en las lines 400 y pico creo. Puede ser porque tenga el Service Pack 1?
     He visto que ya está el Service Pack 3, en principio no pasará nada si lo actualizo verdad? o tengo que tener cosas en cuanta antes de actualizarlo?. 
    Gracias.


     Un saludo.


    SQL Server no es un gran consumidor de CPU, así que ese comportamiento es normal. En cambio, cuanta más memoria le pongas, más usará. 4Gb de memoria se me antoja escasa para un servidor en producción. No dices si es x64 o x86 (y en este último caso, si tienes activado /3GB). En cualquier caso, lo más problemático en un servidor de base de datos es el acceso a disco (ya que suele ser el recurso más lento de todos): comprueba que su rendimiento efectivamente es correcto.

    Por otra parte, te recomiendo que NO reduzcas el tamaño de la base de datos. Es muy mala práctica, y, a menos que efectivamente tengas problemas de espacio, no lo ejecutes nunca.

    El log de transacciones tampoco se mantiene ejecutando las instrucciones que has ejecutado. Si no lo necesitas, pon la base de datos en modo de recuperación SIMPLE y ya te puedes olvidar de él. Si la tienes en modo FULL o BULK-LOGGED, entonces ejecuta periódicamente backups del log.

    El script que te comenté funciona correctamente. Si al ejecutarlo lanza algún error, postéalo aquí y tal vez te podamos ayudar. Si sólo dices que han ocurrido "errores" en la línea "400 y pico" pues poco te vamos a poder decir. En cuanto al Service Pack, antes la solución obligaba a tener instalada al menos el SP1, pero ahora ya no es un requisito indispensable. En cualquier caso no pasa nada si lo instalas; de hecho deberías haberlo hecho, pero no ya por el script, sino por la propia salud del servidor.
    Monday, December 07, 2009 4:38 PM
  • Hola de nuevo Carlos,

    En tema del servidor es un x86. Tengo pendiente aun lo del test del disco, ya lo haré cuando tenga un rato y lo postearé.

    La verdad esque me da un poco de miedo al decirme de no reducir la BD, de momento no tengo problemas de espacio es mas que nada para optimizar la BD y que la .bak no sea muy pesada. La he reducido y entro al programa que utiliza el SQL y no tengo problemas en el PC de pruebas.

    En cuanto al script q me mandas, te pongo aqui mensaje que me sale al ejecutarlo:

    Msg 50000, Level 16, State 1, Line 45
    The database that you are creating the objects in has to be in compatibility_level 90 or 100.
    Msg 468, Level 16, State 9, Procedure DatabaseBackup, Line 146
    Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 102, Level 15, State 1, Procedure IndexOptimize, Line 456
    Incorrect syntax near '('.

    Gracias por las ayudas.

    Un saludo
    Monday, December 07, 2009 5:20 PM
  • Hola de nuevo Carlos,

    En tema del servidor es un x86. Tengo pendiente aun lo del test del disco, ya lo haré cuando tenga un rato y lo postearé.

    La verdad esque me da un poco de miedo al decirme de no reducir la BD, de momento no tengo problemas de espacio es mas que nada para optimizar la BD y que la .bak no sea muy pesada. La he reducido y entro al programa que utiliza el SQL y no tengo problemas en el PC de pruebas.

    En cuanto al script q me mandas, te pongo aqui mensaje que me sale al ejecutarlo:

    Msg 50000, Level 16, State 1, Line 45
    The database that you are creating the objects in has to be in compatibility_level 90 or 100.
    Msg 468, Level 16, State 9, Procedure DatabaseBackup, Line 146
    Cannot resolve the collation conflict between "Modern_Spanish_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
    Msg 102, Level 15, State 1, Procedure IndexOptimize, Line 456
    Incorrect syntax near '('.

    Gracias por las ayudas.

    Un saludo

    Aunque no tienes mucha memoria en el servidor (te recomiendo comprar un poco más, seguro que la máquina lo agradece), es recomendable habilitar una serie de opciones para que SQL Server pueda adquirir un poco más de memoria. Echa un vistazo a http://support.microsoft.com/kb/291988 y http://support.microsoft.com/kb/274750.

    Si quieres optimizar la base de datos, reduciendo su tamaño lo único que consigues es fragmentación del(los) fichero(s) de datos, lo cual no es muy bueno desde el punto de vista de rendimiento. Si el backup resultante es muy grande y no tienes SQL 2008 EE (en el cual tienes la opción de compresión de backups disponible), siempre puedes usar algún compresor (tipo Winzip, Winrar, 7zip o similar) para reducirlo.

    Y en cuanto al error que te está lanzando, el mensaje es bastante claro: la base de datos donde crear los objetos que el script crea tiene que estar en el nivel de compatibilidad 90 (SQL Server 2005) ó 100 (SQL Server 2008). El script lo puedes ejecutar directamente en master, con lo que te tiene que ese error ya no te aparecerá.
    Monday, December 07, 2009 5:35 PM