Hitta de största tabellerna

Ibland vill man veta vilka tabeller som tar upp utrymmet i en vissa databas, till exempel om du upplever ökad tillväxt eller helt enkelt vill ta reda på om det finns stora tabeller som du borde städa ut historiskt data ur. Det är ju så att 1 GB data i din databas lätt kan kräva 10 GB lagringsutrymme totalt, om man räknar  in backup utrymme, redundans på disknivå, test och utvecklingsmiljöer och så vidare.

Ett enkelt sätt att titta på detta är att högerklicka på databasen och välja Reports -> Standard Reports -> Disk Usage By Top Tables Du får då en rapport som ser ut såhär:

Om du vill göra detta lite mer automatiserat, och inte högerklicka för mycket, kan du givetvis skriva ihop ett script för detta. Eller så kan du köra Profiler, och fånga frågan som rapporten ovan kör, och anpassa den lite. Då kan du få ut en kodsnutt som ser ut ungefär som den nedan, som du sedan kan anpassa för eget behov:

SELECT TOP 10    (row_number() over(order by (a1.reserved + ISNULL(a4.reserved,0)) desc))%2 as l1,   

 a3.name AS [schemaname],    a2.name AS [tablename],    a1.rows as row_count,    

(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,    a1.data * 8 AS data,   

 (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data 

THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,   

(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used 

THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used 

ELSE 0 END) * 8 AS unused    

FROM    

(SELECT    ps.object_id,    SUM (    CASE    WHEN (ps.index_id < 2) THEN row_count    ELSE 0    END    ) AS [rows],

 SUM (ps.reserved_page_count) AS reserved,    

SUM (    CASE    WHEN (ps.index_id < 2) 

THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)    

ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)    

END    ) AS data,    

SUM (ps.used_page_count) AS used    

FROM sys.dm_db_partition_stats ps    

GROUP BY ps.object_id) AS a1   

LEFT OUTER JOIN   

 (SELECT    it.parent_id,    SUM(ps.reserved_page_count) AS reserved,    SUM(ps.used_page_count) AS used   

 FROM sys.dm_db_partition_stats ps    

INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)    

WHERE it.internal_type IN (202,204)    

GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)    

INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )    

INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)    

WHERE a2.type <> N'S' and a2.type <> N'IT'

Till exempel kan informationen sparas till en tabell för att kunna gå bakåt och titta på historisk information, för att kunna se trender.