Aspectos esenciales de SQL Server 2012, parte 1

Guillermo Taylor F.Consultor Senior TI & Especialista SQL Serverguillermotaylor@hotmail.com http://mastergt68.wordpress.com

Hoy hablaremos de…

Archivos, grupos de archivos y discos.Respaldos & Estrategia de respaldos.Modelo de recuperación.Mantenimiento de base de datos.

En esencia…

Los discos almacenan archivos…Los archivos almacenan datos…Los grupos de archivos están compuestos por archivos…

Estructura de BD – SQL Server 2012

Database

Data File.MDF (1)

.NDF (0-n)

Log File.LDF (1-n)

Latin1_General_CI_AS_KS_WS

Tipos de bases de datos

Bases de datos de sistema:Master – Configuración de SQL ServerMSDB – Automatización de SQL ServerTEMPDB – Área temporalModel – Plantilla para nuevas BD

Bases de datos de usuario.Otras de tipo no-de-sistema

Sobre la TEMPDB

Archivos de datos de Tempdb = Número de Núcleos de ProcesadorConfigurar el tamaño de la base de datos Tempdb

Al menos 10% del tamaño total de las bases de datos, OEl tamaño de la tabla más grande; cualquiera que sea el mayor número

Parámetros de configuración de TempdbIncrementar el tamaño inicialIncrementar el parámetro de “Autogrowth” (Usar MB en lugar de %)Usar el “Simple Recovery Model”Ubicarla físicamente en un sitio diferente a donde están las bases de datos de contenido

Separación de archivosPráctica recomendada

5 unidades de almacenamientoBases de datos del sistemaArchivos MDF/NDF de bases de datos de usuarioArchivos LDF de bases de datos de usuarioTEMPDBBACKUPS

Nota mental: Recordar sacarlos del servidor y probarlos

LDF: La más rápida posibleTEMPDB: Al menos, más rápida que la de MDFMDF: Que ofrezca redundanciaAmbientes de tipo Producción o Pruebas tipo QA

Sobre grupos de archivos

Estructura lógica para agrupar objetos en una base de datos.Todas las bases de datos tienen un grupo de archivo: el primario.

Éste contiene el archivo .MDF y .NDFs…En el grupo de archivo secundario podemos tener datos y objetos SQL Server.

Estos contienen archivos .NDF unicamente…Permite manejar los archivos contenidos en el grupo como uno solo.Ayuda en desempeño y en recuperación.

RepresentaciónBase de datos

Filegroup 1 (PRIMARIO)

Filegroup 2 (SECUNDARIO)

Archivo A Archivo B

Archivo C Archivo D

Tabla 1

Tabla 2

Tabla 3

Tabla 4

Tabla 5

Tabla 6

Revisión de archivos, grupos de archivos y discos

demo

Respaldos

Mecanismo de protección de datos.Tres diferentes tipos de respaldos

Respaldo completo.Respaldo diferencial.Respaldo de log de transacción.

Opciones avanzadas de respaldo.Respaldos de grupos de archivos.

Guardar respaldos en otros servidores.Guardar respaldo en Windows Azure.

Respaldo completo

Genera una copia completa de la base de datos.El respaldo es una representación completa del estado de la base de datos de ese momento.Debería hacerse diario o semanal.

Respaldo diferencial

Genera copia diferencial de la base de datos.Desde el último, respaldo lo que sea diferente en la base de datos.Debería hacerse diario y responder a una estrategia de recuperación definida.

Importancia del respaldo

¡SIEMPRE hay que hacer respaldo!Primer nivel de estrategia de recuperación ante situación de desastre.Copia local para recuperación rápida

Mismo cuarto, mismo piso, mismo edificio, mismo ciudad…Ustedes definen

Copia remota para temas tipo desastreMismo país, mismo departamento…

¡Es importante tener definida una estrategia!

Respaldo de base de datos

demo

Modelos de recuperación

Relacionado con respaldo y restauración.Diseñados para controlar el mantenimiento del log de transacciones de la base de datos.En esencia, es una propiedad de base de datos que controla como se registran las transacciones.

Indica si el log de transacción requiere y permite respaldo.Indica que tipo de operaciones de restauración están disponibles.

Tipos de modelos de recuperación

Full – Menor exposición a pérdida de datos.Simple – Exposición a pérdida de datos desde el último respaldo generado.Bulk-Logged – Exposición a pérdida de datos si el archivo de log se ha dañado.Detalles y explicación en http://msdn.microsoft.com/en-us/library/ms189275.aspx

Modelo de recuperación Full

.LDF

Data

Data

.MDFAgregarConteni

do

Base de datos de contenido en Disco Duro

Checkpoint

Datos

Datos

Full Recovery Model (Recomendado)

Modelo de recuperación Simple

.MDF .LDFAgregar

Contenido

Base de datos de contenido en Disco Duro

Checkpoint

Datos

Datos

Simple Recovery Model

Modelo de recuperación

demo

Mantenimiento de base de datos

Actividades para que la base de datos esté y responda “como nueva”.Regeneración y desfragmentación de índices.Actualización de estadísticas.

Evitando crecimientos enormes en Transaction Logs

.mdf .ldf

Sunday Monday Tuesday Wednesday

Full Backup Differential Differential Differential

Backup Log DB_Name to D:\SQLBackups\TempBackup.Bak

.ldf.mdf .ldf.mdf .ldf

.mdf .ldf

.mdf

Backup Log DB_Name to D:\SQLBackups\Weekly_T_Log.Bak with INIT

Lose .mdf file of database on Thursday at 4:00pm

(2) (3)

(1)(4)

Índices en SQL Server

SQL Server Index Design Guide: http://technet.microsoft.com/en-us/library/jj835095.aspx

¿Por qué hay fragmentación?

El motor mantiene automáticamente los índices cada vez que ocurre INS, UPD o DEL.En el tiempo, esto puede ocasionar que la información en el índice se fragmente.La fragmentación se da cuando en las páginas de los índices el ordenamiento lógico basado en el valor de la clave, no concuerda con el ordenamiento físico dentro del archivo de datos.Puede causar degradación del desempeño del query; respuestas lentas.

Y, ¿cómo se corrige?

Reorganizando el índice…Regenerando el índice…Primero, detectar fragmentación…Si el valor de “avg_fragmentation_in_percent” de la DMV “sys.dm_db_index_physical_stats” es:

> 5% and < = 30% entonces ALTER INDEX REORGANIZE> 30% entonces ALTER INDEX REBUILD WITH (ONLINE = ON)*

Aspectos a tener en cuenta

REGENERACIÓNPuede ser online u offline, dependiendo de la versión, edición y tipos de datos de las columnas.Crea el nuevo índice y luego elimina el índice viejo.Actualiza las estadísticas en el índice.

DESFRAGMENTACIÓNSiempre es online.Mueve los datos en las páginas en un esquema registro por registro.Únicamente mueve aquellos registros que requieren ser movidos.

Estadísticas… Y, ¿qué es eso?

Metadatos dinámicos.Contienen información estadística sobre la distribución de valores.En uno o más columnas de una tabla.En una o más columnas de una vista indexada.Describen valores de llaves en índices.Se mantienen para columnas de índices.Se usan por SQL Server para decidir los índices más apropiados a usar cuando se ejecutan los queries.

Importancia de las estadísticas

El “query optimizer” usa las estadísticas para crear planes de ejecución de consultas que mejoran el desempeño de las mismas.Para la mayoría de las consultas el “query optimizer” genera las estadísticas para un plan de calidad para ejecución consultas.En algunos casos, muy pocos, es necesario crear estadísticas adicionales o modificar el diseño de la consulta para mejores resultados.

Consideraciones…

Por defecto, SQL Server mantiene estadísticas para las columnas de uno o de los índices de una tabla.Se pueden mantener estadísticas en columnas que no hacen parte de un índice, si se desea.Lo anterior genera un impacto sobre el límite de los índices “non-clustered” que se pueden tener en una tabla.

Dicho límite es de 249.

Es decir, las estadísticas…

Son cómo SQL Server define cómo accede a los datos que almacena.Se usan para crear un plan de ejecución.Contienen un muestreo de los valores dentro de una tabla o de un índice.

De hecho, pueden contener hasta 200 muestras de los valores dentro de una table.

Rastrean el número de valores entre los valores de los cuales se tienen muestras.

Concepto clave: Cardinalidad

Número de registros en el conjunto resultado de la consulta.El optimizador usa las estadísticas para estimar la cardinalidad de la consulta.Los estimados de la cardinalidad habilitan al optimizador a crear un plan de alta calidad para el plan.Ejemplo:

Uso de “Index Seek” en lugar de “Index Scan” para que una consulta tenga mejor desempeño.

Más adelante se revisa este concepto con otros aspectos…

Índices y estadísticas

demo

Conclusiones

Tener en cuenta conceptos y esquemas de organización de archivos y grupos de archivos para tener la BD bien organizada.Es importante respaldar y probar el respaldo, pero es más importante definir una estrategia de respaldo.Importancia del modelo de recuperación en el mantenimiento de base de datos.Mantenimiento para desempeño de queries y gestión de la base de datos.

Aspectos esenciales de SQL Server 2012, parte 1

Guillermo Taylor F.Consultor Senior TI & Especialista SQL Serverguillermotaylor@hotmail.com http://mastergt68.wordpress.com