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')
4/27/2018 07:48:00 a. m.
Share:
0 comentarios: