Verificare dimensione tabelle in SQL Server 2019

Utilizzando SQL Server Management Studio (SSMS) è possibile visualizzare la dimensione di tutte le tabelle presenti all’interno di un database.

In questo post vedremo insieme come verificare dimensione tabelle in SQL Server tramite SSMS (SQL Server Management Studio) e stored procedure messe a disposizione da SQL Server

Verificare dimensione tabelle in SQL Server mediante Report

Conoscere la dimensione tabelle in SQL Server consente di prendere decisioni migliori, ad esempio quando si prevedono i requisiti di spazio su disco o si dà la priorità alla risoluzione delle inefficienze all’interno del database (ad esempio tipi di dati/compressione).

Ci sono molti modi per controllare le dimensioni di una tabella in SQL Server come l’utilizzo dei report di SQL Server.

Il report “Utilizzo del disco” da parte delle tabelle principali in SQL Server consente di ottenere rapidamente le dimensioni di tutte le tabelle all’interno di un database.

Utilizzando SQL Server Management Studio (SSMS) è sufficiente fare clic con il pulsante destro del mouse su un database, selezionare Report, Report standard e Utilizzo disco in base alle tabelle principali.

Il rapporto mostra che questo database ha 2 tabelle di dimensioni vicine a 700 MB, entrambe contenenti lo stesso numero di righe, ma ci sono alcune differenze negli indici.

Dimensioni tabelle in SQL Server mediante query

Alle volte ho bisogno di controllare le dimensioni e l’occupazione di ogni singola tabella di un database per valutare quanto impatta sullo spazio disco.

La stored procedure (sp_spaceused) permette di visualizzare le informazioni richiamandola per ogni singola tabella di cui, però, dobbiamo già conoscere il nome da passargli come parametro.

exec sp_spaceused('table_name')

Se però abbiamo la necessità di visualizzare la dimensioni tabelle in SQL Server di uno specifico database, e magari ordinare l’elenco in base alle dimensioni delle tabelle, dobbiamo utilizzare una query più complessa.

Questa è la query che ci permette di visualizzare le dimensioni tabelle in SQL Server:

-- Script verifica dimensione tabelle in SQL Server


