Bueno, hace ya bastante desde la última vez que escribí. Y como vengo con las manos vacías, he decidido recuperar una pequeña guía que escribí (para mi mismo) sobre el mantenimiento de una base de datos en SQL Server 2005.
En realidad, se trata de un resumen de un vídeo que adjunto en la fuente del final del post. Ahí va ese copy&paste:
Planes de mantenimiento
SQL Server 2005 incorpora la opción de “planes de mantenimiento”, que permiten mediante un asistente realizar tareas de mantenimiento de forma automatizada. Sin embargo, no siempre nos interesará realizar estas tareas de forma automatizada, y probablemente habrá ocasiones en que será necesario o preferible realizarlas de forma manual.
Esta guía pretende dar algunas pinceladas sobre conceptos de mantenimiento de bases de datos en SQL Server.
Hardware
Lo primero que hay que tener en cuenta, es que el mantenimiento de una base de datos también dependerá del hardware de la máquina sobre la que se aloje el SQL Server, en parte debido al elevado consumo de memoria requerido para realizar este tipo de tareas, pero sobretodo, especialmente al realizar tareas de:
- Backups
- Reorganización/recreación de índices
Hay que tener en cuenta si tenemos suficiente hardware como para poder realizar este tipo de tareas, y en cualquier caso, planificarlas para cuando el servidor esté en sus mínimos niveles de utilización.
Verificando las bases de datos con DBCC
SQL Server viene con varias sentencias DBCC de verificación de bases de datos, de entre las cuales destaca:
- DBCC CHECKDB: esta sentencia muestra cuántas filas y páginas tiene cada tabla de una determinada base de datos, indicando errores de asignación y consistencia, sin bloquear. Realmente, esta sentencia ejecuta lo que en SQL Server 2000 sería:
- CHECK ALLOC
- CHECK CATALOG
- CHECK TABLE
Con DBCC CHECKDB se pueden verificar cualquiera de las bases de datos de sistema (Máster + recursos, Model, MSDB y Tempdb, aunque esta última con bloqueo a nivel de tablas), así como las de usuario.
Fragmentación
Uno de los principales problemas de rendimiento viene dado por la fragmentación de los datos de la base de datos. Para evitarla, en medida de lo posible, será conveniente:
- Crear de inicio del archivo de la base de datos lo suficientemente grande como para satisfacer las necesidades de espacio futuras.
- No permitir el “autogrow” del fichero.
- En muy pocas ocasiones se debería hacer uso de la opción “Shrink”, ya que se trata de una operación muy costosa que provoca una gran fragmentación tanto de datos como de índices. Se recomienda usar, en estos casos:
- DBCC SHRINKDATABASE, cuya única limitación es que nunca podremos reducir la base de datos más allá de su tamaño de creación.
NOTA: a esta función se le puede pasar un parámetro numérico que indica el porcentaje de espacio disponible que se desea dejar en el archivo de la base de datos después de reducir la base de datos.
Finalmente, no hay que olvidar los problemas relacionados con:
- Fragmentación lógica, referente al orden de las páginas de una tabla.
- Fill Factor, o factor de llenado de las páginas (parámetro modificable).
Trabajo con índices
En cuanto a los índices, existen dos tipos de operaciones posibles:
- ALTER INDEX REBUILD: operación online que actualiza estadísticas, pero que provoca una gran transacción (deberemos asegurar suficiente espacio en el log de transacciones, ya que además no lo podremos limpiar hasta que la operación no finalice) y que puede llegar a consumir una gran cantidad de recursos.
- Recomendable únicamente cuando el porcentaje de fragmentación de los índices supera el 30%.
- ALTER INDEX REORGANIZE: operación que en este caso no actualiza estadísticas (aunque normalmente no necesitaremos actualizarlas en ningún caso, ya que siempre viene habilitada la opción AUTO-UPDATE STATISTICS con las instalaciones de SQL Server).
- Uso para índices poco fragmentados (porcentaje menor al 30%).
Únicamente deberemos realizar este tipo de trabajo cuando sea realmente necesario, con lo cual será conveniente comprobar el nivel de fragmentación de los índices antes de ejecutar cualquiera de las dos operaciones anteriores.
Backups
Será recomendable tener una buena política de backups, aunque de igual forma, será aún más recomendable realizar comprobaciones de la validez de los mismos:
- RESTORE VERIFYONLY nos permitirá realizar una verificación de un archivo de backup, asegurando su funcionamiento ante un posible restore.
Hay que tener en cuenta que es una buena práctica ir eliminando los archivos de backup obsoletos, tanto del disco como del registro generado en el MSDB.
Otras consideraciones
Finalmente, únicamente indicar que el orden en el que se ejecutan las acciones de mantenimiento puede influir y mucho en el resultado de nuestro plan. De esta manera por ejemplo, no tendrá sentido realizar una reindexación de índices y a continuación un shrink, debido a que ésta última operación volverá a fragmentar los índices de la tabla.
Igualmente, se deberá tener en cuenta la existencia de operaciones incompatibles para realizarse de forma paralela, como por ejemplo una reindexación de índices y un CHECKDB.
Fuente
Mantenimiento de bases de datos en SQL Server 2005
http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=es-CO&EventID=1032346791&CountryCode=CO