SQL Server Architecnología (explicada)

MS SQL Server tiene una arquitectura cliente-servidor. El proceso de MS SQL Server comienza cuando la aplicación cliente envía una solicitud. SQL Server la acepta, la procesa y responde con los datos procesados. A continuación, se muestra en detalle la arquitectura completa:

Como muestra el siguiente diagrama, SQL Server consta de tres componentes principales. Architectura:

1. Capa de protocolo
2. Motor relacional
3. Motor de almacenamiento

Capa de protocolo – SNI

La capa de protocolo de SQL Server, también conocida como interfaz de red del servidor (SNI), admite tres tipos de arquitectura cliente-servidor. Cada protocolo se adapta a un escenario de red diferente. Comprender estos protocolos es fundamental antes de analizar cómo se procesan las consultas internamente.

Memoria compartida

Imaginemos una conversación matutina. Tom y su madre están en el mismo lugar lógico: su casa. Tom pide café y su madre se lo sirve directamente. De forma similar, SQL Server proporciona el protocolo de memoria compartida cuando el cliente y el servidor se ejecutan en la misma máquina. Ambos se comunican mediante memoria compartida sin sobrecarga de red.

Analogía: Tom se corresponde con el Cliente, Mamá con SQL Server, Casa con la Máquina y la comunicación verbal con el protocolo de Memoria Compartida.

Notas de configuración: In SQL Management Studio, la opción “Nombre del servidor” para una conexión local puede ser “.”, “localhost”, “127.0.0.1” o “Máquina\Instancia”.

TCP / IP

Ahora bien, supongamos que Tom quiere un café de una tienda ubicada a 10 km de distancia. Tom está en casa y la cafetería se encuentra en un mercado concurrido. Se comunican a través de una red celular. De manera similar, SQL Server proporciona la Protocolo TCP / IP cuando el cliente y SQL Server están en máquinas separadas conectadas a través de una red.

Analogía: Tom se corresponde con el Cliente, la cafetería con SQL Server, el hogar y el mercado con ubicaciones remotas, y la red celular con el protocolo TCP/IP.

Notas de configuración: En SQL Management Studio, la opción "Nombre del servidor" para una conexión TCP/IP debe ser "Máquina\Instancia del servidor". SQL Server utiliza el puerto 1433 de forma predeterminada para las conexiones TCP/IP.

Tubos con nombre

Finalmente, Tom quiere té verde de su vecina Sierra. Al ser vecinos, se encuentran en la misma ubicación física y se comunican a través de una red interna. De manera similar, SQL Server proporciona el protocolo Named Pipe cuando el cliente y el servidor están conectados mediante una red de área local (LAN).

Analogía: Tom se corresponde con el Cliente, Sierra con SQL Server, ser vecinos se corresponde con LAN y la intra-red se corresponde con el protocolo Named Pipe.

Notas de configuración: Las canalizaciones con nombre están deshabilitadas de forma predeterminada y deben habilitarse a través del Administrador de configuración de SQL.

¿Qué es el TDS?

Ahora que los tres tipos de arquitectura cliente-servidor están claros, veamos TDS:

• TDS significa flujo de datos tabulares.
• Los tres protocolos utilizan paquetes TDS.
• TDS se encapsula en paquetes de red, lo que permite la transferencia de datos desde la máquina cliente a la máquina servidora.
• TDS fue desarrollado inicialmente por Sybase y ahora es propiedad de Microsoft.

La siguiente tabla compara los tres protocolos de conexión de SQL Server:

Elemento	Memoria compartida	TCP / IP	Tubos con nombre
Alcance de la red	La misma máquina	Remoto (WAN/Internet)	LAN solamente
Puerto predeterminado	N/A	1433	445
Rendimiento	Más rápido (sin sobrecarga de red)	Bueno (optimizado para WAN)	Bueno (optimizado para LAN)
Activado por defecto	Sí:	Sí:	No
Mejores casos de uso	Desarrollo y pruebas locales	Acceso remoto a la producción	Entornos LAN de confianza

Con la capa de protocolo gestionando la comunicación de red, el siguiente paso en la arquitectura de SQL Server es el procesamiento de la consulta en sí. Aquí es donde entra en juego el motor relacional.

Motor relacional

