Navegador de Archivos
Calendario del Blog
General
Tocando los índices III de III
Continuando con las últimas publicaciones de SQL, ahora toca el turno a la última parte de "Tocando los índices":
En el artículo anterior se mostró lo que es el reconstruir o defragmentar el índice y se hizo mención de las estadísticas. Tal vez en otro post profundice un poco más sobre las estadísticas.
Por lo pronto me gustaría aclarar que son las estadísticas del SQL. Tal cómo la definición de estadísticas lo dice, es "una ciencia formal" (la estadística, claro está) referente a la recolección, análisis e interpretación de datos. A partir de estas, el motor de SQL decide la mejor forma de obtener la información que se le solicita en una consulta.
Mejores prácticas en la creación de Índices
Antes de crear cualquier índice, es necesario conocer el uso que le daremos a los datos. Si son de solo lectura (un Data Warehouse, sería un claro ejemplo), si se cargará información para después "masajear" la información y volcar los resultados a una tabla definitiva, etc. El objetivo de los índices es facilitar la búsqueda de datos al motor de SQL y no penalizar su desempeño. Si tenemos índices en una tabla que servirá para cargar información de millones de registros en el mismo proceso, lo mejor sería no crear índices hasta que se haya efectuado la inserción, esto ayudará al motor de SQL a guardar dicha información lo más ágil posible. En cuanto termine la carga, se pueden crear los índices respectivos en las columnas que lo requieran.
Al nombrar los objetos es bueno seguir un estándar, con la finalidad de identificar fácilmente el objetivo de cada objeto. En el caso de los índices no hay excepción y en mi caso empleo la nomenclatura ix (de índice -IndeX), nombre del índice y si incluye alguna otra columna. Como el siguiente ejemplo:
USE AdventureWorks2008R2;
GO
CREATE INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor (BusinessEntityID);
GO
Es importante revisar periodicamente el estado de los índices, esto nos permite establecer un tiempo de mantenimiento en el que se podrán reorganizar o reconstruir los índices para su mejor desempeño, usando la siguiente consulta podemos ver el uso de los índices.
SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL);
GO
La columna que nos interesa del resultado de la consulta anterior es avg_fragmentation_in_percent, lo que nos ayudara a saber que debemos hacer para cada índice de cada tabla.
| avg_fragmentation_in_percent value | Corrective statement |
| > 5% and < = 30% | ALTER INDEX REORGANIZE |
| > 30% | ALTER INDEX REBUILD WITH (ONLINE = ON)* |
También podemos modificar la consulta para una tabla en particular.
USE AdventureWorks2008R2;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
Debido a que no solo es crear índices en la base de datos, es recomendable saber cuando crear y cuando no crear índices. Para esto se puede hacer uso de las herramientas disponibles desde la versión 2005 de SQL Server como: SQL Server Profiler, Database Engine Tuning Advisor, sin olvidar el uso de las Dynamic Management Views. Estás últimas son de mucha ayuda si las sabemos emplear, a continuación dejo algunos scripts que pueden ayudar al diagnostico de índices y sus creaciones o borrados masivos.
--Indices que no se han usado desde el ultimo inicio del servidor
SELECT i.name
FROM sys.indexes i
WHERE i.object_id=object_id('<table_name>') AND
i.index_id NOT IN (SELECT s.index_id
FROM sys.dm_db_index_usage_stats s
WHERE s.object_id=i.object_id AND
i.index_id=s.index_id AND
database_id = <dbid> )
-- Indices que aun no se han usado
SELECT
object_name(i.object_id),
i.name,
s.user_updates,
s.user_seeks,
s.user_scans,
s.user_lookups
FROM
sys.indexes i
LEFT JOIN
sys.dm_db_index_usage_stats s
ON s.object_id = i.object_id AND
i.index_id = s.index_id AND
s.database_id = <dbid>
WHERE objectproperty(i.object_id, 'IsIndexable') = 1 AND
-- index_usage_stats has no reference to this index (not being used)
s.index_id IS NULL OR
-- index is being updated, but not used by seeks/scans/lookups
(s.user_updates > 0 AND s.user_seeks = 0
AND s.user_scans = 0 AND s.user_lookups = 0)
ORDER BY object_name(i.object_id) ASC
-- Para eliminar índices sin usar
SELECT
o.name AS Objeto,
i.name AS Indice,
i.index_id AS IndID,
(user_seeks + user_scans + user_lookups) AS Lecturas,
user_updates AS Escrituras,
(SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) AS Filas,
CASE
WHEN s.user_updates < 1 THEN 100
ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS LecturasVsEscrituras,
'DROP INDEX ' + QUOTENAME(i.name) + ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) AS 'Sentencia DROP'
FROM
sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o ON s.object_id = o.object_id
INNER JOIN sys.schemas c ON o.schema_id = c.schema_id
WHERE
OBJECTPROPERTY(s.object_id,'IsUserTable') = 1 AND
s.database_id = DB_ID() AND
i.type_desc = 'nonclustered' AND
i.is_primary_key = 0 AND
i.is_unique_constraint = 0 AND
(SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY Lecturas
Espero esto sea de utilidad para alguien más. 
Para saber más.
Estadísticas de visitantes
Comentarios Recientes
- Celulares con Wifi en Como escoger el amor de tu vida.
- master en La diferencia entre un amigo y un novio
- Iván Jaimes en Internet Explorer 8 y la vista compatibilidad
- mauro en Internet Explorer 8 y la vista compatibilidad
- Iván Jaimes en ¿Qué hacer para que te valoren más?
- marcela en ¿Qué hacer para que te valoren más?
- Iván Jaimes en
- karyot en Haces ruido o te gusta volar?
- Iver en Haces ruido o te gusta volar?
- leo_on en Haces ruido o te gusta volar?
- Iván Jaimes en Los detalles hacen la diferencia
- linuxman en Los detalles hacen la diferencia
- Marcelo Araújo en Internet Explorer 8 y la vista compatibilidad
- Iván Jaimes en Código con aroma de mujer
- karyot en Código con aroma de mujer
- Profesor Mamelowsky en Código con aroma de mujer
- karyot en Lo sabroso de la vida
- Iván Jaimes en
- Iván Jaimes en Internet Explorer 8 y la vista compatibilidad
- cordoba en Internet Explorer 8 y la vista compatibilidad
Dejar un comentario