Blog
home > blog > Dimensioni di tutte le tabelle di un database SQL Server

Tag: SQL Server, database, TSQL, stored procedure

Dimensioni di tutte le tabelle di un database SQL Server

Vediamo una stored procedure per calcolare le dimensioni, in numero di record ed in occupazione su disco, di dati ed indici di ogni tabella di un database su SQL Server.
di Maurizio Ceravolo
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
Post correlati
2010
28
Ottobre
Abilitare e disabilitare i vincoli su un database SQL Server
Maurizio Ceravolo
Due stored procedure per abilitare e disabilitare i vincoli su un database SQL Server.
2010
26
Novembre
Modificare l'owner delle tabelle di un database su SQL Server
Maurizio Ceravolo
Vediamo un semplice script per generare l'sql per modificare l'owner di tutte le tabelle di un database in SQL Server.
2011
18
Marzo
TSQL: convertire un numero in esadecimale
Maurizio Ceravolo
Alle volte c'è la necessità di convertire un numero in esadecimale direttamente dalla fonte dati per generare dei codici colore da usare direttamente su una pagina web.
2010
9
Dicembre
TSQL: concatenare più record senza usare un cursore
Maurizio Ceravolo
Concatenare più record di una tabella in un'unica stringa necessita un cursore, che però è poco performante. Vediamo un metodo tramite FOR XML PATH per concatenare con una query secca.
2011
12
Ottobre
Ebook gratuito su SQL Server "Denali"
Maurizio Ceravolo
Microsoft distribuisce in anteprima e gratuitamente un estratto del libro digitale su SQL Server "Denali". Il libro completo sarà disponibile in primavera 2012.


Archivio Mensile Blog
Tag Cloud
Apple comunicazione Dualboot Facebook geek Google Google+ HTC humor innovazione iPad iPhone marketing Microsoft mobile social network utility video videogiochi Windows Phone
Ultimi Post
I migliori di oggi
I più popolari del mese
Top post