El Motor Relacional, también conocido como Procesador de Consultas, contiene los componentes de SQL Server que determinan qué debe hacer una consulta y cómo ejecutarla de la manera más eficiente. Es responsable de ejecutar las consultas de los usuarios solicitando datos al Motor de Almacenamiento y procesando los resultados devueltos.

Tal y como se muestra en el diagrama arquitectónico, el Motor Relacional consta de tres componentes principales:

Analizador CMD

Los datos recibidos de la capa de protocolo se transfieren al motor relacional. El analizador CMD es el primer componente en recibir los datos de la consulta. Su función principal es verificar la consulta en busca de errores sintácticos y semánticos y, posteriormente, generar un árbol de consulta.

Verificación sintáctica: Al igual que cualquier otro lenguaje de programación, SQL Server cuenta con un conjunto predefinido de palabras clave y reglas gramaticales. SELECT, INSERT, UPDATE y muchas otras pertenecen a esta lista. El analizador CMD verifica que la entrada cumpla con estas reglas. Si la entrada del usuario difiere de la sintaxis esperada, el analizador devuelve un error.

Ejemplo: Imaginemos a un ruso entrando en un restaurante japonés y pidiendo en ruso. El camarero solo entiende japonés y no puede procesar el pedido. Del mismo modo, si un usuario escribe «SELECR» en lugar de «SELECT», el analizador de comandos devuelve un error porque no reconoce la palabra clave.

Verificación semántica: Esto lo realiza el Normalizador. Este verifica si los nombres de las columnas, las tablas y otros objetos consultados existen realmente en el esquema. Si existen, el Normalizador los vincula a la consulta. Este proceso también se conoce como Vinculación. Cuando las consultas de usuario contienen una VISTA, el Normalizador la reemplaza con la definición de vista almacenada internamente.

Ejemplo: Correr SELECT * from USER_ID Esto provocaría que el analizador generara un error durante la comprobación semántica si la tabla USER_ID no existe en la base de datos.

Crear árbol de consultas: Este paso genera diferentes árboles de ejecución que representan las diversas formas en que se puede ejecutar una consulta. Todos los árboles producen el mismo resultado deseado.

Optimizador

El optimizador crea un plan de ejecución para la consulta del usuario. Este plan determina cómo se ejecutará la consulta. No todas las consultas se optimizan. La optimización se aplica a los comandos DML (Lenguaje de Modificación de Datos) como SELECT, INSERT, DELETE y UPDATE. Los comandos DDL como CREATE y ALTER no se optimizan, sino que se compilan en un formato interno.

El coste de la consulta se calcula en función de factores como el uso de la CPU, el uso de la memoria y las necesidades de entrada/salida. La función del optimizador es encontrar el plan de ejecución más económico y rentable, no necesariamente el mejor en términos absolutos.

Ejemplo: Imagina que quieres abrir una cuenta bancaria online. Un banco tarda un máximo de 2 días. Además, tienes una lista de otros 20 bancos que podrían tardar menos tiempo. Buscar en los 20 bancos puede que no te permita encontrar una opción más rápida, y la búsqueda en sí misma consume tiempo. Hubiera sido mejor optar por el primer banco. De forma similar, el Optimizador SQL utiliza algoritmos exhaustivos y heurísticos para minimizar el tiempo de ejecución de las consultas.

El optimizador realiza búsquedas en tres fases:

Fase 0: Búsqueda de un plan trivial

Esta es la etapa de preoptimización. Para algunas consultas, solo existe un plan práctico, conocido como plan trivial. No es necesario seguir buscando, ya que cualquier búsqueda adicional encontraría el mismo plan de ejecución a un costo extra.

Fase 1: Búsqueda de planes de procesamiento de transacciones

Esto incluye la búsqueda de planes tanto simples como complejos. La búsqueda de planes simples utiliza análisis estadísticos de datos de columnas e índices, generalmente restringidos a un índice por tabla. Si no se encuentra ningún plan simple, se realiza una búsqueda más compleja que involucra múltiples índices por tabla.

Fase 2: Procesamiento paralelo y optimización

Si las estrategias anteriores no generan un plan adecuado, el optimizador busca posibilidades de procesamiento paralelo en función de las capacidades de procesamiento de la máquina. Si el procesamiento paralelo no es posible, comienza una fase de optimización final que utiliza todas las opciones restantes para encontrar el mejor plan de ejecución posible.

Ejecutor de consultas

