Tamaño de tablas en SQL Server

Tamaño de tablas SQL Server

En una base de datos en SQL Server la información es almacenada en las tablas, estas pueden llegar a convertirse en tablas grandes y es necesario considerar una partición horizontal en tablas que son grandes. (Ver partición Horizontal de tablas)

En este artículo se va a mostrar como listar las tablas y su tamaño de la base de datos abierta, la idea es tener un listado de las tablas ordenadas por el tamaño que ocupan en el disco, una vez que se obtiene el listado de las tablas se pueden tomar decisiones que permitan aumentar la efectividad de las consultas.

Procedimiento almacenado sp_spaceused

Este procedimiento permite mostrar el espacio que ocupa una tabla.
Usando la base de datos AdventureWorks
Use AdventureWorks
go

Espacio de la tabla Address del esquema Person

Execute sp_Spaceused ‘Person.Address’
go

El resultado en la imagen muestra el nombre de la tabla, la cantidad de registros, el espacio total reservado para la tabla, el espacio que ocupa los datos, el espacio de los índices y el espacio no utilizado. El resultado se muestra con la unidad de medida en KB.

El ejercicio va a listar todas las tablas de la base de datos y las almacenará en un cursor, luego usando el procedimiento almacenado sp_spaceused se va a llenar una tabla temporal. Para ubicar mejor la tabla se va a incluir el nombre del esquema.

Para más información ver:
Tablas
Índices
Joins en Consultas
Cursores
Procedimientos almacenados
Actualizar registros
Tablas temporales
Partición horizontal de tablas

Ver el tamaño de las tablas en AdventureWorks

Use AdventureWorks
go

Set nocount on
DECLARE cursorTablas Cursor for
SELECT S.name + ‘.’ + O.name, S.name
from sys.schemas As S
Join sys.objects As O on O.schema_id = S.schema_id
Where O.type = ‘U’
— Tabla temporal para los resultados de las tablas de la BD
— Tipo de dato sysname se utiliza para los nombres de las tablas.
Drop table if exists #Tablas
Create table #Tablas
(TablaNombre sysname,
TablaRegistros nvarchar(50),
TablaReservado nvarchar(20),
TablaDatos nvarchar(20),
TablaIndice nvarchar(20),
TablaNoUsado nvarchar(20),
TablaEsquema nvarchar(128))
–Recorremos el cursor obteniendo la información de espacio ocupado
Declare @NombreTablaConEsquema As Sysname, @NombreEsquema As nvarchar(128)
Open cursorTablas
Fetch cursorTablas into @NombreTablaConEsquema, @NombreEsquema
While (@@FETCH_STATUS = 0)
Begin
— Print @NombreTabla + space(10)+ @NombreEsquema
Insert into #Tablas
(TablaNombre, TablaRegistros, TablaReservado,
TablaDatos, TablaIndice , TablaNoUsado )
Execute sp_spaceused @NombreTablaConEsquema
— Actualizar el nombre del esquema
Update #Tablas
set TablaEsquema = Left(@NombreTablaConEsquema,CHARINDEX(‘.’,@NombreTablaConEsquema)-1)
where TablaNombre = Right(@NombreTablaConEsquema,Len(@NombreTablaConEsquema)-CHARINDEX(‘.’,@NombreTablaConEsquema))
Fetch cursorTablas into @NombreTablaConEsquema , @NombreEsquema
End
Close cursorTablas
Deallocate cursorTablas
— Los tamaños aparecen con la unidad «KB», se van a eliminar los
— tres últimos caracteres para poder obtener los valores.
UPDATE
#Tablas
SET
TablaReservado = LEFT(TablaReservado,LEN(TablaReservado)-3),
TablaDatos = LEFT(TablaDatos,LEN(TablaDatos)-3),
TablaIndice = LEFT(TablaIndice,LEN(TablaIndice)-3),
TablaNoUsado = LEFT(TablaNoUsado,LEN(TablaNoUsado)-3)
–Ordenamos la información por el tamaño ocupado
SELECT
TablaNombre As ‘Nombre de la tabla’,
TablaEsquema As ‘Esquema’,
TablaReservado As ‘Tamaño en Disco’,
TablaRegistros As ‘Cantidad de registros’,
TablaDatos As ‘Espacio datos’,
TablaIndice As ‘Espacio de índices’,
TablaNoUsado As ‘No usado’
From #Tablas
ORDER BY Convert(Numeric(9,2),TablaReservado) Desc
go

El resultado se muestra en la figura siguiente

Importante:
Para extraer por separado el nombre de la tabla y el nombre del esquema se ha utilizado la función Charindex para buscar el punto que separa ambos nombres guardados en el cursor en la variable @NombreTablaConEsquema

El código siguiente muestra como separar ambos nombres, considerando que la tabla se almacena en la variable @Dato, el esquema es RecursosHumanos y la tabla es Empleados

Declare @Dato nvarchar(100) = ‘RecursosHumanos.Empleados’
select CHARINDEX(‘.’,@Dato) As ‘Ubicación del punto’
Select Left(@Dato,CHARINDEX(‘.’,@Dato)-1) As ‘Nombre del Esquema’
Select Right(@Dato,Len(@Dato)-CHARINDEX(‘.’,@Dato)) As ‘Nombre de la Tabla’
go