Optimización de queries con SQL Server – Parte 1

Hola, cómo les va? 👋

El día de hoy les quiero platicar sobre optimización de queries en SQL Server. Ya les había comentado en algún post, que el performance es algo que debemos tener en cuenta al momento de hacer nuestros queries, y pues ahora voy a hacer una serie de posts relacionados a esto.

Voy a incluir algunos tips y algunas maneras de diseñar nuestros queries para mejorar el performance y que sean escalables. Quizás ya estén familiarizados con algunos de ellos, pero no está de más que sepan el por qué se hace así, ya que muchas veces pasa que hacemos algo de tal manera porque alguien nos dijo, pero sin más explicación.

En esta serie de posts, voy a tratar de mejorar el performance de los queries que hagamos sin agregar ningún índice, únicamente cambiando el diseño de nuestro query.

SELECT *

Voy a iniciar con SELECT * ya que es el primer query que aprendemos cuando estamos iniciando con SQL. Podrán pensar que no hay absolutamente nada de malo en utilizarlo de esta manera, y a lo mejor es cierto si estamos haciendo pruebas, revisando los datos, etcétera.

Sin embargo, cuando hacemos este tipo de queries para alguna aplicación, servicio o reporte, en ocasiones no utilizamos todas las columnas de nuestra tabla, y solamente estamos gastando recursos sin hacer uso de ellos.

Pudiera ser que nuestra tabla tiene 50 columnas y en nuestro reporte o aplicación solamente vamos a mostrar 15, el resto únicamente sirvió para generar más overhead en la red, consumir más recursos de I/O y lo más importante, si estaban pensando que su query iba a usar un índice para mejorar el performance, ahora SQL Server va a tener que hacer un Key Lookup para traerse todas las demás columnas que no son parte del índice ni de la llave primaria.

Para el ejemplo que les voy a mostrar, voy a utilizar la base de datos de StackOverflow2010, que es la más pequeña de todas. Si desean descargarla, pueden ir aquí.

Supongamos que nos piden mostrar el nombre del usuario y la reputación que tiene en StackOverflow de todos los usuarios que viven en San Diego, CA.

SET STATISTICS IO, TIME ON

SELECT 
    DisplayName, Reputation 
FROM 
    Users
WHERE
    Location = 'San Diego, CA';

Anteriormente ya habíamos creado un índice para satisfacer este query, vamos a ver primero la manera correcta. Y si vemos el plan de ejecución, vemos que SQL Server pudo utilizar nuestro índice y nos devolvió los resultados esperados eficientemente.

Incluí este comando SET STATISTICS IO, TIME ON que es para que SQL Server nos de información sobre el tiempo de ejecución y las operaciones que hizo como lecturas lógicas, físicas, etcétera.

(409 rows affected)
Table 'Users'. Scan count 1, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 49 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Vean la parte que dice “logical reads” esto es cuántas páginas tuvo que leer para poder traernos la información que le pedimos. En este caso son 7, y el tiempo de ejecución fue de 49 ms, o sea .049 segundos. Bastante rápido a mi parecer.

Ahora vamos a ver qué pasa, si en lugar de únicamente usar esas dos columnas lo cambiamos por SELECT *.

SET STATISTICS IO, TIME ON

SELECT 
    *
FROM 
    Users
WHERE
    Location = 'San Diego, CA';

Aquí vemos que el plan de ejecución cambió. Si ven, además de usar el índice que ya teníamos, está haciendo un Key Lookup en la llave primaria, para traer todas las demás columnas. Y a parte, SQL Server nos dice que hay un índice que podría beneficiar a nuestro query.

Si vamos a la ventana de mensajes, vemos que SQL Server tuvo que leer 1813 páginas y a un lado dice “physical reads 16”, esto significa que tuvo que consultar 16 páginas directamente en disco, aumentando el I/O de nuestro query.

El tiempo de ejecución fue de .4 segundos que igual no está mal, pero si pasamos esto a un servidor de producción donde se va a estar ejecutando el query miles de veces por segundo, no creo que sea algo bueno, menos si tiene que leer páginas de disco como en este ejemplo.

(409 rows affected)
Table 'Users'. Scan count 1, logical reads 1813, physical reads 16, page server reads 0, read-ahead reads 803, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 415 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 

MISSING INDEXES

Al ejecutar nuestro query con SELECT *, SQL Server nos recomienda crear un índice para hacer la consulta más “eficiente”. Vamos a ver el detalle del índice que nos está pidiendo.

USE [StackOverflow2010]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[Users] ([Location])
INCLUDE ([AboutMe],[Age],[CreationDate],[DisplayName],[DownVotes],[EmailHash],[LastAccessDate],[Reputation],[UpVotes],[Views],[WebsiteUrl],[AccountId])
GO

JA! Prácticamente nos dice que incluyamos todas las columnas en el índice. Eso tampoco creo que sea bueno, y no podemos andar confiando en cada índice que SQL Server nos diga que nos hace falta, pero eso es plática para otro post 😉.

Voy a dejar hasta aquí el post, para no aburrirlos. Estén al pendiente de los siguientes posts, les voy a ir dejando más técnicas y tips para optimizar sus queries y hacerlos más eficientes.

Además que todo esto lo vemos en mi curso Advanced SQL Querying que pueden checar en esta página. También voy a agregar un curso específico para optimización de queries e índices, así que espérenlo pronto.

Bueno, ya me despido y los veo en el siguiente, espero les haya gustado. Gracias por leérme, los veo luego, cheers! 🍻