Upload
guillermo-taylor
View
629
Download
5
Embed Size (px)
Citation preview
Aspectos esenciales de SQL Server 2012, parte 1
Guillermo Taylor F.Consultor Senior TI & Especialista SQL [email protected] 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 [email protected] http://mastergt68.wordpress.com