Mover indices no clustered a un nuevo filegroup

-

Normalmente lo mas común es nunca preocuparse por este tipo de trivialidades y dejamos la configuración por default la cual es lo suficientemente buena en la mayoría de los casos. En esta ocasión quise realizar esta acción para un cliente por varias cuestiones pero que beneficios tiene y porque es un tema sin una respuesta clara.

Bueno en este caso el cliente tenia muchos índices y bastante grandes en general y no podemos hacer mucho por mejorar el desempeño ya que no es posible cambiar codigo ni añadir índices debido a las restricciones del proveedor de servicio de la aplicación alojada en el servidor.

La documentación de Microsoft nos habla de esto, y hasta nos dice como podemos mover un índice a un diferente filegroup pero no nos dice que ganancias o porque hacer esto. Mucho se ve en la red que algunos lo recomiendan, otros dicen que es un efecto placebo y no existe ganancia alguna por lo cual no lo recomiendan.

Como en muchos casos cuando hablamos de bases de datos todo depende de varias cuestiones y no existe una respuesta correcta que se aplique siempre, en mi caso no había ganancia debido a que la SAN es un caja negra para mi y no existe la distribución necesaria para poder considerar que exista una ganancia, no había diferencia de velocidad en las LUNS presentadas ya que tenían la misma configuración y respuesta (use Crystal Disk para esto), tampoco tenían diferencia de raid o tipos de unidades.

Lo que si gane era separar en caso de corrupción saber si la página dañada pertenece realmente a un indice o a una data page más rápido, posiblemente ayudará con los bloqueos aunque sera difícil medir el impacto ya que varios cambios fueron expuestos al mismo tiempo. Una mejor administración lógica de mi base de datos, a la vez la unidad principal donde estaban los data files estaba cerca de consumir el espacio libre disponible debido a un crecimiento inesperado en los últimos meses, al separar los indices pude liberar 170gb de una base de 550gb. Lo cual dará tiempo al área de infraestructura de solventar la situación de su lado.

Buscando en la red encontre este script en el sitio de trycatchfinally el cual no se adecuaba a mis necesidades completamente asi que lo modifique un poco dando la facilidad de elegir un schema ya que no tenía esa facilidad originalmente. 

El script original esta en sqlservercentral
(http://www.sqlservercentral.com/scripts/Miscellaneous/31541/)

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
/*
 Obtain from:  http://www.trycatchfinally.net/2010/02/Migrate-database-indexes-to-a-new-file-group/
 @NewFilegroup  Name of the filegroup to which you'll be moving the indexes.
 @ROWCNT   Migrate only indexes with this many rows. Allows for a staged migration, moving the smaller indexes first
    and saving larger ones for a scheduled move later.
 @DEBUG   When set to "1", generates a script without actually moving anything. This can be used to preview what
    will be done. Setting this to "0" will cause this script to actually migrate the indexes. 
*/

DECLARE @NewFilegroup NVARCHAR(100)
DECLARE @ROWCNT INTEGER
DECLARE @DEBUG BIT
DECLARE @SCHEMA NVARCHAR(100)

SET @NewFilegroup = 'NombreIndice'
SET @ROWCNT=25000000
SET @DEBUG=0
SET @SCHEMA = 'dbo' 

declare @id integer
declare @tbname nvarchar(100)
declare @indid integer
declare @indname nvarchar(100)
declare @fill integer
declare @unique VARCHAR(20)
declare @group integer
declare @column_list nvarchar(4000)
declare @include_list nvarchar(4000)
declare @strsql_d nvarchar(4000)
declare @strsql_c nvarchar(4000)
declare @strsql_a nvarchar(4000)


declare curs1 cursor for
     SELECT so.object_id,
   so.name,
   si.index_id,
   si.name AS indname,
   CASE si.Fill_Factor WHEN 0 THEN 90 ELSE si.Fill_Factor END,
   CASE si.Is_Unique WHEN 0 THEN '' WHEN 1 THEN ' UNIQUE ' END,
   si.data_space_id
    FROM sys.indexes si
    JOIN sys.objects so
   ON si.object_id = so.object_id
    JOIN sys.dm_db_index_usage_stats us
   ON si.object_id = us.object_id
  AND si.index_id = us.index_id
    JOIN sys.partitions p
      on so.object_id = p.object_id
   WHERE so.object_id > 100
  AND so.type = 'U'
  AND si.index_id BETWEEN 2 AND 254 -- Skip primary keys
  and si.is_disabled = 0 
  and si.type = 2      -- Non-clustered only
  and si.data_space_id=1
  and p.index_id = 0
  --AND p.rows<=@ROWCNT
   ORDER BY so.name, si.name

open curs1
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@unique,@group

while @@fetch_status=0
begin

SET @Column_List=''
SET @Include_List = ''

  SELECT @Column_List  = @Column_List  + CASE is_included_column WHEN 0 THEN '['+ c.name+'],' ELSE '' END,
   @Include_List = @Include_List + CASE is_included_column WHEN 1 THEN '['+ c.name+'],' ELSE '' END
    FROM sys.indexes si
    JOIN sys.objects so
   ON si.object_id = so.object_id
    JOIN sys.index_columns ic
   ON si.object_id = ic.object_id
  AND si.index_id = ic.index_id
    JOIN sys.columns c
      ON ic.object_id = c.object_id
  AND ic.column_id = c.column_id
   WHERE so.type = 'U'
  AND si.index_id = @indid
  AND so.object_id = @id
   ORDER BY ic.index_column_id

SET  @Column_List=left(@Column_List,len(@Column_List)-1)

IF LEN(@Include_List) > 0
BEGIN
 SET @Include_List = left(@Include_List,len(@Include_List)-1)
 SET @Include_List = ' INCLUDE ('+@Include_List+') '
END

 begin
  set @strsql_c='CREATE '+@Unique+' INDEX ['+@indname+'2] ON ['+@SCHEMA+'].['+@tbname+']('+@Column_List+') '+@Include_List+' WITH FILLFACTOR = '+CONVERT(VARCHAR(5), @fill)+' ON ['+@NewFilegroup+']'
  set @strsql_d='drop index ['+@SCHEMA+'].['+@tbname+'].['+@indname+']'
  set @strsql_a='exec sp_rename ''['+@SCHEMA+'].['+@tbname+'].['+@indname+'2]'', '''+@indname+''', ''INDEX'''
  
  IF @DEBUG=1
  BEGIN
   print @strsql_c
   print @strsql_d
   print @strsql_a
   print ''
  END

  IF @DEBUG=0
  BEGIN
   BEGIN TRY
    exec sp_executesql @strsql_c
    exec sp_executesql @strsql_d
    exec sp_executesql @strsql_a
    PRINT '['+@tbname+'].['+@indname+'] moved successfully'
    PRINT ''
   END TRY
   BEGIN CATCH
    PRINT '***PROBLEM MIGRATING THIS INDEX***'
    PRINT ''
   END CATCH
  END
  
 end
fetch next from curs1 into @id,@tbname,@indid,@indname,@fill,@unique,@group
end
close curs1
deallocate curs1

Comentarios

Publicar un comentario

Entradas más populares de este blog

Database mail con cuentas de Office 365

-
Imagen
El uso de alertas, es uno de los primeros pasos que debemos de tomar para poder alertarnos de problemas o alertas importantes sobre el estatus de la base de datos de manera pro-activa, aunque es posible llegar y en la mañana revisar el estado de nuestras instancias lamentablemente este método puede ser tardado y poco eficaz.
Leer más