Buscar este blog

viernes, 14 de marzo de 2014

Heaps en SQL Server: tablas sin índice clustered y sus consecuencias

Definición

En SQL Server las tablas pueden tener o no tener un índice clustered. El índice clustered es por el que SQL ordena físicamente los datos (las filas de las tablas) en disco.

El índice clustered le sirve a SQL para buscar, ordenar y agrupar registros de manera eficiente. Así, no tener un índice clustered en una tabla puede llevarnos a problemas de rendimiento. Cuando una tabla no tiene índice clustered, se llama heap.

El problema

Los heaps mantienen los registros en las páginas de datos en el mismo orden en el que se han insertado. Esto hace que los heaps resulten más rápidos a la hora de realizar un INSERT, ya que no han de insertarse en una posición en concreto, sino directamente a continuación del último registro existente para la tabla en cuestión.

Sin embargo, cualquier otra operación que requiera un orden en los datos será más lenta. Esto se aplica a SELECT, DELETE y UPDATE, salvo que se quieran efectuar estas operaciones sobre la tabla completa.

Lo que la MSDN nos dice de los heaps es que:
  • A veces, hay buenos motivos para dejar una tabla como heap en lugar de crear un índice clustered, pero para usar los heaps de forma eficaz se requieren conocimientos avanzados. La mayoría de las tablas deben tener un índice clustered cuidadosamente elegido.
  • Si una tabla es un heap y no tiene ningún índice no clustered, debe examinarse la tabla completa (table scan) cuando se busca una fila.
Es decir, en general los heaps no son aceptables. Solamente bajo ciertos escenarios muy estudiados para los que una ordenación de los datos no es necesaria y un mantenimiento de índices por parte de SQL implicaría un sobrecoste innecesario (por ejemplo, una tabla con una lista de provincias de España, para la que siempre se devuelve la lista completa y nunca se busca información para un registro concreto).

En cualquier otro caso, cualquier consulta sobre una tabla sin índice clustered obligará a SQL Server a recorrer la tabla entera en busca del registro o registros solicitados. Evidentemente, cuanto mayor sea la tabla, mayor será el problema de rendimiento que esta situación puede generar.

Cómo detectarlo

El script a continuación ayuda a detectar heaps en nuestras bases de datos:

SELECT OBJECT_NAME(i.object_id) AS TableName, p.rows
FROM sys.indexes i
INNER JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id = i.index_id
WHERE i.index_id = 0
ORDER BY p.rows DESC

La solución

En general, con cada heap hay que pararse y determinar cuál debería ser su índice clustered. El candidato más idóneo para ser índice clustered suele ser siempre la clave primaria, aunque existen excepciones. Si la tabla sólo tiene clave primaria, ésta debe ser clustered siempre. El resto de los casos podemos estudiarlo, aunque existen algunas directrices:

  • Suelen ser los campos que usamos para hacer búsquedas y joins sobre la tabla.
  • Suelen ser los campos por los que se referencia a la tabla desde otras tablas. 
  •  Hay que intentar que el índice clustered de una tabla tenga el mínimo tamaño posible.

Todo esto señala, como decíamos, a la clave primaria de la tabla. Pero, en ocasiones, creamos claves alternativas a la primaria que se ajustan mejor a esta definición.

Referencias:

miércoles, 12 de marzo de 2014

Tipos de datos por defecto en SQL Server

En ocasiones necesitamos que nuestras consultas devuelvan valores constantes. Por ejemplo, de manera muy sencilla, podemos tener una consulta como la siguiente:

Si necesitamos recuperar los valores devueltos por la consulta en un recordset de ADO.NET, por ejemplo, ¿qué tipo de datos debemos esperar de columnas de este tipo? La respuesta: depende.

Depende del tipo de constante que hayamos introducido. Si se trata de un número que no desborde los 4 bytes de capacidad de un entero (int), como en el caso del 1 de nuestra consulta anterior, éste será el tipo de datos de dicha constante.

Si escribimos la constante entre comillas simples, SQL lo tratará como un varchar del tamaño del literal que hayamos escrito. Por ejemplo:

Creará un campo varchar(1). Éste sería nvarchar(2) si hubiésemos escrito lo siguiente, indicando un literal Unicode mediante la N delante del literal:

Para un número con parte decimal, el tipo de datos será numeric; lo mismo que para un número de tamaño mayor que el máximo entero positivo. Si escribimos un símbolo de moneda, como €, SQL lo interpretará con el tipo de datos money. Y así, sucesivamente.

Para comprobar todos los tipos de datos que queramos, podemos ejecutar el siguiente script:

Y comprobar en sus resultados qué tipo de datos podemos esperar para cada tipo de constante que se nos ocurra:

Llama especialmente la atención el tratamiento que recibe el NULL de la columna 7: su tipo de datos por defecto también es int.

Éste es el comportamiento que cabe esperar de SQL Server. Sin embargo, lo recomendable sería controlar exactamente qué tipo de datos queremos que nos sea devuelto ejecutando los CAST pertinentes sobre cada una de las constantes. En el ejemplo anterior, lo lógico es que queramos tratar el literal '20140312' como una fecha, por lo que la consulta sobre ese valor debería realizarse así: