Usando DMV’s para optimizar nuestra BD (Índices)

Hola a todos, en algún momento no han necesitado obtener que índices pueden estar afectando nuestro sistema, o que índices hacen falta en nuestro sistema para que las consultas se realicen mas rápido. Les voy a colocar varios scripts que he estado armando para obtener algunos indicadores que nos permitirán optimizar nuestras bases de datos, los scripts son los siguientes:

1. Identifica los índices que están siendo mantenidos pero que no se están utilizando, recuerden que los índices ocupan espacio y a su vez pueden causar ciertos efectos negativos a nuestro sistema dependiendo de como fueron creados.

   1: --Identifica los índices que están siendo mantenidos mas no usados.

   2:  

   3: SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']'

   4:             AS [statement] ,

   5:         i.[name] AS [index_name] ,

   6:         ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups]

   7:             AS [user_reads] ,

   8:         ddius.[user_updates] AS [user_writes] ,

   9:         SUM(SP.rows) AS [total_rows]

  10: FROM    sys.dm_db_index_usage_stats ddius

  11:         INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]

  12:                                      AND i.[index_id] = ddius.[index_id]

  13:         INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]

  14:                                         AND SP.[index_id] = ddius.[index_id]

  15:         INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]

  16:         INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]

  17: WHERE   ddius.[database_id] = DB_ID() -- current database only

  18:         AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1

  19:         AND ddius.[index_id] > 0

  20: GROUP BY su.[name] ,

  21:         o.[name] ,

  22:         i.[name] ,

  23:         ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,

  24:         ddius.[user_updates]

  25: HAVING  ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0

  26: ORDER BY ddius.[user_updates] DESC ,

  27:         su.[name] ,

  28:         o.[name] ,

  29:         i.[name ]

  30:  

2. Identifica los índices non-clustered potencialmente ineficiente. Este indicador es importante para optimizar nuestra base de datos.

   1: --Identifica los indices non-clustered potencialmente ineficiente. O sea las escrituras son mayor a las lecturas.

   2: SELECT  OBJECT_NAME(ddius.[object_id]) AS [Table Name] ,

   3:         i.name AS [Index Name] ,

   4:         i.index_id ,

   5:         user_updates AS [Total Writes] ,

   6:         user_seeks + user_scans + user_lookups AS [Total Reads] ,

   7:         user_updates - ( user_seeks + user_scans + user_lookups )

   8:             AS [Difference]

   9: FROM    sys.dm_db_index_usage_stats AS ddius WITH ( NOLOCK )

  10:         INNER JOIN sys.indexes AS i WITH ( NOLOCK )

  11:             ON ddius.[object_id] = i.[object_id]

  12:             AND i.index_id = ddius.index_id

  13: WHERE   OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1

  14:         AND ddius.database_id = DB_ID()

  15:         AND user_updates > ( user_seeks + user_scans + user_lookups )

  16:         AND i.index_id > 1

  17: ORDER BY [Difference] DESC ,

  18:         [Total Writes] DESC ,

  19:         [Total Reads] ASC ;

3. Identifica los índices que faltan en nuestra base de datos según el orden de ventaja para la optimización.

   1: --Identifica que indices faltantes son mas beneficiosos.

   2:         SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 ) AS [index_advantage] ,

   3:         dbmigs.last_user_seek ,

   4:         dbmid.[statement] AS [Database.Schema.Table] ,

   5:         dbmid.equality_columns ,

   6:         dbmid.inequality_columns ,

   7:         dbmid.included_columns ,

   8:         dbmigs.unique_compiles ,

   9:         dbmigs.user_seeks ,

  10:         dbmigs.avg_total_user_cost ,

  11:         dbmigs.avg_user_impact

  12: FROM    sys.dm_db_missing_index_group_stats AS dbmigs WITH ( NOLOCK )

  13:         INNER JOIN sys.dm_db_missing_index_groups AS dbmig WITH ( NOLOCK )

  14:                     ON dbmigs.group_handle = dbmig.index_group_handle

  15:         INNER JOIN sys.dm_db_missing_index_details AS dbmid WITH ( NOLOCK )

  16:                     ON dbmig.index_handle = dbmid.index_handle

  17: WHERE   dbmid.[database_id] = DB_ID()

  18: ORDER BY index_advantage DESC ;

Existen muchos mas beneficios que le podemos sacar provecho a los DMV’s. Si quieren ver mas vayan al siguiente link http://msdn.microsoft.com/en-us/library/ms188754.aspx

Cualquier pregunta no duden en realizarla.

Saludos.