MS SQL Server - Arquitectura

Hemos clasificado la arquitectura de SQL Server en las siguientes partes para facilitar su comprensión:

  • Arquitectura general
  • Arquitectura de memoria
  • Arquitectura de archivos de datos
  • Arquitectura del archivo de registro

Arquitectura general

Client - Dónde se inició la solicitud.

Query - Consulta SQL que es lenguaje de alto nivel.

Logical Units - Palabras clave, expresiones y operadores, etc.

N/W Packets - Código relacionado con la red.

Protocols - En SQL Server tenemos 4 protocolos.

  • Memoria compartida (para conexiones locales y resolución de problemas).

  • Tuberías con nombre (para conexiones que están en conectividad LAN).

  • TCP / IP (para conexiones que están en conectividad WAN).

  • Adaptador de interfaz virtual VIA (requiere hardware especial para ser configurado por el proveedor y también está en desuso de la versión SQL 2012).

Server - Dónde se instalaron los servicios SQL y residen las bases de datos.

Relational Engine- Aquí es donde se realizará la ejecución real. Contiene analizador de consultas, optimizador de consultas y ejecutor de consultas.

Query Parser (Command Parser) and Compiler (Translator) - Esto verificará la sintaxis de la consulta y la convertirá a lenguaje de máquina.

Query Optimizer - Preparará el plan de ejecución como salida tomando como entrada la consulta, las estadísticas y el árbol de Algebrizer.

Execution Plan - Es como una hoja de ruta, que contiene el orden de todos los pasos a realizar como parte de la ejecución de la consulta.

Query Executor - Aquí es donde se ejecutará la consulta paso a paso con la ayuda del plan de ejecución y también se contactará con el motor de almacenamiento.

Storage Engine - Es responsable del almacenamiento y recuperación de datos en el sistema de almacenamiento (disco, SAN, etc.), manipulación de datos, bloqueo y gestión de transacciones.

SQL OS- Se encuentra entre la máquina host (sistema operativo Windows) y SQL Server. Todas las actividades realizadas en el motor de la base de datos están a cargo de SQL OS. SQL OS proporciona varios servicios del sistema operativo, como la gestión de memoria se ocupa de la agrupación de búfer, el búfer de registro y la detección de interbloqueo utilizando la estructura de bloqueo y bloqueo.

Checkpoint Process- Checkpoint es un proceso interno que escribe todas las páginas sucias (páginas modificadas) desde la memoria caché del búfer en el disco físico. Aparte de esto, también escribe los registros de registro desde el búfer de registro al archivo físico. La escritura de páginas sucias desde la memoria caché del búfer al archivo de datos también se conoce como Endurecimiento de páginas sucias.

Es un proceso dedicado y se ejecuta automáticamente por SQL Server a intervalos específicos. SQL Server ejecuta el proceso de punto de control para cada base de datos individualmente. Checkpoint ayuda a reducir el tiempo de recuperación de SQL Server en caso de un apagado inesperado o una falla o falla del sistema.

Puntos de control en SQL Server

En SQL Server 2012 hay cuatro tipos de checkpoints -

  • Automatic - Este es el punto de control más común que se ejecuta como un proceso en segundo plano para asegurarse de que la base de datos de SQL Server se pueda recuperar en el límite de tiempo definido por el intervalo de recuperación - Opción de configuración del servidor.

  • Indirect- Esto es nuevo en SQL Server 2012. Esto también se ejecuta en segundo plano pero para cumplir con un tiempo de recuperación objetivo especificado por el usuario para la base de datos específica donde se ha configurado la opción. Una vez que se ha seleccionado Target_Recovery_Time para una base de datos determinada, esto anulará el intervalo de recuperación especificado para el servidor y evitará el punto de control automático en dicha base de datos.

  • Manual- Esta se ejecuta como cualquier otra instrucción T-SQL, una vez que emita el comando de punto de control, se ejecutará hasta su finalización. El punto de control manual se ejecuta solo para su base de datos actual. También puede especificar Checkpoint_Duration, que es opcional; esta duración especifica el tiempo en el que desea que se complete su punto de control.

  • Internal- Como usuario, no puede controlar el control interno. Emitidos en operaciones específicas como

    • El apagado inicia una operación de punto de control en todas las bases de datos, excepto cuando el apagado no está limpio (apagado con nowait).

    • Si el modelo de recuperación cambia de Completo \ Registro masivo a Simple.

    • Mientras realiza una copia de seguridad de la base de datos.

    • Si su base de datos está en un modelo de recuperación simple, el proceso de punto de control se ejecuta automáticamente cuando el registro se llena al 70% o según la opción del servidor: intervalo de recuperación.

    • El comando Alterar la base de datos para agregar o quitar un archivo de registro de datos también inicia un punto de control.

    • El punto de control también tiene lugar cuando el modelo de recuperación de la base de datos se registra de forma masiva y se realiza una operación mínimamente registrada.

    • Creación de instantáneas de base de datos.

  • Lazy Writer Process- El escritor diferido enviará páginas sucias al disco por una razón completamente diferente, porque necesita liberar memoria en el grupo de búfer. Esto sucede cuando el servidor SQL se encuentra bajo presión de memoria. Hasta donde yo sé, esto está controlado por un proceso interno y no hay configuración para ello.

El servidor SQL monitorea constantemente el uso de la memoria para evaluar la contención (o disponibilidad) de los recursos; su trabajo es asegurarse de que haya una cierta cantidad de espacio libre disponible en todo momento. Como parte de este proceso, cuando se da cuenta de cualquier conflicto de recursos, hace que Lazy Writer libere algunas páginas de la memoria escribiendo páginas sucias en el disco. Emplea el algoritmo de uso menos reciente (LRU) para decidir qué páginas se van a descargar en el disco.

Si Lazy Writer está siempre activo, podría indicar un cuello de botella en la memoria.

Arquitectura de memoria

A continuación se presentan algunas de las características más destacadas de la arquitectura de memoria.

  • Uno de los principales objetivos de diseño de todo el software de base de datos es minimizar la E / S de disco porque las lecturas y escrituras de disco se encuentran entre las operaciones que consumen más recursos.

  • La memoria en Windows se puede llamar con el espacio de direcciones virtual, compartido por el modo Kernel (modo OS) y el modo Usuario (aplicación como SQL Server).

  • El "Espacio de direcciones de usuario" de SQL Server se divide en dos regiones: MemToLeave y Buffer Pool.

  • El tamaño de MemToLeave (MTL) y del grupo de búfer (BPool) lo determina SQL Server durante el inicio.

  • Buffer managementes un componente clave para lograr una E / S de alta eficiencia. El componente de gestión del búfer consta de dos mecanismos: el administrador del búfer para acceder y actualizar las páginas de la base de datos y el grupo de búfer para reducir la E / S de los archivos de la base de datos.

  • El grupo de amortiguadores se divide a su vez en varias secciones. Los más importantes son la caché de búfer (también denominada caché de datos) y la caché de procedimientos.Buffer cachemantiene las páginas de datos en la memoria para que los datos a los que se accede con frecuencia se puedan recuperar del caché. La alternativa sería leer las páginas de datos del disco. La lectura de páginas de datos de la memoria caché optimiza el rendimiento al minimizar el número de operaciones de E / S necesarias que son inherentemente más lentas que recuperar datos de la memoria.

  • Procedure cachemantiene los planes de ejecución de consultas y procedimientos almacenados para minimizar el número de veces que deben generarse planes de consulta. Puede encontrar información sobre el tamaño y la actividad dentro de la caché de procedimientos utilizando la instrucción DBCC PROCCACHE.

Otras partes del grupo de amortiguadores incluyen:

  • System level data structures - Contiene datos de nivel de instancia de SQL Server sobre bases de datos y bloqueos.

  • Log cache - Reservado para leer y escribir páginas de registro de transacciones.

  • Connection context- Cada conexión a la instancia tiene un área pequeña de memoria para registrar el estado actual de la conexión. Esta información incluye procedimientos almacenados y parámetros de función definidos por el usuario, posiciones del cursor y más.

  • Stack space - Windows asigna espacio de pila para cada hilo iniciado por SQL Server.

