InfoTácticas Soluciones

viernes, 27 de abril de 2018

Reorganizar y volver a generar índices - optimizar la consulta de datos - PARTE 2

En mi entrada: http://infotacticassoluciones.blogspot.pe/2018/02/reorganizar-y-volver-generar-indices.html muestro como reorganizar o reconstruir todos los indices de una tabla determinada de manera automática, sin tener que escribir indice por indice y con la posibilidad de que si se agregan más indices, se tenga que editar el script que reorganiza los indices para incluir los nuevos.
En esta oportunidad, extiendo esta funcionalidad para considerar un conjunto de tablas dadas y que el script se encargue de reorganizar o reconstruir, según sea el caso, todos los índices que posea.

--
--
create procedure PROC_REINDEXA_TABLAS
as
begin

set nocount on

-- Reduce el tamaño de los archivos de datos y de registro de la base de datos especificada.
DBCC SHRINKDATABASE(N'tempdb')

CREATE TABLE #tablas_reindexar (
  ID smallint identity,
  Nombre_Tabla varchar(200)
) 

insert into #tablas_reindexar values('dbo.cta_corriente')
insert into #tablas_reindexar values('dbo.transaccion')
insert into #tablas_reindexar values('dbo.descuento_cta')
insert into #tablas_reindexar values('dbo.decremento_cta')
insert into #tablas_reindexar values('dbo.DOCUMENTO')
insert into #tablas_reindexar values('dbo.DETALLE_DOCUMENTO')
insert into #tablas_reindexar values('dbo.PAGO')
insert into #tablas_reindexar values('dbo.DETALLE_PAGO')
insert into #tablas_reindexar values('dbo.RESOLUCION')
insert into #tablas_reindexar values('dbo.PERSONA')
insert into #tablas_reindexar values('dbo.INTERES')

CREATE TABLE #reorganizar_reindexar (
 Id int identity,
 Index_ID int,
 NOMBRE_INDICE varchar(200),
 NOMBRE_TABLA varchar(200),
 PORCENTAJE_FRAGMENTACION decimal(10,2),
 ACCION varchar(2)
)

DECLARE @DB_NAME nvarchar(100) = ( select DB_NAME() )



-- LLENAR a la tabla #reorganizar_reindexar los indices de las tablas especificadas, 
-- el porcentaje de fragmentación y la acción que corresponde de REORGANIZAR O RECONSTRUIR
DECLARE @nombre_tabla varchar(200) = null
DECLARE @id_tabla smallint = 0

SELECT TOP 1 @id_tabla = ID, @nombre_tabla = nombre_tabla 
from #tablas_reindexar
where ID > @id_tabla 
order by ID


WHILE @nombre_tabla is not null
BEGIN
 
 insert into #reorganizar_reindexar (Index_ID, NOMBRE_INDICE, NOMBRE_TABLA, PORCENTAJE_FRAGMENTACION, ACCION)
 SELECT a.index_id, name as nombre_indice, OBJECT_name(a.object_id) as nombre_tabla,
 avg_fragmentation_in_percent as porcentaje_fragmentacion, 
 case when avg_fragmentation_in_percent <= 30 then 'RO' -- REORGANIZAR
 else 'RI' -- REINDEXAR 
 END as 'ACCION'
 FROM sys.dm_db_index_physical_stats (DB_ID(@DB_NAME), OBJECT_ID(@nombre_tabla), NULL, NULL, NULL) AS a
  JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
 where avg_fragmentation_in_percent >= 5 
 and name <> ''

 set @nombre_tabla = null

 SELECT TOP 1  @id_tabla = ID, @nombre_tabla = nombre_tabla from #tablas_reindexar
 where ID > @id_tabla 
 order by ID

 --Print 'ID = ' + convert(varchar, isnull(@id_tabla,0)) + ', ' + @nombre_tabla
END

DECLARE @i int = 1
declare @nombre_indice varchar(200), @accion varchar(10)
declare @sql nvarchar(200)=''

set @nombre_tabla = null

select top 1 @i = ID, @nombre_tabla = nombre_tabla, @nombre_indice = nombre_indice, @accion = accion
from #reorganizar_reindexar
order by ID


while @nombre_tabla is not null
begin
 --RAISERROR(@i, 0, 1) WITH NOWAIT
 Print @i
 RAISERROR(N'', 0, 1) WITH NOWAIT

 if @accion = 'RO'
 begin
  set @sql = 'ALTER INDEX ' + @nombre_indice + ' ON dbo.' + @nombre_tabla + ' REORGANIZE'
 end 
 else
 begin
  set @sql = 'ALTER INDEX '+  @nombre_indice + ' ON dbo.' + @nombre_tabla + ' REBUILD' + ' WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON)'
 end
 
 --RAISERROR(@sql, 0, 1) WITH NOWAIT
 Print @sql
 RAISERROR(N'', 0, 1) WITH NOWAIT
 execute sp_executesql @sql
  
 set @nombre_tabla = null

 select top 1 @i = ID, @nombre_tabla = nombre_tabla, @nombre_indice = nombre_indice, @accion = accion
 from #reorganizar_reindexar
 where ID > @i
 order by ID

end

set nocount off

drop table #reorganizar_reindexar
drop table #tablas_reindexar
end

Finalmente, para saber a que grupo de archivos de base de datos pertenecen los índices de una tabla, por ejemplo para la tabla Department del esquema HumanResources de la base de datos AdventureWorks2012, entonces se necesita usar el siguiente script:

--
--
use AdventureWorks2012
GO;

-- https://www.lawebdelprogramador.com/foros/SQL/615668-Como-ver-en-que-filegroup-esta-la-tabla.html
select si.name, sf.fileid, sf.name as 'nombre file', sfg.groupid, sfg.groupname 
from sysindexes si inner join sysfiles sf on si.groupid = sf.groupid 
inner join sysfilegroups sfg on sf.groupid = sfg.groupid 
where si.id = object_id('HumanResources.Department')

indices sql server

Posted by InfoTacticas Soluciones | No comments

4/27/2018 07:48:00 a. m. Share: