Cloud SQL en Google Cloud Platform

Recientemente he estado considerando la opción de Cloud SQL para SQL Server ofrecida por Google Cloud Platform (GCP). Esta es la plataforma de servicios en la nube ofrecida por el gigante Google. En terminos de servicios cloud, parece que aún no está al nivel de Amazon Web Services (AWS) o Microsoft Azure. Sin embargo, el crecimiento de GCP en los último años dos años ha sido significativo. Continue reading

SQL101: Full Recovery Model

Siguiendo el atículo anterior sobre fundamentos de SQL Server: SQL101: Simple Recovery Model, en este vamos a tratar sobre el modelo más extendido, Full Recovery Model.

Empecemos haciendo un repaso de las principales características de este modelo de recuperación:

  1. Requiere hacer backups del Log de Transacciones.
  2. No hay perdida de datos. Esto es cierto mientras el tail log backup está disponible, en otro caso, los cambios posteriores al último backup del log de transacciones necesitan ser repetidos.
  3. Se puede hacer una recuperación de la base de datos al instante deseado, normalmente antes del error de aplicación o usuario que motiva la restauración. Dicho de otro modo, permite restauraciones Point-in-time.
  4. Permite configuraciones de Alta Disponibilidad como AlwaysOn Availability Groups y Database Mirroring. También habilita el uso de Log-Shipping, Change Data Capture,… en resumen, cualquier característica que se apoye en el uso del Transaction Log.

De lo citado anteriormente, lo más importante del Full Recovery Model es que nos posibilita el no tener pérdida de datos en caso de un desastre. Pero además de tener esta opción en nuestra base de datos, hace falta establecer un mecanismo automático de copias de seguridad que haga uso de los diferentes tipos de backups: Full, Differential (no es obligatorio pero sí recomendable), y Log.

Una estrategia sencilla de copias de seguridad puede ser la siguiente:

  • Full backup: Cada semana.
  • Differential backup: Cada día.
  • Log backup: Cada N minutos.

De esta forma, en el caso más extremo, como máximo se perderían las acciones recientes entre el último Log backup y el momento del desastre. Si ocurrido el desastre, aún podemos acceder a la instancia de SQL Server y podemos hacer un Tail Log backup, no habrá perdida de datos alguna. Veamos un esquema a continuación:

bnrr-rmfull1-db-failure-pt

Ejemplo de escenario de desastre y pasos a realizar para evitar pérdida de datos usando Full Recovery Model. Fuente

Imaginemos una aplicación o usuario que realiza una operación  como borrar/modificar por error millones de registros de una tabla crítica para el negocio. Esto puede significar un desastre para muchas empresas. Pero no nos alarmemos, tiene solución. Se puede restaurar la Base de Datos al momento anterior a que eso ocurriese, tan sólo hace falta la intervención de un operador experto (DBA) de SQL Server, y usando los backups existentes, recuperará el sistema al punto deseado, permitiendo que la aplicación vuelva a funcionar y aseguando la continuidad del negocio.

Ahora puede surgirnos una pregunta… ¿Qué debemos hacer para tener una base de datos en Full Recovery Model?

  1. Modificar la opción a nivel de la base de datos.
    USE master;
    GO
    
    ALTER DATABASE [NombreDeTuDB]
    	SET RECOVERY FULL;
    
  2. Realizar un Full backup inicial.
    USE master;
    GO
    
    BACKUP DATABASE [NombreDeTuDB]
    	TO DISK = '...\ruta\fullBackup_fileName.bak'
            WITH --...(opciones deseadas: compresion, encriptacion, etc);
    
    
  3. Configurar una estrategia de backups (Full y Logs como mínimo). Ejemplo un job de SQL Agent, que realice las copias de seguridad según la estrategia que definamos.
    • Los backups deberían estar al menos en una ubicación diferente a la máquina que ejecuta la instancia de la base de datos.

El punto 2) de esta lista es fundamental, esa copia inicial es la que inicia la cadena de backups. Sobre el punto 3) se recomienda en una ubicación diferente a la máquina de de la base de datos, por motivos de seguridad en caso de problemas que nos impidan acceder a ese servidor (inundación, fuego, virus informático, etc), además no tener una sola copia sino varias y en diferentes localizaciones.

Llegados a este punto, podemos preguntarnos qué desventaja tiene el Full Recovery Model. La mayor obligación que implica es la de tener que gestionar el Transaction Log (y sus VLFs) y hacer los Transacion Log Backups, pero siendo una tarea típicamente automatizada no debería suponer apenas esfuerzo. Otro inconveniente que podemos observar, es que hay ciertas operaciones que pueden hacer un uso muy intensivo del Transaction Log, como una carga masiva de datos desde un fichero, o una recostrucción de índices en una tabla muy grande. En esos casos, el Transaction Log debe tener el tamaño necesario para contener el detalle de estas operaciones. Otra opción posible es el uso temporal, mientras dure la operación, del tercer y último modelo de recuperación: Bulk Logged. Pero sobre esto hablaremos en un próximo artículo de fundamentos de SQL Server.

