Indices inutilizados sql server 2008

cancha · #1 (**permalink**) 08/02/2012, 00:11

Hola colegas, tengo una duda con respecto a los indices en una base de datos sql server 2008, el escenario es el siguiente:

tengo una DB donde hay problemas de performance eh estado revisando el aplicativo y me di cuenta que la mayor parte de la lógica de negocio esta programada en la DB con stores, hacen uso excesivo de cursores, tablas temporales y transacciones hasta para los sentencias select generando bloqueos innecesarios

consultando con la parte técnica que da soporte a esa DB me comentan que el sistema mejora sustancialmente y temporalmente (unos 3 a 4 dias) cuando realizan una re-indexacion, arreglando los indices de las tablas, yo tengo entendido que si se hace uso de muchos indices in-apropiadamente la performance de las consultas y DB tienden a disminuir la DB tien al rededor de 790 indices en total, pero no tengo certeza de que todos realmente se usen aqui es donde pido su ayuda con alguna herramienta que me permita saber que indices no se usan y poder eliminarlos, esto para tratar de evitar menos defragmentaciones en las tablas

como podria saber cuales son los indices inutiles o innecesarios?

por otro lado tambien estado usando el sql server profile para sacar traces y luego pasar los archivos trc al tunning advisor para que me de sugerencias para mejorar la performance de la DB, aqui yo saco los trace en archivos de 10MB no se si es suficiente para que las recomendaciones que me de sean efectivas, las recomendaciones que me da el tunning advisor es crear indices y estadisticas, en un archivo trace me estima un 70% de mejoria con las recomendaciones, en otro archivo trace un 32% de mejoria aqui estoy haciendo bien este proceso?, los archivos traces son de 10MB e hice la prueba con dos y esos los porcentajes que me arrojo

si creo mas indices y estadisticas, estaria arriesgando a que la DB se defragmente mas rapido?

la idea que tengo es eliminar los indices que no se usan y crear los que me recomienda el tunning advisor, pero para ello me falta identificar cuales son los indices que no se usan, algun script o herramienta para esta labor que conozcan me seria de gran ayuda me den una manito con este tema amigos

algunos consejos para mejorar el performance por favor

Saludos

iislas · #2 (**permalink**) 08/02/2012, 12:09

¿Que indices NO se usan?

Código SQL:

Ver originalDECLARE @db VARCHAR(25)
SET @db = '<database>'
SELECT object_name(i.object_id), i.name 
FROM sys.indexes i, sys.objects o 
WHERE  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 = db_id(@db) )
AND o.TYPE = 'U'
AND o.object_id = i.object_id
ORDER BY object_name(i.object_id) ASC
GO

¿Que indices me hacen falta?

Código SQL:

Ver originalSELECT 
  mig.index_group_handle, mid.index_handle, 
  CONVERT (DECIMAL (28,1), 
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
  ) AS improvement_measure, 
  'CREATE INDEX IDX_' + REPLACE(REPLACE(mid.statement, '[', ''), ']', '')+ CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle) 
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (DECIMAL (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

¿Consejos?

Crear un PLAN DE MANTENIMIENTO que se ejecute al menos 1 vez por semana que incluya la REGENERACION DE INDICES

matanga · #3 (**permalink**) 10/02/2012, 21:48

Como bien comenta iislas, debes identificar los índices necesarios, la falta de índices puede dar problemas de rendimiento en los SELECT, y tener índices que no se utilicen afecta los INSERT, UPDATE y DELETE además de ocupar espacio innecesariamente.

Por otro lado, debes detectar la fragmentación, un índice se fragmenta por los INSERT, UPDATE o DELETE sobre la tabla, eso lo resuelves haciendo un rebuild, y consultando la sys.dm_db_index_physical_stats en particular el campo avg_fragmentation_in_percent puedes establecer con que frecuencia, como por ejemplo: un rebuild diario de los índices que tengan una fragmentación superior al 10% o un rebuild semanal de todos los índices de la base.

Saludos

cancha · #4 (**permalink**) 10/02/2012, 23:05

Cita:

Iniciado por iislas

¿Que indices NO se usan?

Código SQL:

Ver originalDECLARE @db VARCHAR(25)
SET @db = '<database>'
SELECT object_name(i.object_id), i.name 
FROM sys.indexes i, sys.objects o 
WHERE  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 = db_id(@db) )
AND o.TYPE = 'U'
AND o.object_id = i.object_id
ORDER BY object_name(i.object_id) ASC
GO

¿Que indices me hacen falta?

Código SQL:

Ver originalSELECT 
  mig.index_group_handle, mid.index_handle, 
  CONVERT (DECIMAL (28,1), 
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)
  ) AS improvement_measure, 
  'CREATE INDEX IDX_' + REPLACE(REPLACE(mid.statement, '[', ''), ']', '')+ CONVERT (VARCHAR, mig.index_group_handle) + '_' + CONVERT (VARCHAR, mid.index_handle) 
  + ' ON ' + mid.statement 
  + ' (' + ISNULL (mid.equality_columns,'') 
    + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END 
    + ISNULL (mid.inequality_columns, '')
  + ')' 
  + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement, 
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (DECIMAL (28,1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

¿Consejos?

Crear un PLAN DE MANTENIMIENTO que se ejecute al menos 1 vez por semana que incluya la REGENERACION DE INDICES

Gracias iislas util estos scripts, ahora tengo otra duda corri esos escript normal
en el caso de los indices que no se usan con el script que compartes me da un total de 101 indices y cuando corro con otro escript que saco de la pagina de sql server me sale 104 aca esta el script

Código SQL:

Ver originalSELECT OBJECT_NAME(ind.object_id) [Tabla], ind.name [Indice]
FROM Sys.Indexes ind
INNER JOIN Sys.Objects obj ON obj.object_id = ind.object_id
WHERE OBJECTPROPERTY(obj.object_id,'IsUserTable') = 1
AND NOT EXISTS
(SELECT 1 FROM Sys.dm_db_index_usage_stats usg
WHERE usg.object_id = ind.object_id 
AND usg.index_id = ind.index_id
AND ind.index_id = usg.index_id
) AND ind.name IS NOT NULL 
ORDER BY
[Tabla],
[Indice]

cancha · #5 (**permalink**) 10/02/2012, 23:21

con respecto a la regeneracion de indices lo hice mediante este procedure

Código SQL:

Ver originalALTER PROCEDURE [dbo].[job_degragmentarDb]
AS
SET NOCOUNT ON --- INSTRUCCION QUE OMITE LAS SALIDAS DE LAS SENTENCIAS DENTRO DEL PROCEDURE PARA EVITAR USO DEL ANCHO DE BANDA AL ENVIAR INFORMACION A LA APLICACION CLIENTE
DECLARE @contador INT = 1 
DECLARE @limiteContador INT
DECLARE @tblDefragmentar TABLE(
    id INT IDENTITY(1,1) NOT NULL,
    cmd VARCHAR(180)
)
--- INSERTAMOS COMANDOS A EJECUTAR EN FORMATO CADENA DE LOS INDICES CON FRAGMENTACION SUPERIOR AL 30% EN LA VARIABLE @tblDefragmentar LAS CUALES SERAN REGENERADAS
INSERT INTO @tblDefragmentar(cmd) (
    SELECT 
        ('ALTER INDEX ' + name + ' ON ' + OBJECT_NAME(tblFrag.object_id) + ' REBUILD WITH(ONLINE = ON)')
    FROM sys.dm_db_index_physical_stats(db_id('SSLProduccion'),NULL,NULL,NULL,NULL)tblFrag
    JOIN sys.indexes i ON tblFrag.object_id = i.object_id AND tblFrag.index_id = i.index_id 
    WHERE tblFrag.avg_fragmentation_in_percent > 30 AND NOT name IS NULL
)
--- INSERTAMOS COMANDOS A EJECUTAR EN FORMATO CADENA DE LOS INDICES CON FRAGMENTACION ENTRE EL 5 Y 30 % EN LA VARIABLE @tblDefragmentar LAS CUALES SERAN REORGANIZADAS
INSERT INTO @tblDefragmentar(cmd) (
    SELECT 
        ('ALTER INDEX ' + name + ' ON ' + OBJECT_NAME(tblFrag.object_id) + ' REORGANIZE') 
    FROM sys.dm_db_index_physical_stats(db_id('SSLProduccion'),NULL,NULL,NULL,NULL)tblFrag
    JOIN sys.indexes i ON tblFrag.object_id = i.object_id AND tblFrag.index_id = i.index_id 
    WHERE tblFrag.avg_fragmentation_in_percent BETWEEN 5 AND 30 AND NOT name IS NULL
)
--- ASIGNAMOS EL LIMITE DEL CONTADOR A LA VARIABLE @limiteContador PARA RECORRER LAS FILAS DE LA TABLA @tblDefragmentar
SET @limiteContador = (SELECT COUNT(*) FROM @tblDefragmentar)
--- RECORREMOS LAS FILAS DE LA VARIABLE @tblDefragmentar
WHILE(@contador <= @limiteContador)
BEGIN
    DECLARE @cmd VARCHAR(180)
    --- ASIGNAMOS EL VALOR DEL COMANDO A SER EJECUTADO EN FORMATO CADENA A LA VARIABLE @cmd
    SET @cmd = (SELECT cmd FROM @tblDefragmentar WHERE id = @contador)
    --- EJECUTAMOS EL COMANDO PARA DESFRAGMENTAR LOS INDICES
    EXEC(@cmd)
    --- INCREMENTAMOS EL CONTADOR DE RECORRIDO DE FILAS DE LA TABLA
    SET @contador = @contador + 1
END

pero el resultado es que aproximadamente la mitad de la cantidad total de los indices que tenian fragmentacion promedio superior al 5 %, se redujeron por debajo de los 5% el resto no lo hizo algunos se redujeron y quedaron con 30 hasta 50% en la fragmentacion promedio estos datos los saque de aqui
sys.dm_db_index_physical_stats del campo avg_fragmentation_in_percent

esto porque sucede? ya no disminuyen cada vez que vuelvo a regenerar o reorganizar los indices

estos son los indices que se quedaron por encima de 5%
lo campos estan en este orden:

database_id | object_id | index_id | partition_number | index_type_desc | alloc_unit_type_desc | index_depth | index_level | avg_fragmentation_in_percent | fragment_count | avg_fragment_size_in_page | page_count

Código result set:

Ver original7   37575172    1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   11.5942028985507    15  4.6 69
7   37575172    12  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   6.66666666666667    3   5   15
7   37575172    13  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   6.66666666666667    3   5   15
7   125399666   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   33.3333333333333    2   1.5 3
7   208823906   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   6.66666666666667    2   7.5 15
7   208823906   2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   20  2   2.5 5
7   208823906   3   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   25  2   2   4
7   213575799   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   11.1111111111111    2   4.5 9
7   213575799   2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   25  2   2   4
7   213575799   9   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   33.3333333333333    2   1.5 3
7   229575856   16  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   5.57377049180328    51  5.98039215686275    305
7   243531951   2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   12.5    2   4   8
7   243531951   12  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   11.1111111111111    2   4.5 9
7   295112242   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   20  2   2.5 5
7   301348238   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   50  2   1   2
7   352108395   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   12.8205128205128    18  4.33333333333333    78
7   352824419   1   1   CLUSTERED INDEX IN_ROW_DATA 2   0   7.69230769230769    19  5.47368421052632    104
7   352824419   2   1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   24.4444444444444    15  3   45
7   389576426   22  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   11.8279569892473    21  4.42857142857143    93
7   389576426   23  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   10.2564102564103    25  4.68    117
7   389576426   27  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   5.40540540540541    26  5.69230769230769    148
7   389576426   28  1   NONCLUSTERED INDEX  IN_ROW_DATA 2   0   9.01639344262295    25  4.88    122

aprovecho para comentarles que el aplicativo que estoy revisando tiene problemas de lentitud y bloqueos en las operaciones, revisando me eh dado cuenta que realizan muchas transacciones dentro de los procedures y se crean en ocaciones transacciones anidadas y dentro de ellas hay select quizas como hay un select y mientras ese select no termine de arrojar todo el resultado la tabla se mantiene bloqueada impidiendo asi que otro usuario pueda acceder a ella para este caso como podria detectar cuales son las queries o procedures que causan mas bloqueos y por tiempos largos? una posible solucion seria quitar los select que haya dentro de transacciones y ponerlas fuera, pero como hay algunos casos en la que un procedure llama a otro y cada procedure tiene su propia transaccion si quito el select fuera de la transaccion en uno de los procedures seguira dentro de la otra transaccion del procedure padre el que invoca como podria darle solucion a esto?

quitar todas las transacciones de todos los procedures ? seria muy tedioso hacerlo con un montos de procedures 1500 promedio

iislas · #6 (**permalink**) 13/02/2012, 11:47

Es un mal desarrollo, el "tiempo de vida" de una transaccion, debe ser MINIMA (lo mas minima posible).

En cuando a defragmentar, a veces es recomendable regenerar los indices:

-- version sin cursores
exec sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 100)'
go
exec sp_updatestats
go

cancha · #7 (**permalink**) 14/02/2012, 14:14

Cita:

Iniciado por iislas

Es un mal desarrollo, el "tiempo de vida" de una transaccion, debe ser MINIMA (lo mas minima posible).

En cuando a defragmentar, a veces es recomendable regenerar los indices:

-- version sin cursores
exec sp_msforeachtable 'ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 100)'
go
exec sp_updatestats
go

hola de nuevo iislas perdona mi ignorancia pero porque le pones FILLFACTOR = 100
tengo entendido que esto es el factor de relleno y por defecto es 0

y el procedure sp_updatestats me actualiza las estadísticas dime esto en que beneficia?, me comentaron que la db a sido migrada de sql server 2000 al 2005 y luego al 2008 pero no usaron los asistentes de migraciones simplemente copiaron y ejecutaron el script así que creo que hay algunos comandos o sentencias que quizás ya son obsoletas o en sql server 2008 sugieren usar otras alternativas

dime una vez que cree los indices que me recomienda el script y borrar los que no se usan actualizar las stadisticas y regenerar los indices en ese orden (o el orden tambien es importante?) es necesario reiniciar el sql server ?

Gracias

saludos

iislas · #8 (**permalink**) 14/02/2012, 14:37

El cero, representa el 100 en el fillfactor, 0 = 100