¿Cuáles son las tablas más pesadas de nuestras bases de datos?

Volvemos a la carga con una entrada muy breve y útil que podrás lanzar en todas tus instancias SQL Server:

¿Cuáles son las tablas más pesadas de TODAS tus bases de datos?

Usaremos el procedimiento almacenado de Microsoft , reconocido como «undocumented features of SQL Server”, sp_MSforeachdb y una tabla temporal donde se almacenará la información de todas las tablas de todas las bases de datos del SQL Server.

Empezamos creando la tabla temporal global de la siguiente manera:

CREATE TABLE ##tablas_pesadas(
[NombreBBDD] nvarchar (255),
[Tabla] nvarchar (255),
[Usado_mb] numeric(36, 2)
)

Tras ello, necesitaremos declarar una variable llamada COMMAND en la que vamos a guardar la query a lanzar como parámetro del procedimiento sp_MSforeachdb :

DECLARE @command varchar(1000)

El siguiente paso es guardar la query en la variable definida en el paso anterior:

SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
USE [?];
INSERT INTO ##tablas_pesadas
select top 20 DB_NAME() as NombreBBDD, schema_name(tab.schema_id) + ''.'' + tab.name as [tabla], 
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as Usado_mb
from sys.tables tab
join sys.indexes ind 
     on tab.object_id = ind.object_id
join sys.partitions part 
     on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
     on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + ''.'' + tab.name
order by sum(spc.used_pages) desc;
END'

Y por último, ejecutar el procedimiento sp_MSforeachdb con el parámetro @command:

EXEC sp_MSforeachdb @command

Ya tendríamos toda la información almacenada en la tabla temporal global ##tablas_pesadas. Solo nos quedaría consultarla aplicando filtros en WHERE y ordenándolas de mayor a menor según su tamaño:

SELECT * FROM ##tablas_pesadas
where [Usado_mb] > 50000.00
order by [Usado_mb] desc

En nuestro ejemplo, solo queremos sacar las tablas con mayor tamaño a 50 GB.

Aquí os dejamos la consulta completa para sacarle el máximo provecho a este código:

DECLARE @command varchar(1000) 
CREATE TABLE ##tablas_pesadas(
[NombreBBDD] nvarchar (255),
[Tabla] nvarchar (255),
[Usado_mb] numeric(36, 2)
)
SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') 
BEGIN 
USE [?];
INSERT INTO ##tablas_pesadas
select top 20 DB_NAME() as database_name, schema_name(tab.schema_id) + ''.'' + tab.name as [table], 
    cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb
from sys.tables tab
join sys.indexes ind 
     on tab.object_id = ind.object_id
join sys.partitions part 
     on ind.object_id = part.object_id and ind.index_id = part.index_id
join sys.allocation_units spc
     on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + ''.'' + tab.name
order by sum(spc.used_pages) desc;
END' 

EXEC sp_MSforeachdb @command 

SELECT * FROM ##tablas_pesadas
where [Usado_mb] > 50000.00
order by [Usado_mb] desc

drop table ##tablas_pesadas