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.
L'occupazione su disco è dato, non solo dai dati presenti sulla tabella, ma anche dagli indici, che potrebbero anche occupare uno spazio non indifferente.
I motivi per cui ci si dovrebbe preoccupare dello spazio occupato dal database e dai suoi componenti sono a mio avviso tre.
- Se si sta usando SQL Server Express (la versione gratuita) la dimensione massima è di 4GB per SQL Server 2005 e 10GB per SQL Server 2008 R2. Oltre non si può andare. La maggioranza dei progetti non necessitano di memorizzare un numero così elevato di dati, però in alcuni casi si. In questi casi bisogna andare a vedere tabella per tabella, per vedere quali sono quelle che sono più onerose in termini di spazio.
- Se il database sta su una macchina condivisa è probabile che abbiamo delle quote per lo spazio a disposizione. Anche in questo caso vedere l'occupazione tabella per tabella ci permette di vedere dove è il caso di ottimizzare lo spazio.
- Un database è una struttura logica che però è memorizzata su un supporto fisico. Ogni dispositivo di memorizzazione ha delle velocità di lettura e scrittura finite. Ci sono dei limiti quindi più o meno grandi che possono essere aumentati passando a RAID di dischi o utilizzando dischi SSD, però non possono portarci a velocità infinite. Quindi più sono grandi le tabelle, più tempo ci vorrà per interrogarle e aggiornare i dati. Ovviamente utilizzando gli indici ed ottimizzando query e stored procedure si possono migliorare i tempi di risposta, ma anche questo caso non in maniera infinita. Lavorare su un database di un giga è comunque più prestazionale che lavorare su uno di 10. Andare a fare le pulci su ogni tabella ci permette quindi di capire dove sprechiamo più spazio. Ad esempio non è il caso di assegnare 100 caratteri al campo telefono su una tabella di 10 milioni di record.
Per analizzare le dimensioni di tutte le tabelle ho scritto una semplice
stored procedure che elenca le tabelle di un database e per ognuna riporta il numero di record, e gli spazi riservati ed occupati per dati ed indici.
La differenza fra riservato ed occupato è che il database riserva ad una tabella un po' più di spazio rispetto al necessario per eventuali nuovi dati. Quindi può essere che una tabella occupi un megabyte anche se usa effettivamente 500k. È come dire che tengo un armadio mezzo vuoto e se lo riempio aggiungo un anta.
Ecco la stored procedure per effettuare questo calcolo. L'ho provata anche su un database da 10 gigabyte, la risposta è quasi istantanea. Potete usare la stored procedure dove e come volete, senza chiederci il permesso. L'unica richiesta è di lasciare il commento con l'indicazione dell'origine.
CREATE PROCEDURE [dbo].[CalculateTableSize]
AS
BEGIN
-- realizzato da - 2011
SET NOCOUNT ON;
CREATE TABLE #FrameworkTableSize(
[TableName] [varchar](100) NULL,
[RowNumber] [int] NULL,
[KBReservedSize] varchar(100) NULL,
[KBDataSize] varchar(100) NULL,
[KBIndexSize] varchar(100) NULL,
[KBUnusedSize] varchar(100) NULL
)
declare DBObjectCursor cursor for select 'dbo.' + name from dbo.sysobjects where xtype = 'u'
declare @TableName varchar(100)
open DBObjectCursor
fetch next from DBObjectCursor into @TableName
while @@fetch_status = 0
begin
insert into #FrameworkTableSize (TableName, RowNumber , KBReservedSize, KBDataSize, KBIndexSize , KBUnusedSize)
exec sp_spaceused @TableName
fetch next from DBObjectCursor into @TableName
end
close DBObjectCursor
deallocate DBObjectCursor
SELECT * FROM #FrameworkTableSize
END