Arquitectura de archivos de datos

La arquitectura del archivo de datos tiene los siguientes componentes:

Grupos de archivos

Los archivos de la base de datos se pueden agrupar en grupos de archivos con fines de asignación y administración. Ningún archivo puede ser miembro de más de un grupo de archivos. Los archivos de registro nunca forman parte de un grupo de archivos. El espacio de registro se administra por separado del espacio de datos.

Hay dos tipos de grupos de archivos en SQL Server, primario y definido por el usuario. El grupo de archivos principal contiene el archivo de datos principal y cualquier otro archivo no asignado específicamente a otro grupo de archivos. Todas las páginas de las tablas del sistema se asignan en el grupo de archivos principal. Los grupos de archivos definidos por el usuario son cualquier grupo de archivos especificado mediante la palabra clave del grupo de archivos en la declaración de creación de base de datos o modificación de base de datos.

Un grupo de archivos en cada base de datos funciona como grupo de archivos predeterminado. Cuando SQL Server asigna una página a una tabla o índice para el que no se especificó ningún grupo de archivos cuando se crearon, las páginas se asignan desde el grupo de archivos predeterminado. Para cambiar el grupo de archivos predeterminado de un grupo de archivos a otro grupo de archivos, debe tener la función db fija db_owner.

De forma predeterminada, el grupo de archivos principal es el grupo de archivos predeterminado. El usuario debe tener un rol fijo de base de datos db_owner para poder realizar copias de seguridad de archivos y grupos de archivos individualmente.

Archivos

Las bases de datos tienen tres tipos de archivos: archivo de datos principal, archivo de datos secundario y archivo de registro. El archivo de datos principal es el punto de partida de la base de datos y apunta a los otros archivos de la base de datos.

Cada base de datos tiene un archivo de datos principal. Podemos dar cualquier extensión para el archivo de datos principal, pero la extensión recomendada es.mdf. El archivo de datos secundario es un archivo que no es el archivo de datos principal de esa base de datos. Algunas bases de datos pueden tener varios archivos de datos secundarios. Es posible que algunas bases de datos no tengan un solo archivo de datos secundario. La extensión recomendada para el archivo de datos secundario es.ndf.

Los archivos de registro contienen toda la información de registro utilizada para recuperar la base de datos. La base de datos debe tener al menos un archivo de registro. Podemos tener varios archivos de registro para una base de datos. La extensión recomendada para el archivo de registro es.ldf.

La ubicación de todos los archivos en una base de datos se registra tanto en la base de datos maestra como en el archivo principal de la base de datos. La mayoría de las veces, el motor de la base de datos utiliza la ubicación del archivo de la base de datos maestra.

Los archivos tienen dos nombres: lógico y físico. El nombre lógico se utiliza para hacer referencia al archivo en todas las declaraciones de T-SQL. El nombre físico es OS_file_name, debe seguir las reglas del SO. Los archivos de datos y de registro se pueden colocar en sistemas de archivos FAT o NTFS, pero no en sistemas de archivos comprimidos. Puede haber hasta 32,767 archivos en una base de datos.

Extensiones

Las extensiones son unidades básicas en las que se asigna espacio a tablas e índices. Una extensión es de 8 páginas contiguas o 64 KB. SQL Server tiene dos tipos de extensiones: uniforme y mixto. Las extensiones uniformes se componen de un solo objeto. Las extensiones mixtas son compartidas por hasta ocho objetos.

Páginas

Es la unidad fundamental de almacenamiento de datos en MS SQL Server. El tamaño de la página es de 8 KB. El inicio de cada página es un encabezado de 96 bytes que se utiliza para almacenar información del sistema, como el tipo de página, la cantidad de espacio libre en la página y la identificación del objeto que posee la página. Hay 9 tipos de páginas de datos en SQL Server.

  • Data - Filas de datos con todos los datos excepto texto, ntext e imagen.

  • Index - Entradas de índice.

  • Tex\Image - Datos de texto, imagen y ntext.

  • GAM - Información sobre extensiones asignadas.

  • SGAM - Información sobre extensiones asignadas a nivel de sistema.

  • Page Free Space (PFS) - Información sobre el espacio libre disponible en páginas.

  • Index Allocation Map (IAM) - Información sobre las extensiones utilizadas por una tabla o índice.

  • Bulk Changed Map (BCM) - Información sobre extensiones modificadas por operaciones masivas desde la última declaración de registro de respaldo.

  • Differential Changed Map (DCM) - Información sobre extensiones que han cambiado desde la última declaración de la base de datos de respaldo.

Arquitectura de archivos de registro

El registro de transacciones de SQL Server funciona lógicamente como si el registro de transacciones fuera una cadena de registros. Cada registro de registro se identifica mediante el número de secuencia de registro (LSN). Cada registro contiene el ID de la transacción a la que pertenece.

Los registros de registro para modificaciones de datos registran la operación lógica realizada o registran las imágenes de antes y después de los datos modificados. La imagen anterior es una copia de los datos antes de que se realice la operación; la imagen posterior es una copia de los datos después de que se ha realizado la operación.

Los pasos para recuperar una operación dependen del tipo de registro:

  • Operación lógica registrada.
    • Para hacer avanzar la operación lógica, la operación se realiza de nuevo.
    • Para revertir la operación lógica, se realiza la operación lógica inversa.
  • Imagen de antes y después registrada.
    • Para hacer avanzar la operación, se aplica la imagen posterior.
    • Para revertir la operación, se aplica la imagen anterior.

Los diferentes tipos de operaciones se registran en el registro de transacciones. Estas operaciones incluyen:

  • El inicio y el final de cada transacción.

  • Cada modificación de datos (insertar, actualizar o eliminar). Esto incluye cambios por procedimientos almacenados del sistema o declaraciones de lenguaje de definición de datos (DDL) en cualquier tabla, incluidas las tablas del sistema.

  • Cada extensión y asignación o desasignación de páginas.

  • Crear o eliminar una tabla o índice.

También se registran las operaciones de reversión. Cada transacción reserva espacio en el registro de transacciones para asegurarse de que exista suficiente espacio de registro para admitir una reversión provocada por una declaración de reversión explícita o si se encuentra un error. Este espacio reservado se libera cuando se completa la transacción.

La sección del archivo de registro desde el primer registro de registro que debe estar presente para una reversión exitosa en toda la base de datos al último registro escrito se denomina parte activa del registro o registro activo. Esta es la sección del registro necesaria para una recuperación completa de la base de datos. Ninguna parte del registro activo se puede truncar. El LSN de este primer registro se conoce como el LSN de recuperación mínimo (Min LSN).

El Motor de base de datos de SQL Server divide cada archivo de registro físico internamente en varios archivos de registro virtuales. Los archivos de registro virtuales no tienen un tamaño fijo y no hay un número fijo de archivos de registro virtuales para un archivo de registro físico.

El motor de base de datos elige el tamaño de los archivos de registro virtuales de forma dinámica mientras crea o amplía los archivos de registro. El motor de base de datos intenta mantener una pequeña cantidad de archivos virtuales. Los administradores no pueden configurar ni establecer el tamaño o la cantidad de archivos de registro virtuales. La única vez que los archivos de registro virtuales afectan el rendimiento del sistema es si los archivos de registro físicos se definen por valores de tamaño pequeño y crecimiento_incremento.

El valor de tamaño es el tamaño inicial del archivo de registro y el valor de growth_increment es la cantidad de espacio que se agrega al archivo cada vez que se requiere espacio nuevo. Si los archivos de registro crecen a un tamaño grande debido a muchos incrementos pequeños, tendrán muchos archivos de registro virtuales. Esto puede ralentizar el inicio de la base de datos y también registrar las operaciones de copia de seguridad y restauración.

Recomendamos que asigne a los archivos de registro un valor de tamaño cercano al tamaño final requerido y que también tenga un valor de incremento de crecimiento relativamente grande. SQL Server utiliza un registro de escritura anticipada (WAL), que garantiza que no se escriban modificaciones de datos en el disco antes de que el registro de registro asociado se escriba en el disco. Esto mantiene las propiedades ACID para una transacción.