--SET database context
	USE [DataBaseName]
	
	--Declare Variable
	DECLARE @sql VARCHAR(128)
	DECLARE @name VARCHAR(128)

	--Create temporary table #Tables
	CREATE TABLE #Tables(Name VARCHAR(128))
	
	--Insert all TABLE_NAME inside the temporary table #Tables
	SET @sql = 'INSERT #Tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
	EXEC(@sql)
	
	--Create temporary table @SpaceUsed 
	CREATE TABLE #SpaceUsed 
		(Name VARCHAR(128), Rows VARCHAR(11), Reserved VARCHAR(18), Data VARCHAR(18), IndexSize VARCHAR(18), Unused VARCHAR(18))
	
	--Inserts the value in the #SpaceUsed table
	SET @name = ''
	WHILE exists (SELECT * FROM #Tables WHERE Name > @name)
	BEGIN
		SELECT @name = min(Name) FROM #Tables WHERE Name > @name
		SET @sql = 'EXEC ..sp_executesql N''insert #SpaceUsed exec sp_spaceused [' + @name + ']'''
		EXEC(@sql)
	END
	
	--Select and convert all values
	SELECT 
		Name,
		CAST(Rows AS DECIMAL) as Rows,
		CAST(left(Reserved, LEN(Reserved) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Reserved,
		CAST(left(Data, LEN(Data) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Data,
		CAST(left(IndexSize, LEN(IndexSize) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as IndexSize,
		CAST(left(Unused, LEN(Unused) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as Unused
	FROM #SpaceUsed
	ORDER BY Data DESC, Reserved DESC
	
	--Delete temporary tables
	DROP TABLE #tables
	DROP TABLE #SpaceUsed


-- Fine script verifica dimensione tabelle in SQL Server

Il codice è abbastanza commentato in modo da capire i passaggi che vengono effettuati prima di arrivare ad avere l’elenco delle tabelle con le rispettive dimensioni.

Se sei interessato ad altri post inerenti a SQL Server e le sue funzionalità puoi dare uno sguardo qui:

• Come cambiare path file database in Sql Server 2019 – Bartolomeo Alberico
• Installare SQL Server Agent su Ubuntu Server 20.04 – Bartolomeo Alberico
• Database in modalità Suspect in Sql Server 2019 – Bartolomeo Alberico
• Sql Server e MySql: creare un Linked Server – Bartolomeo Alberico
• Deadlock in SQL Server 2019. Come gestirli – Bartolomeo Alberico
• Configurare Server Mail in SQL Server 2016 – Bartolomeo Alberico

Descrizione della query

Per comodità illustro i singoli passaggi per verificare le dimensioni tabelle in SQL Server che la query compie.

Prima cosa indichiamo il database su cui verrà eseguita la query

--SET database context
USE [DataBaseName]

Dichiariamo le variabili che verranno utilizzare

	
	--Declare Variable
	DECLARE @sql VARCHAR(128)
	DECLARE @name VARCHAR(128)

Creiamo la tabella temporanea #Tables e la popoliamo con tutti i nomi delle tabelle presenti nel database (recuperati dalla tabella INFORMATION_SCHEMA.TABLES)

--Create temporary table #Tables
	CREATE TABLE #Tables(Name VARCHAR(128))
	
	--Insert all TABLE_NAME inside the temporary table #Tables
	SET @sql = 'INSERT #Tables SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'''
	EXEC(@sql)

Creiamo la tabella temporanea #SpaceUsed e, ciclando la tabella temporanea #Tables, la popoliamo con i valori di ogni singola tabella recuperati tramite la stored procedure sp_spaceused

--Create temporary table @SpaceUsed 
	
CREATE TABLE #SpaceUsed 
		(Name VARCHAR(128), Rows VARCHAR(11), Reserved VARCHAR(18), Data VARCHAR(18), IndexSize VARCHAR(18), Unused VARCHAR(18))
	
	--Inserts the value in the #SpaceUsed table
	SET @name = ''
	WHILE exists (SELECT * FROM #Tables WHERE Name > @name)
	BEGIN
		SELECT @name = min(Name) FROM #Tables WHERE Name > @name
		SET @sql = 'EXEC ..sp_executesql N''insert #SpaceUsed exec sp_spaceused [' + @name + ']'''
		EXEC(@sql)
	END

Ora non ci resta che effettuare una SELECT sulla tabella temporanea #SpaceUsed in modo da visualizzare tutti i dati delle tabelle del database.
Per una migliore interpretazione dei dati convertiamo tutti i valori in GByte e li ordiniamo dalla più grande in modo decrescente

-Select and convert all values
	SELECT 
		Name,
		CAST(Rows AS DECIMAL) as Rows,
		CAST(left(Reserved, LEN(Reserved) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Reserved,
		CAST(left(Data, LEN(Data) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 AS Data,
		CAST(left(IndexSize, LEN(IndexSize) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as IndexSize,
		CAST(left(Unused, LEN(Unused) - 3) AS DECIMAL(18, 3)) / 1024 / 1024 as Unused
	FROM #SpaceUsed
	ORDER BY Data DESC, Reserved DESC

Al termine della select eliminiamo le tabelle temporanee

--Delete temporary tables
	DROP TABLE #tables
	DROP TABLE #SpaceUsed

Conclusione

Abbiamo visto come verificare dimensione tabelle in SQL Server mediante l’utilizzo di SQL Server Management Studio (SSMS) e della Stored Procedure.

• Xiaomi Redmi Note 7. Il nuovo smartphone della Redmi
• Rimuovere campo mail dal form dei commenti WordPress. Ecco come fare.
• Le pagine errore 404 più divertenti del web. Ecco alcuni esempi.
• Inserire Breadcrumbs WordPress. Ecco 2 modi per farlo.
• Come disinstallare un plugin WordPress manualmente in maniera corretta.