Espero que el artículo haya resultado de ayuda, como repaso de lo ya sabido o como primera toma de contacto. Como siempre…

¡Hasta la próxima!

Creando nuestro laboratorio de Elasticsearch con Hyper-V

Para crear nuestro laboratorio de Elasticsearch en un equipo Windows, con el que poder experimentar más adelante 😊, una buena opción es usando Hyper-V. Este es un software de virtualización que ofrece Microsoft y que se puede instalar en varias versiones de Windows 10 de forma gratuita.

Objetivo

Vamos a crear un cluster con las siguientes características:

  • Nombre del cluster: es-cluster-lab
  • Número de nodos: 3
  • Nombre de los nodos: elasticsearch-lab-{1..3}.localdomain

Para cada nodo/VM:

  • CPU: 1 core
  • RAM: 1024 MB
  • VHD: 20 GB
  • OS: Linux CentOS 7
  • SW: OpenJDK 1.8 + Elasticsearch 6.7

Preparación

Lo primero es comprobar que nuestro equipo cumple con los requisitos para Hyper-V:

  1. Descargar la imagen de la distribución de Linux deseada, para nuestro ejemplo: Centos 7. Lo usaremos más adelante
  2. Abrir la consola de Powershell (PS) en modo administrador.
  3. Ejecuta systeminfo.exe
  4. En el resultado, comprueba que en la sección Hyper-V Requirements, se cumplen todos.

[Aviso: Habilitando Hyper-V causará que otras herramientas de virtualización instaladas dejen de funcionar. Puedes seguir este artículo para añadir una opción de iniciar Windows sin Hyper-V habilitado.]

La instalación usando PS la hacemos con una línea de comando:

> Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Hyper-V -All

Luego reiniciamos nuestra máquina y estaremos listos.

Creando las VMs

Tras el reinicio de nuestra máquina, si vamos al Inicio de Windows y buscamos por “Hyper”, veremos que hay una aplicación nueva: Hyper-V Manager.

Hyper-V Manager

Para crear los nodos de nuestro cluster, necesitaremos definir un Virtual Switch, que podemos crear usando Hyper-V Manager o PS (más cómodo):

> New-VMSwitch -name MyVirtualSwitch -NetAdapterName Ethernet -AllowManagementOS $true

Con la preparación completada, podemos pedirle a Hyper-V que cree nuestra primera VM, y se lo diremos por medio de PowerShell. El siguiente es un bloque de código que editamos usando Powershell ISE:

# Definimos el nombre de la VM, el Switch (creado antes) y la ruta de la imagen
$VMName = 'elasticsearch-lab-1'
$Switch = 'MyVirtualSwitch'
$InstallMedia = 'D:\ISOs\CentOS-7-x86_64-Everything-1804.iso'

# Creamos la VM, 20GB
New-VM -Name $VMName -MemoryStartupBytes 1073741824 -Generation 1 -NewVHDPath "D:\VMs_slow\$VMName\$VMName.vhdx" -NewVHDSizeBytes 21474836480 -Path "D:\Virtual Machines\$VMName" -SwitchName $Switch

# Añadimos una unidad DVD a la VM..
Add-VMScsiController -VMName $VMName
Add-VMDvdDrive -VMName $VMName -Path $InstallMedia

# ..y la montamos
$DVDDrive = Get-VMDvdDrive -VMName $VMName

# Configura la VM para arrancar desde el CD/DVD
Set-VMBios -VMName $VMName -StartupOrder @("CD", "Floppy", "LegacyNetworkAdapter", "IDE")

Si abrimos Hyper-V Manager y listamos las Virtual Machines, veremos la nuestra:Hyper-V Manager 2
Click derecho sobre la VM y seleccionamos Start, tras unos segundos se iniciará el asistente de instalación de CentOS:

Picture3

No vamos a entrar en detalle de cómo realizar esta instalación, al existir una buena cantidad de guías en castellano en la web. Dónde si vamos a entrar más en detalle es en la instalación de nuestro cluster de Elasticsearch.

Repetimos este paso tantas veces cómo nodos queramos en nuestro cluster, tan sólo cambiando el nombre de la VM (…-1, …-2,…) en nuestro script de PS.

Creando el cluster de Elasticsearch

JAVA es un prerrequisito antes de instalar Elasticsearch, ya que su motor de búsqueda es Apache Lucene que está creado en dicho lenguaje. Sólo podemos usar el que provee Oracle o la versión OpenJDK. En nuestro ejemplo usamos el segundo.

Vamos a la consola del primer nodo con permisos elevados:

$ yum install java-1.8.0-openjdk-devel

Tras esto procedemos a instalar propiamente elastic:

$ yum install elasticsearch

E iniciamos el servicio:

$ systemctl start elasticsearch.service

Editamos algunas propiedades en el fichero de configuración de nuestro nodo:

$ vi /etc/elasticsearch/elasticsearch.yml

Cambiamos los valores de:

  • cluster.name, le damos el nombre que queramos.
  • node.name, le ponemos igual que el nombre de la VM.
  • network.host, la IP (v. 4) de la máquina virtual.
  • discovery.zen.ping.unicast.hosts: Le ponemos la lista de las IPs que van a formar el cluster. Ejemplo: [“192.168.1.72″,”192.168.1.73″,”192.168.1.38”]. Nota: Esta propiedad la usamos porque estamos en versión 6.8. A partir de la 7 se recomienda usar discovery.seed_hosts.

Repetimos todo lo anterior para los demás nodos. Cuando hayamos terminado, el cluster se debería haber formado de manera transparente, ya que hemos usado el mismo cluster.name, y los nodos son visibles entre sí. Dicho de otro modo, los nodos de elasticsearch detectan si existe un cluster que coincida con su configuración (archivo elasticsearch.yml) y se unen a él de manera automática. Podemos comprobarlo usando esta llamada:

$ curl -X GET "IP_DE_NODO:9200/_cat/nodes?v&pretty"

Ejemplo:

$ curl -X GET "192.168.1.38:9200/_cat/nodes?v&pretty"

Con resultado:

Picture4

Si sólo nos apareciera el nodo de la IP, tenemos que averiguar el porqué. Problemas comunes:

  1. Problemas con el archivo de configuración. Esto es fácil de ver.
  2. Problemas con el firewall.

Para ver si se trata de lo segundo, podemos comprobar que los nodos tengan comunicación entre sí:

$ nc -vz 192.168.1.73 9300

La IP es la del nodo con el que queremos comprobar la comunicación, y el puerto TCP 9300, es el que se utiliza por defecto para la comunicación entre los nodos. Si no estuviera abierta, hacemos lo siguiente:

$ firewall-cmd --permanent --add-port=9200/tcp
$ firewall-cmd --permanent --add-port=9300/tcp

Ahora, si volvemos a probar la llamada anterior. Deberíamos obtener como resultado la lista de los 3 nodos de nuestro cluster. Si no fuera el caso, puedes dejar un comentario o enviarme un mensaje privado.

Con esto ya tenemos configurado nuestro cluster de elasticsearch de 3 nodos con Hyper-V ¡Bien hecho! A partir de este punto podemos plantearnos próximos retos:

  • Automatizar completamente todo el proceso de creación del cluster. Vagrant?
  • Hacer pruebas de rendimiento con nuestro cluster.
  • Federarlo con otra base de datos.
  • Configurar los nodos de forma automática con Ansible.

Hay muchas opciones, y sin duda escribiré más artículos sobre ello ¡Hasta la próxima!

SQL101: Simple Recovery Model

Antes de entrar en detalle sobre los diferentes Modelos de Recuperación de bases de datos en SQL Server, repasemos qué es el Modelo de Recuperación.

recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available.” MSDN

“Un modelo de recuperación es una propiedad de la base de datos que controla cómo se registran las transacciones, si el registro de transacciones requiere (y permite) copia de seguridad, y qué tipos de operaciones de restauración están disponibles”.

Es decir, el recovery model determina el mantenimiento del registro de transacciones, y las opciones a la hora de hacer copias de seguridad, backups, y restaurar una base de datos.

Otro concepto importante a entender antes de continuar, es el del registro de transacciones, o transaction log. Toda base de datos SQL Server tiene un registro de transacciones que guarda todas las transacciones y las modificaciones hechas en la base de datos por cada una de las transacciones.

Ahora veamos qué Recovery Models tenemos en SQL Server:

  • Simple Recovery Model, en castellano, modo de recuperación simple.
  • Full Recovery Model, o modo de recuperación completa.
  • Bulk Logged Model, o modo de registro de operaciones masivas.

En este artículo, nos centramos en el primero, y a continuación vemos sus características más destacadas:

  1. Elimina la necesidad de gestionar el transaction log. Se reclama automáticamente el espacio del transaction log, para mantenerlo tan reducido como sea posibleSe simplifica parte del mantenimiento.
  2. Consecuencia de lo anterior, es que no es posible hacer copias de seguridad del transaction log.
  3. Tampoco es posible el uso de las siguientes funcionalidades, ya que se apoyan en el registro de transacciones:
    • Log Shipping, mecanismo de sincronización con otros servidores de SQL Server, típicamente usado como solución económica y sencilla para Disaster Recovery.
    • Opciones de alta disponibilidad, como DB Mirroring, o Always On.
    • Change Data Capture.
  4. No se pueden hacer recuperaciones de la base de datos en un punto determinado en el tiempo (PITR). Tampoco se pueden hacer recuperaciones sin perdida de datos.
Perdida de datos usando Simple Recovery Model

Ejemplo de escenario de desastre y perdida de datos posible usando Simple Recovery Model. Fuente

5. Permite hacer differentials backups. Estos backups solo contienen las modificaciones hechas en la base de datos desde el full backup más reciente. Son más rápidos y de menor tamaño que un full backup. Este tipo de backups también es posible en los otros modelos de recuperación.

Llegados a este punto, nos podemos preguntar ¿En qué casos podemos configurar una base de datos con Simple Recovery Model ? La respuesta es sencilla: aquellos casos en los que las desventajas no importen. Algunos casos típicos:

  • Bases de datos con contenido estático o semi estático, cuyo contenido cambia con muy poca frecuencia.
  • Cuando no hay objetivos de recuperación exigentes, siempre que sea aceptable una posible pérdida de datos si un desastre ocurriera.
  • Cuando no se requiera de alta disponibilidad. Esto es que haya otra instancia de la base de datos sincronizada en tiempo real.
  • Cuando la información es temporal y/o se pueda regenerar los datos facilmente.
  • Etc.

Para finalizar, espero que haya sido aclaratorio el artículo y que el lector tenga una mejor idea sobre esta opción para cualquier base de datos en SQL Server. En futuros articulos 101 de SQL Server, revisaremos los otros modelos de recuperación.

¡Hasta la próxima!

Query Store: Ejemplos prácticos

En mi primer artículo sobre Query Store, hace ya bastante tiempo, comenté que escribiría otro incluyendo ejemplos de configuraciones, monitorización y consumo de recursos. Bien, ese día ha llegado!

Código de ejemplo de configuración

USE master;
GO

ALTER DATABASE [NombreDeTuDB]
	SET QUERY_STORE = ON
	(
		OPERATION_MODE = READ_WRITE
		, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14)
		, DATA_FLUSH_INTERVAL_SECONDS = 600
		, INTERVAL_LENGTH_MINUTES = 10
		, MAX_STORAGE_SIZE_MB = 512
		, QUERY_CAPTURE_MODE = AUTO
		, SIZE_BASED_CLEANUP_MODE = AUTO
		, MAX_PLANS_PER_QUERY = 7
	);

Puedes ver que los parámetros se explican en el citado artículo. Como siempre, te recomiendo probar bien la configuración en entornos de pre-producción, ya que la carga de trabajo de tu Base de Datos influye de forma directa. Dicho esto, este ejemplo de configuración es funcional y no he tenido problemas con ello.

Código de ejemplo para monitorizar el uso de disco de Query Store

Este código lo vamos a desglosar por pasos:
1) Creamos una base de datos (opcional) y una nueva tabla para guardar los datos que vamos a monitorizar:

USE master;
GO

CREATE DATABASE DBA_Admin;
GO

USE DBA_Admin;
GO

CREATE TABLE dbo.QueryStoreSizeHistory (
	current_storage_size_mb	BIGINT NOT NULL
	, max_storage_size_mb	BIGINT NOT NULL
	, date_time				DATETIME2(2) DEFAULT(GETDATE()) NOT NULL
);

2) A continuación podemos crear un SQL Agent Job, que se lance cada N horas, y que  sólo va a ejecutar el siguiente código de T-SQL:

USE DBA_Admin;
GO
INSERT INTO dbo.QueryStoreSizeHistory (
current_storage_size_mb
, max_storage_size_mb
)
SELECT current_storage_size_mb
, max_storage_size_mb
FROM MiDB.sys.database_query_store_options;

Fíjate que la vista del sistema sys.database_query_store_options existe a nivel de la Base de datos donde QS está configurado. Por lo que hace falta usar la referencia en tres partes (NombreDB.NombreEsquema.NombreTabla).

3) Finalmente tras dejar pasar un tiempo, puedes comprobar como se ha ido usando el espacio asignado a Query Store:

SELECT *
	FROM DBA_Admin.dbo.QueryStoreSizeHistory
	ORDER BY date_time;

Mencionar que esta tabla de monitorización no tiene índices de ningún tipo ni PK. Esto es así poque su tamaño no debería superar unos pocos MBytes. Está pensanda para una comprobación inicial sobre cómo de rápido se llena el espacio de QStore. Si quieres mantener esta monitorización de forma indefinida, mi recomendación sería incluir un paso de limpieza/borrado de los registros más antiguos.

Tras un tiempo, con QS en funcionamiento, deberías echar un vistazo a los informes en tu Base de Datos a través de la interfaz de SQL Server Management Studio (SSMS):

2019-04-26_00h56_29

Lo recomendable en este punto, es que lo pruebes y veas por ti mismo lo útil que puede llegar a ser. Puedes usar también los ejemplos que utilicé en mi última presentación sobre Query Store , mira el link en la sección de comentarios.

Aparte de los informes predefinidos de SSMS, te recomiendo echar un vistazo a las consultas del sistema que vienen como parte de Query Store, y que te brinda toda la personalización que se te ocurra en tus comprobaciones. En la documentación de MSSQL puedes encontrar un buen puñado de ejemplos que usan esas vistas del sistema. Resultan especialmente interesantes las consultas bajo la sección Performance Auditing and Troubleshooting.

Y eso es todo por hoy, espero que te haya sido de ayuda. Cualquier pregunta, puedes enviarme un mensaje o dejar un comentario.

¡Hasta el próximo artículo!

Grupo de Usuarios SQL Server Málaga: ¡Tres años ya!

El 9 de Febrero de 2016 un grupo de amigos se reunió para tomar unas cervezas y hablar sobre SQL Server fuera del trabajo… Y surgió una pregunta ¿Por qué no crear una comunidad de usuarios en Málaga?

highres_467945864

Se acaban de cumplir 3 años desde que aquel día, y hemos compartido muchos buenos momentos, aprendido muchísimo, conocido otros grandes profesionales e interesantes empresas. Hemos mantenido un ritmo casi constante de una sesión técnica cada 2 meses, siempre abierta, de forma gratuita y dando la bienvenida a toda persona interesada en nuestros temas.

Sin olvidar que también tenemos un grupo en PASS, una comunidad global (también abierta) de SQL Server, dónde puedes encontrar una cantidad ingente de información de muy buena calidad. A fin y al cabo esa es nuestra principal motivación: Compartir conocimiento.

Así ha sido y así seguirá siendo, y que nuestra comunidad siga creciendo,  y es que a día de hoy superamos los 500 miembros en Meetup… Muchas gracias a todos los que formáis parte de esta familia geek que tenemos en Málaga. Especial mención para Carlos, David y Jesús, y a los amigos que habéis llegado posteriormente.

¡GRACIAS a tod@s!

Una opción a considerar en nuestras tablas: Large Value Types Out Of Row

Hoy vamos a comentar una opción que aunque existe desde hace varios años, es desconocida por muchos: “Large Value Types Out Of Row”.

¿Qué nos ofrece? Imagina una tabla con varias columnas y algunas de ellas de tipo grande (VARCHAR(MAX), NVARCHAR(MAX), XML, TEXT, VARBINARY, etc). La tabla requerirá muchas más páginas (disco) para almacenar la información, que si no tuviera esas columnas. Bien, con esta opción habilitada (y algo de trabajo adicional si la tabla ya tiene datos) podemos hacer que las consultas y operaciones DML con esta tabla sean significativamente más rápidas, además de otros beneficios.

¿Cómo es posible? Tras habilitar esta opción, las columnas de tipo grande pasan a almacenarse en páginas separadas. En una tabla que ya existe con miles de registros, esto puede implicar una liberación de espacio significativa en las páginas que usaba la tabla inicialmente. Dependerá de cuanta información de tipo grande existe. En una consulta que utiliza scans como la del ejemplo más abajo, vamos a ver cómo se reduce el número de páginas cuando se ejecuta la consulta. Esto significa que la consulta va a ser más rápida y consumirá menos recursos: Memoria y CPU.

La opción se habilita a nivel de tabla y la sintaxis es bastante sencilla:

USE AdventureWorks2014; -- Cambiar por tu nombre de BBDD
GO

EXEC sp_tableoption 'Person.Person', 'large value types out of row', 1;
 

En el siguiente ejemplo donde puedes, ejecutando paso a paso, cómo afecta positivamente esta opción al número de lectura lógicas cuando hacemos una consulta en la tabla modificada.

-- Limpiamos la cache para hacer pruebas sin nada precargado en memoria
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;
GO
USE AdventureWorks2014;
GO

-- 1) Comprobamos las estadísticas de acceso a disco para la siguiente consulta, esta tabla tiene la opción large_value_types_out_of_row desactivada(valor por defecto)
SET STATISTICS IO, TIME ON;
GO

SELECT FirstName
		, MiddleName
		, LastName
		, ModifiedDate
	FROM Person.Person;

SET STATISTICS IO, TIME OFF;
GO
-- RESULTADO:
--		(19972 rows affected)
--		Table 'Person'. Scan count 1, logical reads 3847, physical reads 3, read-ahead reads 3976, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--		(1 row affected)
--		SQL Server Execution Times:
--		CPU time = 47 ms,  elapsed time = 1055 ms.

-- 2) Cambiamos la opción large_value_types_out_of_row en la tabla
EXEC sp_tableoption 'Person.Person', 'large value types out of row', 1; -- switch on the feature
GO

-- 3) El cambio de esta opción no toma efecto para los valores existentes hasta el momento en que se actualicen
UPDATE Person.Person
	SET AdditionalContactInfo = AdditionalContactInfo
	WHERE AdditionalContactInfo IS NOT NULL;

UPDATE Person.Person
	SET Demographics = Demographics
	WHERE Demographics IS NOT NULL;

-- Reconstruimos todos los índices para qué las páginas que lo componen se reordenen, ojo esta operación puede ser costosa y generar bloqueos
ALTER INDEX ALL ON Person.Person REBUILD;

-- Limpiamos la cache de nuevo para comparar el efecto de la nueva config.
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

-- 4) Comprobar de nuevo las estadísticas de acceso a disco, como en el paso 1)
SET STATISTICS IO, TIME ON;
GO

SELECT FirstName
		, MiddleName
		, LastName
		, ModifiedDate
		--, AdditionalContactInfo
	FROM Person.Person;

SET STATISTICS IO, TIME OFF;
GO
-- RESULTADO:
--		(19972 rows affected)
--		Table 'Person'. Scan count 1, logical reads 268, physical reads 0, read-ahead reads 220, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--		(1 row affected)
--		SQL Server Execution Times:
--		CPU time = 15 ms,  elapsed time = 241 ms.

Observa que la cantidad de lecturas lógicas pasa de 3800 a menos de 300. Esto es una reducción del 90% para este escenario. En todas las tablas no va a pasar lo mismo, ya que depende principalmente de los datos que haya en la tabla, y de la consulta en sí.

Necesitarás la Base de datos de AdventureWorks2014, puedes descargarla en este enlance. El script anterior también está disponible en GitHub.

Si quieres comprobar cuantas tablas pueden beneficiarse de esta opción en tus bases de datos, puedes usar esta consulta que he subido a la TechNet Gallery. No olvides dar tu valoración! 🙂 Si quieres ver más aquí tienes un link a la documentación oficial.

Si lo piensas, esto es equivalente a hacer un particionado vertical de una tabla, sacando las columnas grandes a una tabla nueva. Lo interesante de la opción “Large Value Types Out Of Row” es que es completamente transparente para la aplicación, y mucho más sencillo de implementar.

Si lo probáis, comentadme que tal os parece y los resultados.

Hasta el próximo artículo!

Un asunto pendiente de MS SQL Server Developer

Para este artículo empezaremos comentando como es un entorno común de trabajo, que de encaje al ciclo de desarrollo de SW elegido en vuestra empresa. Luego veremos que nos ofrece MS SQL Server. Y al final os comento mi opinión.

Si nos paramos a pensar como son los entornos de trabajo de una empresa de desarrollo, muchos de vosotros encajaréis en en lo siguiente:

  • 1 Entorno de desarrollo: Volumen de datos reducido, esquema igual que producción + nuevos cambios. AKA Desarrollo,
  • [0..N] Entornos de pruebas de integración: Volumen de datos reducido, esquema igual al de producción. AKA Integración.
  • [0..M] Entornos de pruebas de carga: Volumen de datos similar a Producción, esquema clon de producción, configuración (servidor e instancia) igual a producción. AKA Load Test
  • [1..P] Entornos de producción: entorno final del producto/aplicación. AKA Producción.

Por [0..N], nos referimos a cero o más. [1..M]: Al menos uno, etc. Basta decir la importancia que tiene la consistencia entre entornos es crucial para que el ciclo de desarrollo del SW funcione correctamente.

Ejemplo 1: la definición de una tabla o el código de un procedimiento almacenado en Desarrollo debe ser igual al del resto de los entornos antes de un nuevo cambio.

Ejemplo 2: La configuración del entorno de Load Test debe ser un clon al de Producción, en todos los sentidos: Esquema de BD, configuración de la instancia de SQL Server, configuración del servidor, etc. De lo contrario las pruebas realizadas aquí no tendrán valor o muy poco, ya que no nos servirá para predecir el comportamiento de Producción en situaciones de alta carga de trabajo.

Ahora que hemos planteado el escenario, veamos que nos ofrece SQL Server para cada entorno, resumido en dos líneas:

  • Entornos de no Producción: MSSQL Developer.
  • Entornos de Producción: MSSQL Standard o Enterprise.

Standard tiene muchas características/opciones y Enterprise las tiene todas. Por citar un ejemplo: El motor de SQL Server en Enterprise, para una consulta X, usará automáticamente una vista indexada, en caso de que exista y ayude a generar un mejor plan de ejecución y por tanto optimize el rendimiento de la consulta. En Standard no es así. Por eso la diferencia de costes de licencias entre una y otra. Aquí puedes ver todas las diferencias entre las ediciones, hay un buen número.

Pos su parte la edición MSSQL Developer funciona con todas las opciones de Enterprise, seguramente con algún matiz pero ahora no entraremos en eso. Lo cual está muy bien para ver qué es lo que ofrece el MSSQL, probar y tratar de convencer a tus jefes de lo bueno que es Enterprise para que lo compren.

Pero… ¿Qué pasa si el entorno de Producción tiene que ser Standard, sí o sí? Developer edition no tiene hasta la fecha ninguna opción para configurarlo como otra edición diferente. WTF!!??

¡Sí amigos! Esto es lo que hay por ahora… pero se puede hacer algo para que lo cambien. Para ello existe un ticket abierto (Editado 1-Mayo-2020: enlace actualizado debido a cambio de sitio de MS para peticiones en SQL Server), si te interesa como a mí ¡Vota!

Hasta el próximo post!

 

Empezando con QUERY STORE

Esta es una nueva de las nuevas características en la versión 2016, de la que se está hablando mucho en los círculos de SQL Server ¿Está lista para ser usada? ¿O es una de esas características a las que hay que dejar evolucionar hasta que pasen unas versiones más? ¿Hay unas buenas prácticas ya? Yo te recomiendo ante todo que lo pruebes por ti mismo, aunque también puedes leer un blog y ver qué dicen… ¡Ahí vamos!

Lo que ofrece Query Store

Nos ofrece la creación de forma automática un registro perdurable de consultas, planes de ejecución y estadísticas. Esta funcionalidad se activa a nivel de base de datos, no de instancia. Lo cual veo positivamente, porque lo hace más adaptable. Habrá instancias donde te interese tenerlo activado para todas las BBDD de usuario y otras en las que sólo para algunas.

Si habéis hecho alguna vez consultas a las vistas dinámicas del sistema sys.dm_exec_query_stats o sys.dm_exec_procedure_stats para medir el rendimiento de alguna consulta o procedimiento, incluso combinadolo con la función sys.dm_exec_query_plan, posiblemente habréis pensado “Qué pena que toda esta información no sea persistente y se pierda con un reinicio del servidor o servicio de SQL…”

Bueno… ¡Es justo lo que hace Query Store! Y varias cosas más como veremos en este y futuros artículos.

Configurando Query Store

Query Store, en adelante QS, tiene una configuración por defecto que puede cambiarse a conveniencia. Recomiendo leer detenidamente todas estas opciones de configuración en la msdn, las detallo todas a continuación y comento algunas de ellas:

  • OPERATION_MODE: Puede ser READ_WRITE, que permite la recopilación y consulta de datos por parte de Query Store. También puede ser READ_ONLY, o sólo consulta, y dejan de actualizarse las estadísticas, los planes, etc.
  • CLEANUP_POLICY: Se usa junto a la keyword STALE_QUERY_THRESHOLD_DAYS y sirve para especificar cuanto tiempo queremos retener los datos recopilados, por defecto son 367 días.

Si la principal utilidad de QS es analizar el rendimiento de la base de datos, y dar un mejor soporte al troubleshooting, guardar los metadatos de todo un año o incluso varios meses parece muy exagerado. Para este propósito de resolver/investigar un problema de rendimiento, pondría un valor de una semana como mucho. Con un valor bajo, mantenemos a ralla el tamaño máximo que pueda necesitar QS.

  • DATA_FLUSH_INTERVAL_SECONDS: Para indicar con qué frecuencia quieres que los datos recopilados se graben en disco, se hagan persistentes. El valor por defecto son 900 seg (15 min).

Hay que tener en cuenta que en caso de un apagón se perderá todo lo que QS tenga en memoria, si este valor es alto entonces potencialmente puedes perder todos esos datos de los últimos N-1 minutos, donde N es el valor configurado. Por otro lado, si este valor es muy bajo, QS estará grabando cada pocos segundos en disco. Es algo que hay que ponderar y que dependerá de la memoria disponible y el rendimiento del almacenamiento.

  • MAX_STORAGE_SIZE_MB: Para indicar el tamaño máximo de almacenamiento asignado a QS. Por defecto son 100 MB. Si Query Store alcanza su tamaño máximo asignado, automáticamente su OPERATION_MODE pasa a READ_ONLY.

Este valor dependerá de la carga de trabajo, workload, que tenga la base de datos. Un buen valor será aquél que no cause frecuentes llenados del QS.

  • INTERVAL_LENGTH_MINUTES: Intervalo de tiempo para agregar datos. Valores permitidos: 1, 5, 10, 15, 30, 60 y 1440. Por defecto es cada hora.

Este valor es muy importante, ya que afecta al espacio que QS necesitará. Si se agregan datos cada pocos minutos, hará falta más espacio.

  • SIZE_BASED_CLEANUP_MODE: Hace que QS haga un mantenimiento automático cuando se acerque al MAX_STORAGE_SIZE_MB. Valores permitidos: OFF y AUTO, cuando llega al 90% de la capacidad, elimina las más antiguas y menos costosas hasta llegar al 80%.

Esta es una opción muy importante para evitar que el QS se llene, se ponga en modo READ_ONLY y por tanto deje de recopilar datos y sernos útil. El citado cleanup no debería ocurrir más de una vez cada N días, si no podrías estar perdiendo información útil en caso de querer investigar un problema en las últimas 24 horas por ejemplo.

  • QUERY_CAPTURE_MODE: Indica el modo actual de captura de datos de ejecución. Valores posibles:

ALL, captura todas las consultas. Es el valor por defecto.:

AUTO, recopila todas las consultas relevantes. Se basa en número de ejecuciones y consumo de recursos.:

NONE deja de capturar nuevas consultas, pero continúa recopilando datos para las que existían.:

La opción AUTO sin duda ayudará a mantener el tamaño de QS a raya.:

  • MAX_PLANS_PER_QUERY: Indica el número máximo de planes de ejecución que se pueden guardar para cada consulta. Por defecto es 200.

El valor por defecto es realmente alto. Personalmente lo reduciría en función de STALE_QUERY_THRESHOLD_DAYS. Si vamos a retener información para una semana, MAX_PLANS_PER_QUERY podría ser por ejemplo 7, para el caso extremo de una query que cambie su plan de ejecución cada día.

Eso es todo por ahora, espero que como introducción os haya sido útil. En un próximo artículo mucho más práctico sobre Query Store, comprobaremos algunos ejemplos de configuraciones, monitorización y consumo de recursos.

Malaga SQL PASS

Para empezar puedo contarte que PASS es una organización independiente sin ánimo de lucro creada por y para la comunidad. Con un grupo de mas de 100K miembros que va en aumento, PASS da soporte a los profesionales que usan la plataforma de datos de Microsoft a nivel global.

PASS se esfuerza por cumplir su misión mediante:

  • Facilitar el intercambio de información y networking a través de los grupos locales y virtuales, eventos online y regionales, y conferencias internacionales.
  • Proporcionar contenido técnico de alto nivel y actualizado para un aprendizaje en profundidad y desarrollo profesional.

PASS fue cofundado por CA Technologies y Microsoft en 1999 para promover y educar usuarios de SQL Server en todo el mundo. Desde su fundación PASS se ha expandido globalmente para acoger a cualquier profesional usando las tecnologías de datos de Microsoft.

Si necesitas más información:

Email: info@sqlpass.org

Telf: 1.604.899.6009 (International)

Mail: Professional Association for SQL Server
203 North LaSalle, Suite 2100
Chicago IL 60601 USA

 

Ser miembro de PASS es gratis

PASS pertenece a cada uno de sus miembros. El sitio web de PASS te proporciona un link virtual a todos los miembros de PASS, pero también te puedes beneficiar a nivel local uniéndote a un Grupo PASS y encontrándote en persona con otros profesionales del área.

Además, PASS organiza eventos globales como el Summit anual, el mayor evento para profesionales de Microsoft SQL Server en el mundo, o el PASS Business Analytics Conference. Y también los eventos SQLSaturday.

En España tenemos el PASS Spanish Group, con sede en Madrid, y desde Diciembre de 2016: el Grupo Malaga SQL Server. Somos el segundo grupo creado en España, de lo que estamos muy orgullosos! 🙂

También están los Grupos PASS virtuales, hay docenas y ofrecen conocimiento muy especializado:

  • Application Development
  • Big Data
  • Business Analytics
  • Business Intelligence
  • Cloud
  • Data Architecture
  • Data Science
  • Database Administration
  • DBA Fundamentals
  • Excel Business Intelligence
  • Varios grupos específicos por idiomas: Español, italiano, francés,…
  • Sanidad / Healthcare
  • High Availability & Disaster Recovery
  • Hybrid
  • Infrastructure VC
  • Performance
  • PowerShell
  • Professional Development
  • Saturday Night SQL
  • Security
  • Virtualization
  • Women in Technology
  • etc

PASS está activamente comprometido para añadir nuevos beneficios a sus miembros. Te puedes registrar siguiendo este enlace, y podrás ver la lista completa de beneficios. Si todavía no estás seguro, échale un vistazo a su web y mira todo lo que ofrecen. Una vez registrado, puedes unirte a todos los grupos, chapters, que quieras. Recomendamos los virtuales que te interesen y tu PASS chapter más cercano.

Lo mejor, un montón de recursos gratis e impulsar tu carrera 😉