Problema del giorno: preso un backup di un database di produzione, una volta fatto il restore sulla mia istanza di Sql Server volevo cancellare tutti i dati non interessanti per l’analisi che dovevo fare. Trattandosi di un database con un numero spropositato di tabelle, volevo concentrare la mia attenzione solo su quelle che mi avrebbero consentito di ottenere un reale guadagno, in termini di spazio occupato. Si trattava quindi di ordinare le tabelle nel database in base al loro spazio occupato.
Ci viene in soccorso la vista di sistema sys.allocation_units
; quest’ultima riporta le dimensioni (espresse in termini di “pagine”) di ciascuna unità di allocazione associata alle tabelle. Il legame viene instaurato tramite la vista sys.partitions
, che abbiamo già incontrato nella puntata precedente.
Come il commento iniziale sembra suggerire, lo script non è effettivamente farina del mio sacco, ma trovato bello fatto su stackoverflow:
/* Author : marc_s Date : 2011.11.25 http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database */ SELECT TableName = tables.NAME, RowCounts = partitions.rows, TotalSpaceKB = SUM( allocation_units.total_pages ) * 8, UsedSpaceKB = SUM( allocation_units.used_pages ) * 8, UnusedSpaceKB = ( SUM( allocation_units.total_pages ) - SUM( allocation_units.used_pages ) ) * 8 FROM sys.tables INNER JOIN sys.indexes ON indexes.object_id = tables.OBJECT_ID AND indexes.OBJECT_ID > 255 INNER JOIN sys.partitions ON indexes.object_id = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id INNER JOIN sys.allocation_units ON allocation_units.container_id = partitions.partition_id WHERE tables.NAME NOT LIKE 'dt%' AND tables.is_ms_shipped = 0 GROUP BY tables.Name, partitions.Rows ORDER BY tables.Name
Questo script è stato da me solo leggermente modificato per comprendere anche lo schema, escludere la tabella dbo.sysdiagrams
(che pur essendo creata da Sql Server, ha il flag is_ms_shipped
impostato a 0) ed infine per ordinare i dati secondo lo spazio usato (decrescente)
SELECT [Schema] = schemas.name, [Table] = tables.name, [RowCounts] = partitions.rows, [TotalSpaceKB] = SUM( allocation_units.total_pages ) * 8, [UsedSpaceKB] = SUM( allocation_units.used_pages ) * 8, [UnusedSpaceKB] = ( SUM( allocation_units.total_pages ) - SUM( allocation_units.used_pages ) ) * 8 FROM sys.tables INNER JOIN sys.schemas ON schemas.schema_id = tables.schema_id INNER JOIN sys.indexes ON indexes.object_id = tables.object_id AND indexes.object_id > 255 INNER JOIN sys.partitions ON indexes.object_id = partitions.object_id AND indexes.index_id = partitions.index_id INNER JOIN sys.allocation_units ON allocation_units.container_id = partitions.partition_id WHERE tables.name != 'sysdiagrams' AND tables.is_ms_shipped = 0 GROUP BY schemas.name, tables.Name, partitions.Rows ORDER BY [UsedSpaceKB] DESC