5/10/2018 12:11:00 a. m.
Share:
Mostrando entradas con la etiqueta indices. Mostrar todas las entradas
Mostrando entradas con la etiqueta indices. Mostrar todas las entradas
jueves, 10 de mayo de 2018
Listar las tablas de una base de datos, su tamaño en disco, de los datos e indices
Para listar las tablas de una base de datos con información de su tamaño en disco, de los datos e indices y del número de filas se puede hacer uso del siguiente script:
indices
sql server
--
--
--
--Cursor que contiene todos los objetos que ocupan espacio
DECLARE objects_cursor CURSOR LOCAL FAST_FORWARD READ_ONLY for
SELECT s.name + '.' + o.name from sys.schemas s
INNER JOIN sys.objects o
ON o.schema_id = s.schema_id
WHERE
o.type = 'S' or --Tablas de sistema
o.type = 'U' or --Tablas de usuario
o.type = 'V' or --Vistas (solo las indexadas devuelven tamaño)
o.type = 'SQ' or --Cola de servicio
o.type = 'IT' -- Tablas internas usadas p.e. por el Service Broker o los indices XML
--Tabla temporal para albergar los resultados
CREATE TABLE #results
(name_with_schema SYSNAME,
name SYSNAME, rows CHAR(11),
reserved VARCHAR(18), data VARCHAR(18),
index_size VARCHAR(18),Unused VARCHAR(18))
DECLARE @resultado AS TABLE
(name SYSNAME, rows CHAR(11),
reserved VARCHAR(18), data VARCHAR(18),
index_size VARCHAR(18),Unused VARCHAR(18))
--Recorremos el cursor obteniendo la información de espacio ocupado
DECLARE @object_name AS SYSNAME
OPEN objects_cursor
FETCH NEXT FROM objects_cursor
INTO @object_name;
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @resultado
EXEC sp_spaceused @object_name
INSERT INTO #results
SELECT @object_name, x.*
from @resultado as x
delete from @resultado
FETCH NEXT FROM objects_cursor
INTO @object_name;
END;
CLOSE objects_cursor;
DEALLOCATE objects_cursor;
-- Quitamos "KB" para poder ordenar
UPDATE
#results
SET
reserved = LEFT(reserved,LEN(reserved)-3),
data = LEFT(data,LEN(data)-3),
index_size = LEFT(index_size,LEN(index_size)-3),
Unused = LEFT(Unused,LEN(Unused)-3)
--Ordenamos la información por el tamaño ocupado
SELECT
name_with_schema,
Name,
reserved AS [Tamaño en Disco (KB)],
data AS [Datos (KB)],
index_size AS [Indices (KB)],
Unused AS [No usado (KB)],
Rows AS Filas FROM #results
ORDER BY
name_with_schema,
name,
CONVERT(bigint, reserved) DESC
drop table #results
Posted by InfoTacticas Soluciones | No comments
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.
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:
indices
sql server
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')
Posted by InfoTacticas Soluciones | No comments
4/27/2018 07:48:00 a. m.
Share:
domingo, 4 de febrero de 2018
Reorganizar y volver a generar índices - optimizar la consulta de datos - PARTE 1
Los indices de una tabla, por ejemplo la tabla Department del esquema HumanResources de la base de datos AdventureWorks2012, se pueden listar con la siguiente instrucción:
Cada vez que se realizan operaciones de inserción, actualización o eliminación de los datos de una tabla, el motor de base de datos de SQL Server da mantenimiento a los indices de dicha tabla. Sin embargo, con el tiempo la información del índice se dispersa por la base de datos, es decir, se fragmenta, ocasionando que la ordenación lógica que está basada en el valor de clave no coincida con la ordenación física dentro del archivo de datos. Cuando los índices están muy fragmentados hay mayor probabilidad de que las consultas a los datos de la tabla sean lentas. La fragmentación del índice se soluciona reorganizándolo o volviéndolo a generar. El proceso de volver a generar un índice quita y vuelve a crear el índice. Para saber si los índices de una tabla están fragmentados podemos usar la siguiente consulta:
Para reorganizar un índice en una tabla se debe emplear la siguiente instrucción:
Para reconstruir un índice en una tabla se debe emplear la siguiente instrucción:
Para volver a reconstruir todos los índices de una tabla determinada usar:
Para obtener información más técnica acerca de la fragmentación de los índices, puede consultar el siguiente enlace: https://technet.microsoft.com/es-es/library/ms189858(v=sql.110).aspx. En esta página se menciona que la decisión de si se reorganiza o se reconstruye un índice de una tabla depende del porcentaje de fragmentación, si está entre un 5% y 30% es mejor REORGANIZARLO, en caso sea mayor al 30% de fragmentación entonces es mejor RECONSTRUIRLO, si es menor a un 5% no es conveniente aplicarle ningún tratamiento. A continuación he elaborado un script que permite reorganizar reconstruir o dejar intacto los índices de una tabla dada en función a la recomendación mencionada del porcentaje de fragmentación.
Utilizo RAISERROR tan solo para ver los mensajes durante la ejecución del WHILE, porque de otra manera los mensajes se muestran al final del while o después de cierto tiempo, por ello el RAISEERROR muestra el mensaje inmediatamente.
indices
sql server
-- -- use AdventureWorks2012 GO; SELECT s.name, tab.name as tablename, ind.name as indexname FROM sys.indexes ind INNER JOIN sys.tables tab ON ind.object_id = tab.object_id INNER JOIN sys.schemas s on tab.schema_id = s.schema_id where tab.name = 'Department' and s.name = 'HumanResources' ORDER BY s.name,tab.name
Cada vez que se realizan operaciones de inserción, actualización o eliminación de los datos de una tabla, el motor de base de datos de SQL Server da mantenimiento a los indices de dicha tabla. Sin embargo, con el tiempo la información del índice se dispersa por la base de datos, es decir, se fragmenta, ocasionando que la ordenación lógica que está basada en el valor de clave no coincida con la ordenación física dentro del archivo de datos. Cuando los índices están muy fragmentados hay mayor probabilidad de que las consultas a los datos de la tabla sean lentas. La fragmentación del índice se soluciona reorganizándolo o volviéndolo a generar. El proceso de volver a generar un índice quita y vuelve a crear el índice. Para saber si los índices de una tabla están fragmentados podemos usar la siguiente consulta:
--
--
SELECT a.index_id, name as nombre_indice, avg_fragmentation_in_percent as porcentaje_fragmentacion
FROM sys.dm_db_index_physical_stats (DB_ID(N'BD_TRIBUTACION'),
OBJECT_ID(N'dbo.Cta_Corriente'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
-- o en AdventureWorks2012
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'),
OBJECT_ID(N'HumanResources.Department'), NULL, NULL, NULL) AS a
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO
Para reorganizar un índice en una tabla se debe emplear la siguiente instrucción:
-- -- ALTER INDEX [NombreDelIndice] ON [schema].[TablaDondeEstaElIndice] REORGANIZE
Para reconstruir un índice en una tabla se debe emplear la siguiente instrucción:
-- -- ALTER INDEX [NombreDelIndice] ON [schema].[TablaDondeEstaElIndice] REBUILD
Para volver a reconstruir todos los índices de una tabla determinada usar:
-- -- USE BD_TRIBUTACION; GO -- Reorganizar todos los índices de la tabla dbo.Cta_corriente. ALTER INDEX ALL ON dbo.Cta_corriente REBUILD; GO
Para obtener información más técnica acerca de la fragmentación de los índices, puede consultar el siguiente enlace: https://technet.microsoft.com/es-es/library/ms189858(v=sql.110).aspx. En esta página se menciona que la decisión de si se reorganiza o se reconstruye un índice de una tabla depende del porcentaje de fragmentación, si está entre un 5% y 30% es mejor REORGANIZARLO, en caso sea mayor al 30% de fragmentación entonces es mejor RECONSTRUIRLO, si es menor a un 5% no es conveniente aplicarle ningún tratamiento. A continuación he elaborado un script que permite reorganizar reconstruir o dejar intacto los índices de una tabla dada en función a la recomendación mencionada del porcentaje de fragmentación.
--
--
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'
into #reorganizar_reindexar
FROM sys.dm_db_index_physical_stats (DB_ID('N'BD_TRIBUTACION'), OBJECT_ID(N'dbo.cta_corriente'), 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
--select * from #reorganizar_reindexar
DECLARE @i int = 1, @ix int
declare @nombre_indice varchar(200), @nombre_tabla varchar(200), @accion varchar(10)
declare @sql nvarchar(200)=''
select top 1 @i = index_id, @nombre_tabla = nombre_tabla, @nombre_indice = nombre_indice, @accion = accion
from #reorganizar_reindexar
order by index_id
while @i 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' --WITH (ONLINE = ON);
end
else
begin
set @sql = 'ALTER INDEX '+ @nombre_indice + ' ON dbo.' + @nombre_tabla + ' REBUILD' --WITH (ONLINE = ON);;
end
--RAISERROR(@sql, 0, 1) WITH NOWAIT
Print @sql
RAISERROR(N'', 0, 1) WITH NOWAIT
execute sp_executesql @sql
set @ix = null
select top 1 @ix = index_id, @nombre_tabla = nombre_tabla, @nombre_indice = nombre_indice, @accion = accion
from #reorganizar_reindexar
where index_id > @i
order by index_id
set @i = @ix
end
drop table #reorganizar_reindexar
Utilizo RAISERROR tan solo para ver los mensajes durante la ejecución del WHILE, porque de otra manera los mensajes se muestran al final del while o después de cierto tiempo, por ello el RAISEERROR muestra el mensaje inmediatamente.
Posted by InfoTacticas Soluciones | No comments
2/04/2018 04:30:00 p. m.
Share:
