cloud

Jul

2024

Logins y Users: Seguridad en SQL

Una de las principales labores que tenemos como administradores de bases de datos es la gestión de su seguridad y, para eso, es imprescindible la gestión de inicios de sesión (Logins) y usuarios (Users). Puede parecer que son lo mismo pero no lo son y, entender sus particularidades y diferencias va a ser clave para nuestra labor. Además, a estos conceptos tenemos que sumarle uno nuevo, los usuarios independientes, que se han implementado en las soluciones SQL en Azure y que harán que cambiemos nuestra forma de actuar en muchos casos.

Logins o Inicios de Sesión, primera capa de seguridad

Los inicios de sesión en SQL Server son entidades de seguridad a nivel de servidor que permite a los usuarios autenticarse y acceder al servidor SQL. Los inicios de sesión pueden ser de dos tipos: inicios de sesión basados en Windows y inicios de sesión de SQL Server.

Los logins de Windows aprovechan las cuentas de usuario o grupos de seguridad definidos en Active Directory o en el equipo local, permitiendo una autenticación integrada y centralizada. Por otro lado, los logins de SQL Server son gestionados directamente por SQL Server y, para crearlos, tendremos que proporcionar un nombre de inicio de sesión y una contraseña específicos para SQL Server.

Creación de logins

Para crear logins usaremos la sintaxis CREATE LOGIN de la siguiente manera:

a) Para un login de Windows usaremos:

CREATE LOGIN MiDominio\MiUsuarioDeWindows FROM WINDOWS

b) Para un login de SQL usaremos:

CREATE LOGIN MiLogin WITH PASSWORD=’MiContraseñ@SuperSegurísima1234’

Usuarios en SQL Server, segunda capa de seguridad

A diferencia de los inicios de sesión, los usuarios en SQL Server existen a nivel de base de datos. Mientras que el inicio de sesión proporciona acceso a la instancia del servidor, el usuario define qué recursos dentro de una base de datos específica puede acceder ese inicio de sesión.

Los usuarios se utilizan para gestionar permisos dentro de una base de datos, controlando el acceso a tablas, procedimientos almacenados y otros objetos. Cada usuario de base de datos puede estar asociado a un inicio de sesión o no, pero un inicio de sesión puede estar asociado a múltiples usuarios en diferentes bases de datos. Cuando un usuario no está asociado a un inicio de sesión decimos que está huérfano.

Creación de users

Crear un usuario en SQL Server se hace, posicionado en la base de datos, con la sintaxis CREATE USER y obligatoriamente implica especificar el inicio de sesión al que estará asociado. Por ejemplo:

USE MiBaseDeDatos
GO
CREATE USER MiUsuario FOR LOGIN MiLogin;

El Concepto de SID ¿Qué es y por qué es clave para la seguridad?

El SID (Security Identifier) es un identificador único que SQL Server asigna a cada inicio de sesión y usuario. Este identificador es crucial porque es lo que realmente utiliza SQL Server para vincular inicios de sesión con usuarios y para gestionar los permisos. Aunque los nombres de los inicios de sesión y usuarios pueden ser los mismos en diferentes bases de datos o instancias, el SID es lo que realmente distingue a cada entidad de seguridad.

Cuando estamos usando logins de windows siempre usaremos el SID que tenga ese usuario en el Directorio Activo (lo sé es lioso pero es que a nivel windows si se llama usuario y nosotros en SQL lo usamos como login).

Problemas conocidos con los SID

Como habrás imaginado, cada SID es único para cada inicio de sesión y, aunque en dos servidores tengamos un login con el mismo nombre, al mover las bases de datos de uno a otro no van a coincidir esos SID y se quedará huérfano el usuario en la base de datos. Lo explico más en detalle, los permisos a nivel de base de datos asignados a un usuario o a nivel de instancia a un login, están realmente vinculados a su SID, no a su nombre. Esto hace que, durante migraciones o en configuraciones de alta disponibilidad, como los Grupos de Disponibilidad (AG) en SQL Server, sea fundamental que los SIDs de los inicios de sesión y usuarios coincidan entre las diferentes instancias y bases de datos.

Si los SIDs no coinciden, los usuarios no podrán acceder a los recursos correspondientes porque SQL Server no los reconocerá correctamente. Por suerte eso se puede solucionar copiando el SID del login en uno de los servidores y recreando el login en el resto con ese mismo SID como te expliqué aquí en un artículo o aquí en video

Usuarios Independientes o Contenidos, simplificando la seguridad

Tanto en SQL Server a partir de SQL 2012 como en las soluciones SQL en Azure existe un modelo de entidades de seguridad que se basa en usuarios sin necesidad de login. A esto se le llama usuarios de base de datos independientes o contenidos. Estos usuarios no están vinculados a un inicio de sesión a nivel de servidor, sino que existen exclusivamente dentro de una base de datos.

Tiene ciertos inconvenientes como que en la conexión siempre se debe especificar a qué base de datos te quieres conectar y si quieres cambiar de base de datos tienes que cerrar tu conexión actual y volver a conectar. Sin embargo en Azure SQL Database, donde “no hay servidor” y esto no es un problema, los usuarios independientes cobran relevancia. Si nos olvidamos de esta limitación, si no es importante para nosotros, las ventajas son indiscutibles, cada base de datos puede tener su propio conjunto de usuarios y permisos, independiente de otros recursos lo que hace que las bases de datos pueden moverse entre servidores o instancias sin necesidad de volver a crear o ajustar inicios de sesión a nivel de servidor.

Ventajas en Entornos de Replicación y Alta Disponibilidad

Los usuarios independientes son particularmente ventajosos en entornos con replicación y alta disponibilidad (HA). En configuraciones tradicionales, cada vez que replicamos o movemos una base de datos a otra instancia, debemos asegurarnos de que los inicios de sesión y sus SIDs coincidan en todas las instancias involucradas. Esto puede ser complicado y propenso a errores.

En entornos de Grupos de Disponibilidad (AG), se requiere un cuidadoso manejo de los inicios de sesión para asegurar que los usuarios puedan acceder a las bases de datos replicadas en diferentes nodos del AG. Debemos crear cada inicio de sesión manualmente en cada réplica secundaria, asegurando que los SIDs coincidan. Con usuarios independientes, este problema se simplifica enormemente. Dado que estos usuarios existen únicamente dentro de la base de datos y no dependen de un inicio de sesión a nivel de servidor, las preocupaciones sobre la coincidencia de SIDs se eliminan. Esto hace que la administración de permisos y accesos en entornos distribuidos sea más sencilla y menos propensa a errores.

Comparación con el Paradigma Tradicional

Comparado con el enfoque tradicional de SQL Server, los usuarios independientes proporcionan una mayor flexibilidad y facilidad de gestión en entornos distribuidos. En cambio, la capacidad de gestionar usuarios directamente dentro de cada base de datos, reduce la complejidad administrativa.

Por ejemplo, un usuario independiente se crea así:

USE MiBaseDeDatos
CREATE USER MiUsuarioIndependiente WITH PASSWORD = 'ContraseñaSegura';

Mientras que según el enfoque tradicional sería:

USE master
CREATE LOGIN MiLogin WITH PASSWORD = 'ContraseñaSegura';
USE MiBaseDeDatos
CREATE USER MiUsuario FOR LOGIN MiLogin;

Seguridad y buenas prácticas

Para asegurar nuestras instancias de SQL Server y las bases de datos asociadas, debemos seguir varias buenas prácticas. Lo primero y fundamental es seguir el principio de privilegios mínimos otorgando solo los permisos necesarios a cada usuario. Otra recomendación es seguir una política de contraseñas que asegure contraseñas fuertes y políticas de expiración y complejidad. Por último, os recomendaría crear auditorías para rastrear el acceso y las acciones de los usuarios.

Seguridad en Azure

En Azure, además de las prácticas anteriores, debemos considerar las herramientas y servicios adicionales que ofrece la plataforma.La integración con Microsoft Entra (antes llamado Azure Active Directory) para una gestión centralizada de identidades será un gran aliado. Al usar Microsoft Entra podremos implementar requisitos de segundo factor de autenticación (MFA) como por ejemplo requerir un código temporal enviado por SMS, llamada o una aplicación de autenticación además de la contraseña del usuario. También disponemos de servicios como Azure Key Vault para la gestión de claves y certificados. Y por supuesto, las alertas y monitorización avanzada utilizando Azure Security Center, Azure Monitor y Azure Insights para detectar y responder a amenazas de seguridad.

Conclusión

La gestión de inicios de sesión y usuarios en SQL Server es un aspecto fundamental de la administración de bases de datos, que garantiza la seguridad y el control de acceso a nuestros datos. Mientras que los inicios de sesión proporcionan acceso a la instancia del servidor, los usuarios dentro de las bases de datos gestionan el acceso a los recursos específicos. Con la evolución hacia entornos cloud, los usuarios independientes en Azure SQL Database ofrecen una mayor flexibilidad y facilitan la administración en escenarios distribuidos. Aprovechar las ventajas de los usuarios independientes en entornos de replicación y HA nos permite reducir la complejidad y mejorar la eficiencia administrativa.

Comprender estas diferencias y aplicar las mejores prácticas de seguridad nos permite aprovechar al máximo nuestras bases de datos SQL Server, ya sea en implementaciones tradicionales o en la nube.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Jul

2024

Diferencias Instancia, BBDD y Esquema en SQL Server

Cuando hablamos de bases de datos, términos como instancia, base de datos (BBDD) y esquema son fundamentales, pero no en todos los sistemas tienen los mismos significados, lo que genera confusión entre los desarrolladores y administradores de bases de datos principiantes. En este artículo, voy a explicarte lo que significan en SQL Server así como las diferencias y relaciones entre estos conceptos tan clave que son el pilar de nuestro RDBMS.

Cuando trabajamos con SQL Server o Azure SQL, es crucial entender cómo están organizados los datos y las estructuras que los contienen. A menudo, los términos «instancia», «base de datos» y «esquema» se utilizan indistintamente, pero cada uno tiene un significado específico y una función distinta. Veamoslo.

¿Qué es una Instancia en SQL Server?

Una instancia de SQL Server es una “copia” del motor de base de datos que se ejecuta como un servicio del sistema operativo. Es la aplicación del servidor SQL instalada en nuestro equipo y podemos tener una o varias instaladas y ejecutándose a la vez. Cada instancia puede contener varias bases de datos y tiene su propia gestión de la memoria, los procesos y su propia configuración.

Tipos de Instancias

Existen dos tipos principales de instancias en SQL Server:

Instancia predeterminada: Por defecto es la primera instancia instalada en el servidor y no tiene nombre específico. Se accede a ella utilizando sólo el nombre del servidor.
Instancia con nombre: Se pueden instalar múltiples instancias con nombre en un mismo servidor, y se accede a ellas utilizando el formato Nombre_Servidor\Nombre_Instancia.

Esto no es siempre así, puedes poner un nombre a todas tus instancias o instalar primero una instancia con nombre y después la predeterminada. Lo que sí es importante es que solo puede haber una instancia predeterminada en el servidor y eso se configura al momento de la instalación.

Ventajas y desventajas de Múltiples Instancias

Las múltiples instancias tienen ciertas ventajas, por ejemplo, permiten un mayor aislamiento ya que se pueden separar las cargas de trabajo críticas en diferentes instancias. Esto nos permite una configuración independiente donde cada instancia puede tener su propia configuración de seguridad, memoria y opciones de rendimiento. Además es más sencillo gestionar actualizaciones y parches sin afectar a todas las bases de datos.

Esto no quiere decir que tengamos que tener un solo servidor de SQL con todas las instancias, aunque es una opción válida, todo lo que hemos visto como ventajas es aún mayor cuando tenemos servidores independientes cada uno con una instancia, aunque sean servidores virtuales. Aunque esta solución tiene un mayor coste, a mi es la que más me gusta.

Base de Datos (BBDD)

Una base de datos (BBDD) es una colección organizada de datos que se puede acceder, gestionar y actualizar fácilmente. En SQL Server, cada base de datos es una entidad autónoma que contiene sus propios conjuntos de archivos.Cada base de datos en SQL Server consta de tres tipos principales de archivos: Los archivos de datos primarios (.mdf) que contienen los datos de la base de datos y su estructura, los archivos de datos secundarios (.ndf) utilizados para distribuir datos en diferentes discos y mejorar el rendimiento y los archivos de registro o log de transacciones (.ldf) que almacenan todas las transacciones y cambios a la base de datos, cruciales para la recuperación ante fallos.

Esquemas en SQL Server

Un esquema es un contenedor lógico dentro de una base de datos que agrupa objetos como tablas, vistas, procedimientos almacenados, etc. Ayuda a organizar los objetos y facilita la gestión de permisos y la segmentación de datos.

Los esquemas son extremadamente útiles para la segregación de datos permitiéndonos separar datos y objetos por departamentos o aplicaciones y mejorando así la gestión de la seguridad al aplicar permisos a nivel de esquema en lugar de a nivel de objeto. También son un gran aliado para la organización ya que nos va a permitir mantener un orden lógico y estructurado dentro de una base de datos.

Diferencias Clave entre Instancia, BBDD y Esquema en SQL Server

Para recapitular, y a modo resumen, vamos a destacar las diferencias esenciales entre estos tres conceptos:

Instancia: Es el entorno de ejecución del motor de base de datos que puede contener múltiples bases de datos. Proporciona aislamiento y configuración independiente.
Base de Datos (BBDD): Es una colección autónoma de datos y objetos dentro de una instancia. Cada base de datos tiene su propio conjunto de archivos de datos y registro.
Esquema: Es un contenedor lógico dentro de una base de datos que organiza y agrupa objetos. Facilita la gestión de permisos y la segregación de datos.

Conclusión

Entender las diferencias y relaciones entre instancia, base de datos y esquema es fundamental para cualquier profesional que trabaje con SQL Server o Azure SQL. Desde la gestión de múltiples instancias hasta la organización de datos en esquemas, cada componente tiene su lugar y función específica. Al aprovechar estas herramientas de manera efectiva, podemos garantizar que nuestras bases de datos sean robustas, seguras y escalables, tanto en entornos locales como en la nube.

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Jul

2024

Auditoría en SQL Server: ¿Cómo configurarlas?

En nuestro anterior post hablamos sobre SQL Server Profiler y sus trazas y comentábamos que uno de sus usos puede ser el de auditoría ya que registra todos los eventos sobre nuestros servidores SQL Server. Sin embargo su elevado consumo de recursos no lo hace la solución más ideal para esta función. Y es que, SQL Server implementa una auditoría nativa mucho más potente con menor coste de recursos que la hacen la herramienta ideal. Configurar auditorías en SQL Server no solo nos va a ayudar a supervisar la actividad y los cambios, sino que también es un proceso clave en nuestra estrategia de protección de datos y cumplimiento normativo. En este artículo, voy a tratar de explicarte cómo puedes configurar eficazmente estas auditorías, optimizando cada aspecto para obtener el máximo beneficio.

¿Qué es la Auditoría de SQL Server?

La auditoría de SQL Server es un mecanismo que permite registrar y rastrear actividades y eventos dentro de nuestras instancias de SQL Server. La auditoría puede ser configurada para capturar una variedad de eventos, desde inicios de sesión hasta cambios en la configuración del servidor. Con estos registros, podemos realizar un seguimiento detallado de quién hizo qué y cuándo, lo que es vital para la seguridad y el cumplimiento normativo.

Tipos de Auditoría de SQL Server

SQL Server proporciona dos enfoques principales para la auditoría: la auditoría de instancia y la auditoría de base de datos. Cada uno tiene sus características específicas y se aplica en diferentes contextos según los requisitos de seguridad y cumplimiento por lo que no son excluyentes.

La auditoría de instancia se enfoca en eventos que afectan a toda la instancia de SQL Server, no solo a bases de datos individuales. Es ideal para capturar eventos que tienen un impacto global en el servidor y para mantener una vista general sobre la actividad de toda la instancia. Su uso principal es registrar el cumplimiento de políticas de seguridad como la gestión de accesos y el control de cambios en la configuración del servidor o de la instancia. Para ello, captura eventos que afectan a toda la instancia, como inicios de sesión, cambios en la configuración del servidor, y operaciones de mantenimiento. También permite registrar eventos de alto nivel que impactan el funcionamiento de la instancia.

La auditoría de base de datos se centra en eventos que ocurren dentro de una base de datos específica. Es ideal para capturar eventos relacionados con las operaciones de datos y la estructura de la base de datos, proporcionando un nivel de detalle más granular. Como hemos dicho, captura eventos a nivel de base de datos, como modificaciones en los datos, cambios en los objetos, y accesos a datos y nos permite definir qué operaciones se auditan en tablas, esquemas o procedimientos almacenados específicos. Se usa principalmente para registrar operaciones como inserciones, actualizaciones y eliminaciones, es decir, control de cambios y para monitorizar y registrar el acceso a datos sensibles o críticos dentro de una base de datos.

Auditoría de Instancia en SQL Server

Ya hemos visto que la auditoría a nivel de instancia nos permite capturar eventos que afectan a toda la instancia de SQL Server, independientemente de las bases de datos individuales. Este enfoque es útil para registrar eventos que ocurren a nivel de servidor, como cambios en la configuración del servidor o inicios de sesión.

Pasos para Configurar una Auditoría de Instancia

Primero, debemos definir una auditoría que especificará qué eventos se registrarán y cómo se almacenarán los resultados. Esto se hace mediante el SQL Server Management Studio (SSMS) o mediante Transact-SQL (T-SQL).

Usando SSMS:

En SSMS, navegamos a Seguridad > Auditorías.

Hacemos clic derecho en Auditorías y seleccionamos Nueva Auditoría.

En la ventana de propiedades, configuramos la ubicación del archivo de auditoría, que puede ser un archivo de registro, un archivo de eventos o un registro de la aplicación.

Establecemos las opciones necesarias, como el tamaño máximo del archivo y la política de retención.

Usando T-SQL:

USE [master]
GO
CREATE SERVER AUDIT [Audit-20240720-104102]
TO FILE 
( FILEPATH = N'C:\AuditLogs\'
 ,MAXSIZE = 1 GB
 ,MAX_FILES = 10
 ,RESERVE_DISK_SPACE = OFF
) WITH (QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE)
GO

Una vez creada la auditoría, debemos definir qué eventos específicos deseamos capturar. Esto se hace mediante la creación de especificaciones de auditoría.

Usando SSMS:

Navegamos a Seguridad > Especificaciones de Auditoría.

Hacemos clic derecho en Especificaciones de Auditoría y seleccionamos Nueva Especificación de Auditoría.

Seleccionamos la auditoría previamente creada y definimos los eventos que deseamos capturar, como Inicios de sesión o Cambios en la configuración.

Usando T-SQL:

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20240720-104510]
FOR SERVER AUDIT [Audit-20240720-104102]
ADD (SUCCESSFUL_LOGIN_GROUP),
ADD (FAILED_LOGIN_GROUP)
GO

Finalmente, habilitamos tanto la auditoría como las especificaciones para comenzar a capturar los eventos configurados desde SSMS con clic derecho del ratón sobre los objetos y habilitar.

Usando T-SQL:

ALTER SERVER AUDIT [Audit-20240720-104102] WITH (STATE = ON)
GO
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20240720-104510] WITH (STATE = ON)

Auditoría de Base de Datos en SQL Server

A diferencia de la anterior, la auditoría a nivel de base de datos se centra en registrar eventos que ocurren dentro de una base de datos específica. Este nivel de detalle es fundamental para monitorear actividades relacionadas con el contenido y los objetos de la base de datos.

Pasos para Configurar una Auditoría de Base de Datos

Al igual que con la auditoría a nivel de instancia, primero debemos crear una auditoría que especifique dónde se almacenarán los registros. Esta auditoría se crea a nivel de instancia y podemos usar la misma que teníamos antes, en la base de datos solo vamos a crear las especificaciones.

Usando SSMS:

Navegamos a la base de datos en Seguridad > Especificaciones de Auditoría.

Hacemos clic derecho en Especificaciones de Auditoría y seleccionamos Nueva Especificación de Auditoría.

Configuramos los eventos específicos, como operaciones de datos o cambios en los objetos.

Usando T-SQL:

USE [AdventureWorks2022]
GO
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20240720-105338]
FOR SERVER AUDIT [Audit-20240720-104102]
ADD (UPDATE ON DATABASE::[AdventureWorks2022] BY [public]),
ADD (INSERT ON DATABASE::[AdventureWorks2022] BY [public]),
ADD (DELETE ON DATABASE::[AdventureWorks2022] BY [public])
GO

Igual que antes, activamos la auditoría y las especificaciones para comenzar a registrar los eventos.

Usando T-SQL:

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20240720-105338] WITH (STATE = ON);

Auditoría vs SQL Server Profiler

Como vimos en el pasado post, SQL Server Profiler es otra herramienta que, aunque no se usa para auditorías a largo plazo, sigue siendo relevante para capturar eventos en tiempo real y para el análisis detallado de sesiones y transacciones. Vamos a comparar esta herramienta con las auditorías de SQL Server en términos de capacidades y usos.

Alcance y propósito

SQL Server Profiler captura eventos en tiempo real para el análisis detallado de sesiones y transacciones. Es ideal para depurar problemas y monitorear el rendimiento en tiempo real ya que proporciona una vista instantánea de la actividad de la base de datos. Por el contrario, las auditorías de SQL Server registran eventos a lo largo del tiempo, permitiendo un seguimiento extensivo y cumplimiento normativo. Son más adecuadas para el cumplimiento de regulaciones y para proporcionar informes detallados sobre eventos históricos ya que capturan eventos críticos y permiten su almacenamiento en archivos o registros para un análisis posterior.

Persistencia

Los datos capturados por Profiler son temporales y se almacenan en memoria mientras se realiza el seguimiento. Aunque se puede salvar un archivo de traza, este no está diseñado para almacenamiento a largo plazo o para el cumplimiento normativo. Las auditorías de SQL Server sin embargo si almacenan los eventos en archivos o registros, lo que permite un almacenamiento prolongado y una revisión a largo plazo. Además facilitan la conservación de datos históricos necesarios para el cumplimiento regulatorio.

Rendimiento

Como ya vimos también, SQL Server Profiler puede impactar el rendimiento del servidor durante la captura de eventos debido a la sobrecarga de recursos. lo que no lo hace la herramienta ideal para sesiones largas y poco específicas donde el rendimiento es crítico. En este sentido, las auditorías de SQL Server tienen un menor impacto en el rendimiento, especialmente cuando se configuran para capturar solo eventos esenciales. Además nos permiten ajustar la granularidad de la auditoría para minimizar la sobrecarga.

Usabilidad

SQL Server Profiler nos ofrece una interfaz gráfica para configurar y visualizar eventos en tiempo real pero requiere de una comprensión avanzada para interpretar los eventos capturados. Las auditorías de SQL Server que configuramos a través de SSMS o T-SQL, proporcionan una forma estructurada y más amigable de registrar eventos para que puedan ser consumidos por auditores y técnicos de ciberseguridad. Las especificaciones de auditoría permiten un control preciso sobre qué eventos se registran y cómo se almacenan.

Conclusión

Configurar auditorías en SQL Server, tanto a nivel de instancia como de base de datos, es fundamental para mantener un control exhaustivo sobre nuestras bases de datos y garantizar la seguridad y el cumplimiento. A través de la correcta configuración de auditorías y especificaciones, podemos registrar eventos críticos y analizar el acceso y las modificaciones a nuestros datos. Aunque el proceso puede parecer complejo, seguir estos pasos nos permite implementar una estrategia de auditoría efectiva que proporciona una visión detallada y precisa de la actividad en nuestras instancias y bases de datos. Al final, una auditoría bien configurada es una herramienta poderosa que fortalece nuestra postura de seguridad y facilita el cumplimiento normativo.

Jul

2024

SQL Server Profiler

Hoy os quiero hablar de SQL Server Profiler. Aunque su soporte está discontinuado y en un futuro será eliminado de las características de SQL Server en favor de otras herramientas como xEvents, sigue siendo una de las herramientas más poderosas y versátiles a nuestra disposición cuando estamos depurando un problema con consultas SQL.

Durante las próximas líneas, veremos en profundidad el uso del SQL Server Profiler, sus funcionalidades avanzadas, su impacto en los recursos del sistema y cómo puede ayudarnos a mantener nuestros sistemas SQL Server funcionando de manera eficiente.

¿Qué es el SQL Server Profiler?

El SQL Server Profiler es una herramienta nativa de SQL Server que nos permite crear trazas para capturar y analizar eventos que ocurren principalmente en instancias de SQL Server. Aunque su uso principal es para capturar eventos de SQL Server, también es posible conectarlo a una instancia de SQL Server Analysis Services y, por tanto, a un modelo de Power BI. Los eventos que vamos a poder capturar pueden ser consultas T-SQL, procedimientos almacenados, transacciones y mucho más, dependerá de a qué tipo de servidor estemos conectados. Como habrás imaginado ya, poder monitorizar y registrar estos eventos es crucial para ayudarnos en la resolución de problemas, la optimización del rendimiento y la auditoría de actividades en nuestras bases de datos.

Principales Características del SQL Server Profiler

SQL Server Profiler, como hemos visto, nos puede ser de gran utilidad para descubrir qué es lo que está pasando en nuestro SQL Server. Esto lo hace una herramienta ideal para cualquier DBA que persiga un problema de rendimiento o para un desarrollador que necesite depurar una aplicación. Para ello, podemos destacar estas características principales de la herramienta:

Monitorización en Tiempo Real y más: SQL Server Profiler nos permite observar la actividad sobre la base de datos a medida que ocurre, lo cual es esencial para identificar y solucionar problemas de rendimiento de manera inmediata. Pero no solo eso, también nos permite guardar esos archivos para analizarlos en detalle más adelante.
Filtrado de Eventos: Como el resultado de registrar toda la actividad puede ser un fichero tan grande que sea casi imposible de consumir, SQL Server Profiler implementa una serie de filtros específicos para centrarnos en eventos particulares, evitando así la sobrecarga de información y facilitando la identificación de problemas concretos.
Reproducción de Trazas: Como ya os comentaba antes, podemos guardar los archivos de trazas pero, no solo para su visualización. SQL Server Profiler nos da la capacidad de abrir esas trazas conectados en un entorno de prueba, por ejemplo, y reproducir paso a paso, todos los eventos de la traza. Esto es increíblemente útil para el análisis de problemas y depuración de código sin afectar el sistema de producción.

Configuración y Uso del SQL Server Profiler

Cuando queremos usar SQL Server Profiler primero debemos iniciar una nueva sesión de traza. Al hacerlo, se nos presentará una serie de opciones de configuración que nos permitirán personalizar la traza según nuestras necesidades. Veamos los pasos.

Pasos Básicos para Iniciar una Nueva Trazabilidad

Abrir SQL Server Profiler: Lo encontramos en el menú de herramientas de SQL Server Management Studio (SSMS) o como aplicación instalada en tu sistema operativo.
Crear Nueva Traza: Seleccionamos «File» > «New Trace» y nos conectamos a la instancia que queremos monitorizar. Recuerda que puede ser SQL Server (on premise o Azure), SSAS o un modelo de Power BI.
Seleccionar Plantilla: Podremos elegir una de las plantillas predefinidas, si es que se ajustan a nuestras necesidades, o configurar una nueva.
Configurar Eventos y Columnas: En esta pestaña podremos personalizar los eventos y columnas que queremos capturar y mostrar. Abajo a la derecha encontrarás unos checks para mostrar todos los eventos y columnas, recuerda activarlos para ver todas las opciones disponibles.
Aplicar Filtros: Podremos configurar filtros específicos para reducir la cantidad de datos capturados y enfocarnos en eventos relevantes. Es interesante por ejemplo el filtro por base de datos o por usuario si es que tenemos claro donde está más o menos localizado el problema.
Iniciar la Traza: Una vez configurada, iniciamos la traza y comenzamos a monitorizar los eventos en tiempo real.
Una vez con la traza iniciada la podremos pausar y reanudar a nuestro gusto. También detener definitivamente la captura de eventos. Una vez finalizado podremos guardar el fichero para analizarlo o reproducirlo más tarde.

Análisis de Datos Capturados

Una vez que hemos capturado una traza, el siguiente paso es analizar los datos para identificar posibles problemas de rendimiento o errores en la aplicación.

Uno de los usos más comunes del SQL Server Profiler es identificar consultas lentas que pueden estar afectando el rendimiento de la base de datos. Podemos buscar eventos específicos como «SQL» o «RPC» y revisar las métricas de tiempo de ejecución, CPU y lecturas/escrituras de disco para cada consulta.

Los bloqueos y deadlocks también pueden ser un gran problema que queramos analizar, sobre todo en sistemas con alta concurrencia (y sin un nivel de aislamiento Read Committed Snapshot). Utilizando eventos como «Lock» y «Lock Chain», podemos identificar las transacciones involucradas en deadlocks y tomar medidas para resolverlos, ya sea optimizando las consultas o ajustando la configuración de bloqueo.

Impacto de SQL Server Profiler en el rendimiento

Como puedes imaginar todo esto tiene una gran contrapartida y es su impacto en el rendimiento. El SQL Server Profiler puede generar una cantidad significativa de datos, especialmente en sistemas con alta actividad. Cada evento capturado se guarda en un archivo de traza, lo que puede resultar en un uso considerable del espacio en disco. Si no tenemos cuidado podemos llegar a llenar el disco.

Para evitar este problema de consumo excesivo de disco debemos tomar precauciones y aplicar filtros para capturar solo los eventos necesarios, de esta manera reduciremos la cantidad de datos registrados. También es importante configurar límites de tamaño para los archivos de traza y habilitar la opción de sobrescribir los archivos más antiguos. Por último debemos asegurarnos de que las trazas se almacenan en volúmenes con suficiente capacidad para evitar problemas de espacio en disco.

Otro de los problemas principales que podemos encontrar durante el proceso de captura y registro de eventos por parte del SQL Server Profiler es que puede afectar el rendimiento del servidor. El impacto varía según la cantidad de eventos capturados y la configuración de la traza. A mayor frecuencia de eventos capturados, mayor será el impacto en el rendimiento. Capturar una gran cantidad de eventos y columnas innecesarias aumenta la carga en el sistema, como es obvio.

Trata de acotar tus trazas en el tiempo y de capturar los eventos mínimos imprescindibles para evitar saturar la CPU y RAM de tu servidor, utilizar plantillas predefinidas optimizadas para tareas específicas puede ayudarte en este sentido. Es vital en este sentido programar la captura de trazas durante periodos de baja actividad del sistema o solo durante el tiempo que tengas localizado que se produce el error que tratas de depurar en lugar de mantenerlas activas por tiempo prolongado.

Casos de Uso Avanzados del SQL Server Profiler

Como has podido ver en lo que llevamos de artículo, SQL Server Profiler es una herramienta muy útil para depurar código o localizar problemas de rendimiento. Pero, no solo nos va a servir para eso, a continuación os presento otros escenarios en los que nos puede ser de utilidad.

Auditoría de Seguridad

Aunque no es lo ideal, SQL Server Profiler también puede ser utilizado para auditorías de seguridad. Podemos capturar eventos relacionados con el acceso a datos sensibles, cambios en la configuración del servidor y actividades de inicio de sesión. Esto nos permite mantener un registro detallado de las acciones realizadas en la base de datos, lo cual es esencial para cumplir con normativas de seguridad y auditoría. Sin embargo, sus especificaciones y consumo de recursos hacen que no sea la herramienta ideal. En este sentido podremos usar mejor los eventos extendidos o la auditoría nativa de SQL Server.

Optimización de Procedimientos Almacenados

La optimización de procedimientos almacenados es una tarea crítica. Muchas aplicaciones usan estos objetos para sus procesos en lugar de ejecutar código Ad Hoc. Utilizando el Profiler, podemos capturar la ejecución de procedimientos y analizar su rendimiento. Esto nos va a permitir identificar procedimientos que requieren optimización, ya sea mediante la revisión del código T-SQL, la reestructuración de índices y estadísticas o la modificación de la lógica de las consultas.

Diagnóstico de Problemas de Red

En entornos distribuidos, los problemas de red pueden afectar significativamente al rendimiento de SQL Server. El Profiler nos permite capturar eventos relacionados con conexiones y desconexiones, así como latencias en la comunicación. Con esta información, podemos trabajar junto con los administradores de red para resolver problemas de conectividad y mejorar el rendimiento general del sistema.

Uso del SQL Server Profiler para Monitorizar Power BI

Aunque es un proceso no documentado, podemos usar el Profiler para capturar las consultas DAX de Power BI. Yo esto lo he visto funcionando en una charla de Francisco Gutierrez en los Power BI Days de Bilbao y me dejó sorprendido. No esperaba que una herramienta discontinuada por Microsoft tuviera la capacidad de interactuar con Power BI.

Conclusión

El SQL Server Profiler es una herramienta muy potente para cualquier DBA que busque mantener el rendimiento de sus bases de datos SQL Server. Desde la identificación de consultas lentas hasta la auditoría de actividades y la resolución de problemas de red, el Profiler nos proporciona una visión detallada y en tiempo real del comportamiento de nuestro sistema. Su capacidad para personalizar la captura de eventos y analizar datos detallados lo convierte en una herramienta versátil y poderosa. Sin embargo, es importante ser conscientes del impacto que el Profiler puede tener en los recursos del sistema y aplicar estrategias adecuadas para minimizar su uso de disco y su impacto en el rendimiento. Además, siempre que podamos, deberemos usar los eventos extendidos y familiarizarnos con ellos ya que al SQL Server Profiler no le queda mucho entre nosotros.

Jul

2024

Propiedades Extendidas en SQL Server

Hace unos días, en nuestro canal de telegram surgió un tema interesante sobre documentación de tablas y sus columnas en SQL Server. Una de las formas más sencillas para documentar nuestros objetos en SQL Server de forma nativa (sin recurrir a aplicaciones de terceros) es el uso de las propiedades extendidas. Personalmente pienso que, si no tienes una herramienta de documentación externa, las propiedades extendidas son una alternativa indispensable para cualquier DBA o trabajador de datos. En este artículo, veremos qué son las propiedades extendidas, sus usos principales, y cómo podemos mejorar la gestión y documentación de nuestros entornos de SQL Server.

¿Qué son las Propiedades Extendidas en SQL Server?

En resumidas cuentas, las propiedades extendidas son metadatos adicionales que podemos asociar a objetos dentro de SQL Server. Estas propiedades, presentes en SQL Server desde SQL 2008, son unas etiquetas clave-valor que nos van a permitir almacenar información extra, como descripciones, anotaciones y cualquier dato relevante que consideremos útil para la administración y documentación de nuestra base de datos. No te creas que esto es todo, tú en tu escenario le vas a poder dar cualquier uso que te imagines y que sea compatible con una etiqueta clave-valor.

Usos Comunes de las Propiedades Extendidas

Como ya hemos adelantado, las propiedades extendidas tienen tantos usos como tu creatividad logre imaginar. Os voy a contar los principales con los que yo me he encontrado a lo largo de estos años.

Documentación de Objetos: Podemos usar las propiedades extendidas para la documentación de tablas, columnas, procedimientos almacenados, funciones y otros objetos de manera que esta tarea se vuelva más manejable y, sobre todo, la información se almacene en la propia base de datos.
Auditoría y Cumplimiento: De la misma manera, se pueden utilizar las propiedades extendidas para almacenar información de auditoría y cumplir con regulaciones específicas como etiquetado de datos confidenciales o sensibles.
Metadatos Personalizados: Más de lo mismo, podemos usar propiedades extendidas para agregar metadatos específicos de nuestra organización y que de otra manera no estarían cubiertos por los atributos estándar de SQL Server.

Documentando objetos en SQL Server con Propiedades Extendidas

Como hemos visto, uno de los usos más comunes y útiles de las propiedades extendidas es la documentación de objetos. Esta capacidad no solo nos facilita el mantenimiento, sino que también mejora la colaboración entre equipos de desarrollo y los administradores de las bases de datos. Al tener documentado quien creó una tabla o que proceso o persona se encarga de alimentar los datos, por ejemplo, los DBAs vamos a tener más fácil encontrar a las personas afectadas ante una incidencia. O, si sabemos quien desarrolló ese procedimiento que va lento, vamos a poder tomar medidas correctivas (siempre respetando los derechos humanos, recordad).

Documentación de Tablas y Columnas

Podemos agregar descripciones detalladas a nuestras tablas y columnas para aclarar su propósito y uso. Por ejemplo, podemos describir la función de una columna específica, lo que es particularmente útil en bases de datos grandes y complejas.

Ejemplo Práctico

Supongamos que tenemos una tabla Clientes y queremos documentar su columna EmailAlternativo. Podemos hacerlo con el siguiente script:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Correo electrónico del cliente para contactar en caso de que el principal devuelva un error', 
    @level0type = N'SCHEMA', @level0name = 'dbo', 
    @level1type = N'TABLE',  @level1name = 'Clientes', 
    @level2type = N'COLUMN', @level2name = 'EmailAlternativo';

Con este script, hemos agregado una propiedad extendida a la columna EmailAlternativo de la tabla Clientes en el esquema dbo, describiendo que almacena el correo electrónico del cliente alternativo. Como siempre pasa, cuanto más descriptivas sean las descripciones más fácil será entenderlas.

Documentación de Vistas, Procedimientos Almacenados y Funciones

Las vistas, procedimientos almacenados y funciones también pueden beneficiarse de las propiedades extendidas. Podemos documentar su propósito, parámetros y cualquier otra información relevante que facilite su uso y mantenimiento.

Ejemplo Práctico

Consideremos un procedimiento almacenado llamado usp_ObtenerClientesActivos. Podemos documentar su propósito con el siguiente script:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Obtiene una lista de todos los clientes activos en el sistema', 
    @level0type = N'SCHEMA', @level0name = 'dbo', 
    @level1type = N'PROCEDURE', @level1name = 'usp_ObtenerClientesActivos';
EXEC sp_addextendedproperty 
    @name = N'MS_Author', 
    @value = N'Ataulfo Contreras’, 
    @level0type = N'SCHEMA', @level0name = 'dbo', 
    @level1type = N'PROCEDURE', @level1name = 'usp_ObtenerClientesActivos';
En este caso, el script añade una descripción clara y concisa sobre el propósito del procedimiento usp_ObtenerClientesActivos. Después, un segundo script, añade el autor del procedimiento a otra propiedad extendida.

Gestión de Propiedades Extendidas

La forma sencilla de gestionar las propiedades extendidas es a través del entorno gráfico, con un clic derecho sobre el objeto al que deseemos añadir una propiedad extendida podremos entrar en la ventana de propiedades y ahí encontraremos el apartado para las propiedades extendidas. Sin embargo esto no es rápido cuando tienes que ir columna a columna de una tabla documentando sus atributos. Para ello podremos usar los procedimientos almacenados de sistema.

Crear Propiedades Extendidas

Para añadir una propiedad extendida, utilizaremos el procedimiento almacenado sp_addextendedproperty. Este procedimiento nos permite especificar el nombre y el valor de la propiedad, así como el objeto al que se va a asociar.

Modificación de Propiedades Extendidas

Si necesitamos actualizar una propiedad extendida existente, utilizaremos el procedimiento sp_updateextendedproperty. Este procedimiento nos permite cambiar el valor de una propiedad extendida sin necesidad de eliminarla y volver a crearla.

Eliminación de Propiedades Extendidas

Para eliminar una propiedad extendida, utilizaremos el procedimiento sp_dropextendedproperty. Este procedimiento elimina completamente la propiedad especificada del objeto.

Consultar las Propiedades Extendidas existentes

La finalidad de las propiedades extendidas siempre va a ser poder consultarlas, si no para qué íbamos a estar haciendo todo este trabajo, ¿verdad?. En este sentido Microsoft pone a nuestra disposición la función sys.fn_listextendedproperty pero su uso está muy limitado y a mi no me gusta. Existen otras herramientas externas capaces de usar las propiedades extendidas para la generación automática de documentación, como SQL Server Data Tools (SSDT) y otros sistemas de documentación de bases de datos. Estas herramientas pueden extraer las propiedades extendidas y generar documentación completa y detallada de la base de datos. Sin embargo, como el objetivo de este post es no salirnos de SQL y cómo sabéis que yo soy partidario de usar las tablas y vistas de sistema, en este caso no iba a ser menos. Os dejo el script que yo uso:

SELECT
CASE 
   WHEN indexes.name IS NOT NULL AND Child.type_desc = 'USER_TABLE' THEN 'TABLE_INDEX'
   WHEN indexes.name IS NOT NULL AND Child.type_desc = 'VIEW' THEN 'VIEW_INDEX'
   WHEN extended_properties.minor_id <> 0 AND Child.type_desc = 'USER_TABLE' THEN 'TABLE_COLUMN'
   WHEN extended_properties.minor_id <> 0 AND Child.type_desc = 'VIEW' THEN 'VIEW_COLUMN'
   ELSE Child.type_desc 
END AS Object_Type,
extended_properties.name AS Extended_Property_Name,
CAST(extended_properties.value AS NVARCHAR(MAX)) AS Extended_Property_Value,
schemas.name AS Schema_Name,
Child.name AS Object_Name,
Parent.name AS Parent_Object_Name,
columns.name AS Parent_Column_Name,
indexes.name AS Index_Name
FROM sys.extended_properties
INNER JOIN sys.objects Child
   ON extended_properties.major_id = Child.object_id
INNER JOIN sys.schemas
   ON schemas.schema_id = Child.schema_id
LEFT JOIN sys.objects Parent
   ON Parent.object_id = Child.parent_object_id
LEFT JOIN sys.columns
   ON Child.object_id = columns.object_id
   AND extended_properties.minor_id = columns.column_id
   AND extended_properties.class_desc = 'OBJECT_OR_COLUMN'
   AND extended_properties.minor_id <> 0
LEFT JOIN sys.indexes
   ON Child.object_id = indexes.object_id
   AND extended_properties.minor_id = indexes.index_id
   AND extended_properties.class_desc = 'INDEX'
ORDER BY Child.type_desc ASC;

Conclusión

Las propiedades extendidas en SQL Server son una opción muy útil y válida para la documentación de nuestras bases de datos. Nos permiten añadir metadatos personalizados a nuestros objetos, mejorando la claridad, la colaboración y el cumplimiento normativo. Ya sea que estemos documentando tablas, columnas, procedimientos almacenados, funciones o cualquier otro objeto, las propiedades extendidas nos facilitan una gestión más eficiente y eficaz de nuestros datos en SQL Server. Además, la capacidad de automatizar la adición y gestión de propiedades extendidas mediante scripts de SQL amplía aún más sus aplicaciones, permitiéndonos mantener nuestras bases de datos bien documentadas y organizadas sin un esfuerzo manual excesivo. Aprovechando estas características, podemos transformar nuestras bases de datos en repositorios de información clara y útil, accesibles para todos los miembros de nuestro equipo y para las aplicaciones que interactúan con ellas.

En resumen, no solo conocer sino dominar el uso de las propiedades extendidas es una habilidad importante para cualquiera que busque optimizar la gestión y documentación de sus bases de datos en SQL Server. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Jul

2024

DBA vs IA: Fight! La prueba de si la IA te va a quitar el trabajo

Los avances en inteligencia artificial son imparables, eso está claro. Además está claro que son la tendencia en el mercado, parece que términos como big data, cloud o blockchain son cosa del pasado y ahora lo moderno es tener IA hasta en el inodoro. Todas las empresas compiten por añadir IA a sus productos a la par que apuestan por integrar asistentes como ChatGPT o Copilot a sus procesos para facilitar la vida de los trabajadores. Y ojo que a mi me parece muy bien el avance de la IA pero no debemos olvidarnos de nuestra propia inteligencia natural. Por lo menos hasta que esto avance un poco más y vivamos en un mundo donde trabajen robots con IA y nosotros podamos dedicarnos a la holganza más absoluta. No me enrollo más, que lo que os vengo a contar hoy es muy interesante, una prueba más de cómo está ahora la IA en el ámbito de la administración de base de datos. Que cada uno saque sus conclusiones de si puede o no abandonarse a la vida contemplativa gracias a que una máquina complete sus tareas.

Definiendo la prueba

Os pongo en situación, me encontraba yo desempeñando mis labores diarias, por las que me pagan no estas tareas del blog que lo hago por amor al arte. En ese momento estaba empezando a tomar el control de una nueva instancia de SQL, en concreto una instancia administrada en Azure. Se trata de una instancia compartida por varios microservicios donde cada uno de ellos tiene su base de datos, en principio, totalmente independiente a las demás. Por lo que he podido ver en el resumen de consumo de recursos del portal web de Azure, el uso de CPU es elevado en esta instancia y, por desgracia, ahí se queda todo lo que puedes ver en esta ventana. Igual que cuando miras el activity monitor de un SQL en local la verdad.

Como es un servidor compartido y hay varias bases de datos debo priorizar por lo que lo lógico es empezar por la que mayor consumo hace de los recursos. Partimos de una gran ventaja, al ser Azure Query Store está habilitado por defecto en todas las bases de datos recolectando datos sin que yo haya tenido que decírselo pero, esta es una solución que muestra datos a nivel base de datos y no de instancia. No me sirve de primeras. Necesito otra solución y me parece una buena oportunidad de poner a prueba a la IA antes de tirar de mi navaja suiza de scripts. Como estamos hablando de Azure vamos a hacer que todo quede en casa y preguntar a Copilot.

Será Copilot un buen DBA, pongámoslo a prueba

Os dejo con lo que estáis todos esperando, la conversación íntegra con Copilot. Luego al final os daré mis conclusiones.

Round 1

Roberto:

Tengo una instancia administrada de sql en azure con una docena de bases de datos. Necesito saber cual de las bases de datos tiene un mayor consumo de recursos. ¿Cómo puedo hacerlo?

Copilot:

Para monitorear el consumo de recursos de las bases de datos en una instancia administrada de SQL en Azure, puedes utilizar el servicio de Azure SQL Analytics en Azure Monitor. Aquí te dejo los pasos generales para hacerlo:
Habilita la supervisión de diagnóstico para tu instancia administrada de SQL Server en Azure. Esto se hace en la sección de “Diagnóstico” en la configuración de tu instancia en el portal de Azure.

Configura la exportación de registros a un espacio de trabajo de Log Analytics. Durante la configuración del diagnóstico, puedes elegir enviar los datos a un espacio de trabajo de Log Analytics existente o crear uno nuevo.

Instala la solución de Azure SQL Analytics desde la galería de soluciones en tu espacio de trabajo de Log Analytics.

Una vez que la solución esté instalada y los datos comiencen a fluir, puedes ver los paneles de Azure SQL Analytics para obtener detalles sobre el consumo de recursos. Hay un panel específico llamado “SQL Managed Instance Resource Usage” que proporciona detalles sobre el uso de recursos a nivel de base de datos.

Recuerda que puede llevar algún tiempo hasta que los datos estén disponibles en los paneles después de habilitar la supervisión de diagnóstico.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 2

Roberto:

¿Hay manera de hacerlo desde las herramientas nativas de SQL en SSMS?

Copilot

Sí, puedes usar las herramientas nativas de SQL Server Management Studio (SSMS) para monitorear el consumo de recursos de las bases de datos en una instancia administrada de SQL en Azure. Aquí te dejo los pasos generales para hacerlo:

Conéctate a tu instancia administrada de SQL Server en Azure usando SSMS.

Abre una nueva consulta en la base de datos que deseas monitorear.

Ejecuta una consulta de DMV (Dynamic Management View) para obtener información sobre el consumo de recursos. Por ejemplo, puedes usar la vista sys.dm_db_resource_stats para obtener estadísticas de uso de recursos a nivel de base de datos. Aquí tienes un ejemplo de cómo podría ser esta consulta:

SELECT

    database_name,

    avg_cpu_percent,

    avg_data_io_percent,

    avg_log_write_percent

FROM

    sys.dm_db_resource_stats

ORDER BY

    avg_cpu_percent DESC;

Esta consulta te dará el porcentaje promedio de CPU, I/O de datos y escritura de log utilizados por cada base de datos, ordenados por el uso de CPU.

Por favor, ten en cuenta que estas vistas de gestión dinámica solo mantienen datos de las últimas 2 horas, por lo que si necesitas un seguimiento a largo plazo, te recomendaría utilizar Azure SQL Analytics como mencioné anteriormente.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 3

Roberto:

Esa DMV no tiene database_name

Copilot:

Tienes razón, lamento el error. La vista de gestión dinámica sys.dm_db_resource_stats no incluye el nombre de la base de datos. En su lugar, puedes usar la vista sys.resource_stats para obtener estadísticas de uso de recursos a nivel de base de datos en una instancia administrada de SQL Server en Azure. Aquí tienes un ejemplo de cómo podría ser esta consulta:

SELECT

    database_name,

    avg_cpu_percent,

    avg_data_io_percent,

    avg_log_write_percent

FROM

    sys.resource_stats

ORDER BY

    avg_cpu_percent DESC;

Esta consulta te dará el porcentaje promedio de CPU, I/O de datos y escritura de log utilizados por cada base de datos, ordenados por el uso de CPU.

Por favor, ten en cuenta que sys.resource_stats solo mantiene datos de las últimas 14 días.

Espero que esto te ayude a identificar la base de datos que consume más recursos. Si tienes más preguntas, no dudes en preguntar. 😊

Round 4

Roberto:

El objeto sys.resource_stats al que haces referencia no existe ni en Azure MI ni en SQL Server

Copilot:

Mis disculpas por el error anterior. En las instancias administradas de Azure SQL, no se dispone directamente de una vista de gestión dinámica (DMV) que proporcione el uso de recursos a nivel de base de datos.

Sin embargo, puedes obtener información sobre el uso de recursos a nivel de instancia utilizando la DMV sys.dm_instance_resource_governor_workloads. Aunque esta DMV no desglosa el uso de recursos por base de datos, puede darte una idea del uso total de recursos de tu instancia administrada.

Si necesitas un desglose del uso de recursos a nivel de base de datos, te recomendaría considerar el uso de Azure SQL Insights, que es una característica de Azure Monitor. SQL Insights proporciona una visión más detallada del rendimiento y el uso de recursos a nivel de base de datos.

Lamento cualquier confusión causada y agradezco tu paciencia. Si tienes más preguntas o necesitas más ayuda, no dudes en preguntar. 😊

Round 5: KO

Roberto:

Yo creo que sí que se puede hacer usando esta consulta, ¿es cierto?

SELECT

        [Database],

        CONVERT (DECIMAL (6, 3), [CPUTimeInMiliSeconds] * 1.0 /

        SUM ([CPUTimeInMiliSeconds]) OVER () * 100.0) AS [CPUTimeAsPercentage]

     FROM

      (SELECT

          dm_execplanattr.DatabaseID,

          DB_Name(dm_execplanattr.DatabaseID) AS [Database],

          SUM (dm_execquerystats.total_worker_time) AS CPUTimeInMiliSeconds

       FROM sys.dm_exec_query_stats dm_execquerystats

       CROSS APPLY

        (SELECT

            CONVERT (INT, value) AS [DatabaseID]

         FROM sys.dm_exec_plan_attributes(dm_execquerystats.plan_handle)

         WHERE attribute = N’dbid’

        ) dm_execplanattr

       GROUP BY dm_execplanattr.DatabaseID

      ) AS CPUPerDb

Copilot:

Sí, tienes razón. La consulta que has proporcionado utiliza las vistas de gestión dinámica sys.dm_exec_query_stats y sys.dm_exec_plan_attributes para obtener el tiempo total de CPU utilizado por cada consulta, agrupado por base de datos.

La parte interna de la consulta suma el tiempo de CPU para cada base de datos. La parte externa de la consulta convierte ese tiempo en un porcentaje del tiempo total de CPU.

Por lo tanto, esta consulta te dará una idea de qué bases de datos están consumiendo más tiempo de CPU en tu instancia administrada de SQL Server en Azure.

Gracias por compartir esta consulta, es un buen ejemplo de cómo se pueden utilizar las vistas de gestión dinámica para obtener información detallada sobre el uso de recursos. 😊

Conclusiones de esta prueba

Tontuna natural 1 – Inteligencia Artificial 0.

Y digo tontuna natural porque he perdido mi tiempo hablando con una máquina cuando ya tenía la respuesta antes de empezar. Y peor aún, he hecho el intento de decirle como lo tiene que hacer como si estos modelos se alimentasen del feedback de los usuarios. Pero bueno, quedémonos con que por lo menos, la prueba me ha servido para llenar un artículo y entreteneros con esta lectura ligera.

Analizando los resultados lo que saco en claro es que Copilot me ha dado más caritas sonrientes que respuestas correctas y, al menos esta es mi impresión, esta es la tónica habitual en cuanto las preguntas se especializan y requieren de experiencia profesional. Y no solo lo digo yo, hablando con más gente el otro día todos comentaban como en fórmulas DAX por ejemplo, Copilot falla más que acierta cuando pasas de preguntas básicas y le exiges un poco. La IA es un gran aliado en tareas repetitivas o sencillas y yo la uso mucho pero siempre hay que tener claras sus limitaciones y supervisar todo lo que nos dice. No estamos aún en situación de poner en producción código generado por IA sin mirarlo y retocarlo.

Jul

2024

SQL vs DAX ¿Quién es más rápido?

El pasado viernes andaba yo absorto en la masterclass de Salvador Ramos en Nasmasdata cuando, de repente, una diapositiva salvaje apareció. Salva nos hablaba de un cálculo de stock acumulado con su fórmula en DAX porque según ponía en SQL era un cálculo imposible. ¿IMPOSIBLE? Eso era un reto para mí, así que, raudo tomé una captura de pantalla y acepté ese reto. No hubo que esperar mucho, hoy sábado, de buena mañana me he sentado delante del ordenador, he creado una tabla con los datos necesarios para la prueba y me he puesto manos a la obra. No sin antes contactar a Salva e informarle de lo que estaba aconteciendo porque sí amigos, Salva sabe de la existencia de este artículo días antes que vosotros, para la próxima que el reto venga de vosotros y estaréis informados antes.

Entendiendo el reto en DAX

Lo primero que tenemos que entender es lo que nos propone el reto, la medida DAX que se ve en la imagen es esta:

Stock Acumulado =
CALCULATE(
    [Stock Final],
    FILTER(
        ALL(Fecha),
        Fecha[Fecha] <= MAX(Fecha[Fecha])
    )
)

Por un lado CALCULATE es una función que cambia una expresión en un contexto de filtro modificado. Como parámetros le hemos pasado [Stock Final] que es una medida o una calculada que nos sumariza los movimientos de stock. Para el segundo parámetro hemos pasado la función FILTER con dos parámetros más, el resultado es que filtra la tabla Fecha para incluir solo las filas donde la fecha es menor o igual a la fecha máxima en el contexto actual.

Convirtiendo el DAX a T-SQL

Ahora que ya sabemos lo que tenemos que obtener vamos a ver como debemos hacerlo en SQL Server, a ver si realmente era imposible o no. He de deciros que mi tabla solo tiene dos columnas fecha y stock. Podriamos haber complicado el escenario añadiendo artículos pero para la demo nos vale sin eso, supongamos que tenemos solo uno. Como luego vamos a llevarnos estas tablas a Power BI tal como están en SQL no hay problema, en DAX vamos a jugar en las mismas condiciones.

Para empezar vamos a tener que totalizar por dias para saber cuantos movimientos hemos tenido ese día. Luego tenemos que hacer una suma de los valores desde la primera fecha hasta la fecha actual. Eso lo podremos lograr con una función de ventana. Esta sería la consulta imposible:

SELECT 
    Fecha,
    SUM(Stock_Final) OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as Stock_Acumulado
FROM 
    ( SELECT Fecha, sum(Stock_Final) as Stock_Final FROM stock_demo GROUP BY fecha ) f
WHERE 
    Fecha <= (SELECT MAX(Fecha) FROM stock_demo)
ORDER BY 
    Fecha

Vamos a verla paso a paso. En lo primero que nos tenemos que fijar es en la subconsulta del FROM. Es justo lo que comentábamos antes, la suma de los movimientos de stock agrupados por día. Luego, en la consulta principal, estamos utilizando una función de ventana para calcular el Stock_Acumulado. Esta función de ventana SUM() OVER (ORDER BY Fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) suma los valores de Stock_Final en todas las filas desde el inicio de la tabla (UNBOUNDED PRECEDING) hasta la fila actual (CURRENT ROW), ordenadas por Fecha. Esto da como resultado un total acumulativo de Stock_Final hasta la fecha actual.

Ya lo tenemos, no era imposible. Sin embargo, a lo que Salva se refería era los inconvenientes de realizar los cálculos en SQL por tiempos. Y realmente este es un pensamiento muy extendido en el sector. DAX es más rápido realizando cálculos que SQL Server. Pero, ¿realmente es cierto? Vamos a verlo.

Comparativas de rendimiento DAX vs SQL

Para que los resultados puedan ser representativos, vamos a hacer las pruebas sobre una tabla con algo más de 1 millón de registros, algo normal en una empresa pequeña. Vamos a analizar dos años de datos de movimientos de stock. Para medir los tiempos de la consulta DAX he sacado la consulta de la tabla y la he llevado a DAX Studio. En SQL vamos a usar las estadísticas de tiempo que podemos sacar con SET STATISTICS TIME ON.

Como vemos los resultados son demoledores y para nada lo esperado. Analizando 1.010.000 registros repartidos en 1096 fechas distintas SQL Server se ha demorado 85 milisegundos de los cuales 78 ms han sido CPU. En DAX Studio podemos ver que la fórmula DAX para la tabla ha tardado 180 milisegundos, repartidos entre 175 ms del motor de fórmulas (FE) y 5 ms del motor de almacenamiento (SE).

Complicando el escenario

Vamos a pasar de 1 millón a 15 millones de registros a ver si los resultados son distintos. Todos sabemos que el fuerte de Power BI reside en su motor Vertipaq y su gran capacidad de trabajar con grandes cantidades de datos.

Ahora sí que vemos la gran diferencia de tiempos de la que todo el mundo habla en DAX y Power BI. Con 15 millones de registros los tiempos en SQL Server se han disparado a 1219 milisegundos de CPU aunque paralelizando la consulta se ha resulto en 186 ms. En DAX tenemos un tiempo total de 193 milisegundos repartidos en 175 ms del motor de fórmulas (FE) y 18 ms del motor de almacenamiento (SE). Aunque los tiempos de DAX son mayores a los de SQL no hay tanta diferencia y teniendo en cuenta el paralelismo de SQL podemos deducir un mayor consumo de recursos si ejecutamos los cálculos en origen.

Mejorando los tiempos de SQL

Ya sabemos el objetivo a batir en DAX 193 ms. Veamos qué podemos hacer en SQL. Para empezar vamos a probar con un índice columnar sobre la tabla, al fin y al cabo los índices columnares son la misma tecnología que el motor vertipaq de Power BI.

Ahí lo tenemos, ahora sí. Nuestra consulta sobre la tabla con índices columnares ha tardado 155 milisegundos de CPU y solo 56 ms totales al paralelizar. Aun así esto todavía se puede mejorar, ¿recordáis las vistas indexadas? Vamos a probarlo.

CREATE VIEW Stock_Final_View
WITH SCHEMABINDING
AS
SELECT 
      Fecha, 
      COUNT_BIG(*) as row_count, 
      SUM(ISNULL(Stock_Final, 0)) as Stock_Final 
FROM 
      dbo.stock_demo_extra_large_Columnar 
GROUP BY 
      fecha;
GO
CREATE UNIQUE CLUSTERED INDEX IDX_Stock_Final_View
ON Stock_Final_View (Fecha);

¿Qué os parece el resultado? ¿Os lo esperabais? Con la combinación de índice columnar más vista indexada hemos podido ejecutar la consulta pesada sobre 15 millones de registros en menos de un milisegundo. A mi me ha sorprendido la verdad. No esperaba tanta mejoría.

Es importante destacar que esto es una prueba de concepto en un entorno de laboratorio sin carga de trabajo donde puedo hacer todos los cambios que quiero sobre el SQL sin afectar a ningún otro proceso. Esto es un escenario muy simplificado para la DEMO sin ningún parecido con una base de datos de un ERP de producción. Como siempre digo, primero válida en tus servidores de pruebas y luego, si ves que va bien y no da problemas, piensa en aplicarlo en producción.

Conclusión

SQL sigue más vivo que nunca. Es verdad que DAX es muy potente y seguro que tiene un montón más de optimizaciones de las que yo he sido capaz de aplicar. Os diré que para las pruebas estaba la inteligencia de tiempos desactivada y como dimensión de tiempos estaba usando la plantilla que Salva proporciona gratuitamente en su web que entiendo que es la que usa él.

PD.: Salva, no me importa el resultado de estas pruebas, tu sigues siendo un referente para mi 🙂.

cloud

La SQLCon llega a España

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes