Por Leandro Sarmiento - Data Engineer en RocBird
-
Con el advenimiento de nuevas tecnologías durante las últimas décadas ha aumentado la cantidad y tipos de datos que las organizaciones tienen a su disposición para explotar con el fin de brindar mejores productos, servicios y experiencia a sus usuarios.
De esta forma se opta cada vez más por el uso de Big Data e Inteligencia Artificial en la nube; no obstante, todavía se debe seguir trabajando con soluciones on premises tradicionales ya sea porque se debe brindar soporte a las aplicaciones mientras se completa su migración a la nube o porque ciertas aplicaciones no serán migradas. Esto indica que debemos estar preparados para interactuar con tales aplicaciones, como podrían ser aquellas que utilizan motores de bases de datos relacionales.
En este artículo abordaremos algunos conceptos y herramientas propias de SQL Server con el fin de realizar un primer análisis y optimización de consultas.
Conceptos
Índices
Es un concepto fundamental al momento de hablar de optimización de consultas, en lo sucesivo veremos los aspectos más importantes sin entrar en demasiados detalles.
SQL Server organiza los datos de una tabla en estructuras lógicas conocidas como Heaps (pilas) o Balanced Trees (árboles balanceados) una tabla que se organiza como un Balanced Tree también se conoce como Clustered Table o Clustered Index. El tipo de organización lógica de la tabla influirá en la cantidad de lecturas físicas (lecturas que se realizan al disco) que el motor de base de datos deberá realizar para devolver una fila o un conjunto de ellas lo que afectará al desempeño de la consulta. El objetivo final será reducir la cantidad de lecturas físicas.
Heaps:
Es una estructura muy simple en donde los datos no tienen un orden lógico.
En cada consulta se debe escanear el Heap completo incluso si se desea devolver solo una fila de la tabla. Esta operación se denomina Table Scan.
Clustered Index (Balanced Trees):
Una tabla se organiza como un Balanced Tree cuando creamos explícitamente un índice usando el comando CREATE CLUSTERED INDEX o implícitamente cuando creamos una Primary Key o una Unique Constraint (restricción de unicidad) sobre la tabla.
Esta estructura es similar a un árbol invertido con una raíz en el nivel superior, niveles intermedios y hojas en su nivel final, es en este nivel donde residen los datos de la tabla.
Como los datos de la tabla están en el Clustered Index solo puede existir un único Clustered Index.
Los datos en los niveles hojas están ordenados lógicamente según la Clustering Key, es decir la columna o columnas que forman el índice.
Un clustered index solo implica la organización lógica de la tabla y no una organización física.
La Clustering Key puede estar formada por una o varias columnas. Puede usarse una columna con valores repetidos como Clustered Index pero SQL Server mantendrá la unicidad de la clave internamente.
Para devolver las filas de una consulta SQL Server recorre el árbol hasta sus hojas lo cual es muy eficiente, esta operación se conoce como Index Seek.
Si SQL Server tiene que recorrer todo el índice para devolver los datos de una consulta realiza un Index Scan.
NonClustered Index:
Permite ordenar la tabla de una manera diferente que la presente en el Clustered Index, dicha ordenación se guarda en una estructura separada por lo que pueden existir varios Non Clustered Index.
Esta estructura también se ordena como un Balanced Tree solo que en las hojas no se guardan los datos de la tabla (como en el Clustered Index) sino que se guardan punteros a los datos de la tabla base conocidos como row locator.
Pueden crearse para un Heap o un Balanced Tree la única diferencia será que en el caso de un Heap el row locator es un RID (Row Identifier) y en el caso de un Balanced Tree el row locator es la Clustering Key.
Para devolver las filas de una consulta, SQL Server recorre todo el NonClustered Index, llega a las hojas y luego utiliza el RID o la Clustering Key para buscar los datos en el Heap o el Clustered Index. Esta operación se conoce como RID Lookup o Key Lookup según se estén buscando los datos en un Heap o en un Clustered Index . Son operaciones eficientes siempre que la consulta devuelva un número limitado de filas.
Se pueden agregar columnas de datos al NonClustered Index usando el comando INCLUDE esto permitirá que SQL Server encuentre los datos de la consulta en las hojas del Non Clustered Index, de esta forma se evitan las operaciones RID Lookup y Key Lookup, este tipo de consultas se conoce como consultas cubiertas.
Si SQL Server tiene que recorrer todo el índice para devolver los datos de una consulta realiza un Index Scan.
Para conocer los índices que tiene una tabla podemos consultar la vista de sistema sys.indexes:
SELECT OBJECT_NAME(object_id) AS table_name, name AS index_name, type, type_desc
FROM sys.indexes
WHERE object_id = OBJECT_ID(N'[TablaAConsultar]’, N'U');
Se debe reemplazar [TablaAConsultar] por el nombre de la tabla cuyos índices queremos conocer.
La tabla SalesLT.Product tiene 4 índices, el Clustered Index creado a partir de la clave primaria y 3 NonClustered Index.
Estadísticas:
Otro aspecto importante de una consulta es conocer la cantidad de operaciones de lectura necesarias para obtener los resultados, mientras menos lecturas se realicen más eficiente es la consulta. En SQL Server podemos conocer esto usando el comando SET STATISTICS IO ON.
Para obtener información sobre la ejecución de la consulta primero corremos el comando SET STATISTICS IO ON y luego la consulta que queremos evaluar:
La consulta anterior nos devuelve estos valores de SET STATISTICS:
Scan count: Número de Index Scan o Table Scan realizados.
Logical Reads: Número de páginas que se leen desde el caché (una página es una unidad física de 8 KB donde se guardan los datos de la tabla, una tabla puede tener muchas páginas asignadas).
Physical reads: Número de páginas que se leen desde el disco.
Nota: se muestra solo parte de la información que devuelve SET STATISTICS IO, aquella que es relevante para este artículo.
Hasta aquí tratamos los primeros conceptos para realizar la optimización de consultas, en la segunda parte se verán otros conceptos y ejemplos que aplican los mismos.