El ejecutor de consultas llama al método de acceso en el motor de almacenamiento. Proporciona un plan de ejecución que contiene la lógica de obtención de datos necesaria para la ejecución. Una vez recibidos los datos del motor de almacenamiento, el resultado se publica en la capa de protocolo y se envía al usuario final.

Una vez que el motor relacional determina cómo ejecutar una consulta, el motor de almacenamiento gestiona las operaciones físicas de los datos. Esta capa administra cómo se almacenan, se guardan en caché y se recuperan los datos del disco.

Motor de almacenamiento

El motor de almacenamiento se encarga de guardar los datos en un sistema de almacenamiento, como un disco o una SAN, y de recuperarlos cuando sea necesario. Antes de examinar los componentes del motor de almacenamiento, es importante comprender cómo se almacenan físicamente los datos.

Archivos de datos y extensiones

Los archivos de datos almacenan físicamente los datos en forma de páginas de datos, y cada página tiene un tamaño de 8 KB. Esta es la unidad de almacenamiento más pequeña en SQL ServerLas páginas de datos se agrupan lógicamente en extensiones. Ningún objeto se asigna directamente a una página individual; en su lugar, el mantenimiento se realiza mediante extensiones. Cada página tiene un encabezado (96 bytes) que contiene metadatos como el tipo de página, el número de página, el espacio utilizado, el espacio libre y punteros a las páginas siguiente y anterior.

Tipos de archivo

Archivo principal: Cada base de datos contiene un archivo principal. Este almacena todos los datos importantes relacionados con tablas, vistas, disparadores y otros objetos. Su extensión suele ser .mdf, pero puede ser cualquier otra.

Archivo secundario: Una base de datos puede contener o no varios archivos secundarios. Estos son opcionales y contienen datos específicos del usuario. La extensión suele ser .ndf, pero puede ser cualquier otra.

Archivo de registro: También conocidos como registros de escritura anticipada. La extensión es .ldf. Los archivos de registro se utilizan para la gestión de transacciones, la recuperación ante instancias no deseadas y la reversión de transacciones no confirmadas.

El motor de almacenamiento tiene tres componentes principales. Cada uno desempeña un papel específico en la gestión del acceso y la integridad de los datos.

Método de acceso

El método de acceso actúa como una interfaz entre el ejecutor de consultas y el Buffer Administrador o registros de transacciones. No realiza la ejecución por sí mismo, sino que determina el tipo de consulta:

• Si la consulta es una Sentencia SELECT (DML), se pasa al Buffer Gerente para su posterior procesamiento.
• Si la consulta es una Sentencias que no son SELECT (DDL y DML), se pasa al Administrador de transacciones. Esto incluye principalmente las sentencias UPDATE, INSERT y DELETE.

Buffer Manager

La Buffer El administrador gestiona las funciones principales de Plan Cache, el análisis de datos y el manejo de páginas modificadas.

Planificar caché

Plan de consulta existente: La Buffer El administrador comprueba si el plan de ejecución existe en la caché de planes almacenada. Si existe, se utilizan directamente el plan de consulta almacenado en caché y su caché de datos asociada.

Plan de caché para el primer uso: Si el plan de ejecución de una consulta inicial es complejo, se almacena en la caché de planes. Esto garantiza una mayor disponibilidad la próxima vez que SQL Server reciba la misma consulta.

Análisis de datos: Buffer Almacenamiento en caché y datos

La Buffer El administrador proporciona acceso a los datos necesarios. Existen dos enfoques posibles dependiendo de si los datos existen en la caché:

Buffer Caché – Análisis suave

La Buffer El gerente busca datos en el Buffer Caché. Si los datos están presentes, el ejecutor de consultas los utiliza directamente. Esto mejora el rendimiento, ya que la obtención de datos desde la caché requiere menos operaciones de entrada/salida que la obtención desde el almacenamiento en disco.

Almacenamiento de datos: análisis sintáctico completo

Si los datos no están presentes en el Buffer En la caché, los datos necesarios se buscan en el almacenamiento de datos en el disco. Posteriormente, los datos también se almacenan en la caché de datos para su uso futuro.

Gerente de transacciones

El Administrador de transacciones se invoca cuando el Método de acceso determina que una consulta no es una instrucción SELECT. Garantiza la coherencia y durabilidad de los datos a través de varios subcomponentes:

Administrador de registro

