Navegador de Archivos
General
October 2010
Mostrar los índices de una tabla en SQL Server 2005
Muchas veces requerimos conocer que índices existentes en la base de datos, para esto podemos emplear el siguiente script.
DECLARE _at_IndexInfo TABLE (index_name varchar(250)
,index_description varchar(250)
,index_keys varchar(250)
)
INSERT INTO _at_IndexInfo
exec sp_msforeachtable 'sp_helpindex ''?'''
SELECT index_name, FROM _at_IndexInfo
Ahora que si lo que deseas es listar solamente los índices de una tabla en particular sería algo como
sp_helpindex 'Production.Product'
Y si quieres algo más como el esquema, es mejor una consulta como:
SELECT s.name AS [Schema], t.name AS TableName,
ind.name, ind.index_id, ic.index_column_id, col.name,
ind.*, ic.*, col.*
FROM sys.indexes ind
INNER JOIN sys.index_columns ic
ON ind.object_id = ic.object_id AND ind.index_id = ic.index_id
INNER JOIN sys.COLUMNS col
ON ic.object_id = col.object_id AND ic.column_id = col.column_id
INNER JOIN sys.TABLES t
ON ind.object_id = t.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
WHERE
ind.is_primary_key = 0
AND ind.is_unique = 0
AND ind.is_unique_constraint = 0
AND t.is_ms_shipped = 0
AND t.name LIKE '<Table_Name>'
AND s.name LIKE '<Schema_Name>'
ORDER BY
t.name, ind.name, ind.index_id, ic.index_column_id
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