El administrador de registros mantiene track de todas las actualizaciones realizadas en el sistema a través de registros almacenados en los Registros de transacciones. Cada entrada de registro contiene un Número de secuencia de registro junto con el ID de transacción y el Registro de modificación de datos. Este mecanismo tracks transacciones confirmadas y revertidas.

Administrador de bloqueo

Durante una transacción, los datos asociados en el almacenamiento entran en un estado bloqueado. El Administrador de bloqueos maneja este proceso, asegurando la consistencia y el aislamiento de los datos. Estas propiedades también se conocen como ACID (AtomIcidad, Consistencia, Aislamiento, Durabilidad).

Proceso de ejecución

El proceso de ejecución sigue estos pasos:

1. El gestor de registros comienza a registrar la información y el gestor de bloqueos bloquea los datos asociados.
2. Se conserva una copia de los datos en el Buffer Cache.
3. En el registro se conserva una copia de los datos que deben actualizarse. Buffery todos los eventos actualizan los datos en Data Buffer.
4. Las páginas que almacenan datos modificados se conocen como Páginas sucias.

Registro de puntos de control y de escritura anticipada

El proceso de punto de control se ejecuta aproximadamente una vez por minuto y marca todas las páginas modificadas para escribirlas en el disco. Sin embargo, la página primero se envía a la página de datos del archivo de registro desde el Buffer Registro. Este mecanismo se conoce como registro de escritura anticipada (Write-Ahead Logging). Las páginas modificadas permanecen en la caché incluso después de haber sido escritas en el disco.

Lazy Writer

Cuando SQL Server detecta una carga pesada y se necesita memoria intermedia para nuevas transacciones, libera las páginas modificadas de la caché. El perezoso Writer Funciona mediante el algoritmo LRU (menos usado recientemente) para limpiar las páginas del grupo de búferes y transferirlas al disco.

Cómo SQL Server procesa una consulta de principio a fin

Comprender cada capa individualmente es valioso, pero ver cómo funcionan juntas aclara el panorama completo. Cuando una aplicación cliente envía una consulta SQL, ocurre la siguiente secuencia:

La Capa de protocolo Recibe la solicitud a través de memoria compartida, TCP/IP o tuberías con nombre y la encapsula en un paquete TDS. Motor relacional A continuación, toma el control: el analizador CMD comprueba la sintaxis y la semántica, el optimizador genera el plan de ejecución más económico y el ejecutor de consultas comienza la recuperación de datos.

El ejecutor de consultas llama al Motor de almacenamiento Método de acceso, que enruta las consultas SELECT al Buffer Administrador y consultas de modificación al Administrador de transacciones. Buffer El administrador comprueba la caché del plan y Buffer Primero se almacena en caché (análisis suave). Si los datos no están en caché, se realiza una lectura del disco (análisis duro). Para las operaciones de escritura, el Administrador de transacciones coordina el Administrador de registros, el Administrador de bloqueos y el proceso de punto de control para garantizar el cumplimiento de las propiedades ACID.

Una vez que el motor de almacenamiento devuelve los datos solicitados, el motor relacional formatea el conjunto de resultados y la capa de protocolo lo entrega de vuelta a la aplicación cliente a través del mismo protocolo TDS.

Cómo elegir el protocolo adecuado para las conexiones a SQL Server

La selección del protocolo correcto depende de la relación física entre el cliente y el servidor, así como de los requisitos de rendimiento.

Utilizar memoria compartida Cuando la aplicación cliente se ejecuta en la misma máquina que SQL Server. Esta es la opción más rápida, ya que elimina la sobrecarga de red. Es ideal para el desarrollo local, las pruebas y las implementaciones en una sola máquina.

Utilice TCP/IP Cuando el cliente y el servidor se encuentran en máquinas diferentes conectadas a través de una WAN o internet, este es el protocolo más utilizado en entornos de producción. SQL Server escucha en el puerto 1433 de forma predeterminada, y este protocolo admite conexiones cifradas mediante TLS.

Utilice tuberías con nombre Cuando el cliente y el servidor se encuentran en la misma LAN de confianza y el rendimiento en redes internas es una prioridad, Named Pipes está deshabilitado de forma predeterminada y debe habilitarse a través del Administrador de configuración de SQL Server. Si bien es menos común en implementaciones modernas, sigue siendo útil para aplicaciones de intranet heredadas.

Preguntas Frecuentes