Frage Größe aller Tabellen in der Datenbank ermitteln


Ich habe eine ziemlich große SQL Server-Datenbank geerbt. Es scheint mehr Speicherplatz zu beanspruchen, als ich aufgrund der darin enthaltenen Daten erwarten würde.

Gibt es eine einfache Möglichkeit zu bestimmen, wie viel Speicherplatz auf der Festplatte jede Tabelle verbraucht?


896
2017-10-25 16:14


Ursprung


Antworten:


SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

1883
2017-10-25 16:16



Wenn Sie verwenden SQL Server-Verwaltungsstudio (SSMS), anstatt eine Abfrage auszuführen (was in meinem Fall doppelte Zeilen zurückgegeben hat) Sie können ein Standardbericht.

  1. Klicken Sie mit der rechten Maustaste auf die Datenbank
  2. Navigieren Sie zu Berichte> Standardberichte> Festplattennutzung nach Tabelle

Hinweis: Der Datenbankkompatibilitätsgrad muss auf 90 oder höher eingestellt sein, damit dies ordnungsgemäß funktioniert. Sehen http://msdn.microsoft.com/en-gb/library/bb510680.aspx


402
2018-04-24 13:46



sp_spaceused kann Informationen über den Speicherplatz erhalten, der von einer Tabelle, einer indizierten Sicht oder der gesamten Datenbank verwendet wird.

Beispielsweise:

USE MyDatabase; GO

EXEC sp_spaceused N'User.ContactInfo'; GO

Dies meldet die Informationen zur Datenträgerverwendung für die ContactInfo-Tabelle.

Um dies für alle Tabellen gleichzeitig zu verwenden:

USE MyDatabase; GO

sp_msforeachtable 'EXEC sp_spaceused [?]' GO

Sie können die Datenträgerverwendung auch über die Funktion Standardberichte von SQL Server mit der rechten Maustaste abrufen. Um zu diesem Bericht zu gelangen, navigieren Sie vom Server-Objekt im Objekt-Explorer zu dem Datenbank-Objekt und klicken Sie mit der rechten Maustaste auf eine beliebige Datenbank. Wählen Sie im angezeigten Menü Berichte, dann Standardberichte und dann "Festplattennutzung nach Partition: [Datenbankname]".


78
2017-10-25 16:24



 exec  sp_spaceused N'dbo.MyTable'

Für alle Tabellen, verwenden Sie .. (Hinzufügen von den Kommentaren von Paul)

exec sp_MSForEachTable 'exec sp_spaceused [?]'

26
2017-10-25 16:17



Nach einigem Suchen konnte ich keine einfache Möglichkeit finden, Informationen zu allen Tabellen zu erhalten. Es gibt eine praktische gespeicherte Prozedur namens sp_spaceused, die den gesamten von der Datenbank belegten Speicherplatz zurückgibt. Wenn sie mit einem Tabellennamen versehen ist, gibt sie den von dieser Tabelle belegten Speicherplatz zurück. Die Ergebnisse, die von der gespeicherten Prozedur zurückgegeben werden, sind jedoch nicht sortierbar, da die Spalten Zeichenwerte sind.

Das folgende Skript generiert die Informationen, nach denen ich suche.

create table #TableSize (
    Name varchar(255),
    [rows] int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255))
create table #ConvertedSizes (
    Name varchar(255),
    [rows] int,
    reservedKb int,
    dataKb int,
    reservedIndexSize int,
    reservedUnused int)

EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows], 
SUBSTRING(reserved, 0, LEN(reserved)-2), 
SUBSTRING(data, 0, LEN(data)-2), 
SUBSTRING(index_size, 0, LEN(index_size)-2), 
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize

select * from #ConvertedSizes
order by reservedKb desc

drop table #TableSize
drop table #ConvertedSizes

25
2017-10-25 16:17



Hier ist eine andere Methode: verwenden SQL Server-Verwaltungsstudio, im Objekt-Explorer, gehen Sie zu Ihrer Datenbank und wählen Sie Tabellen

enter image description here

Dann öffne die Objekt Explorer Details (entweder durch Drücken von F7 oder gehen Ansicht-> Details zum Objekt-Explorer). Klicken Sie auf der Detailseite des Objekt-Explorers mit der rechten Maustaste auf die Spaltenüberschrift und aktivieren Sie die Spalten, die Sie auf der Seite sehen möchten. Sie können die Daten auch nach jeder Spalte sortieren.

enter image description here


17
2018-01-18 17:11



Obige Abfragen eignen sich gut zum Ermitteln des von der Tabelle belegten Speicherplatzes (eingeschlossene Indizes). Wenn Sie jedoch vergleichen möchten, wie viel Speicherplatz von Indizes für die Tabelle verwendet wird, verwenden Sie diese Abfrage:

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    i.index_id AS IndexID,
    8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
    sys.indexes AS i JOIN 
    sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id JOIN 
    sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY
    i.OBJECT_ID,
    i.index_id,
    i.name
ORDER BY
    OBJECT_NAME(i.OBJECT_ID),
    i.index_id

13
2017-07-11 18:36



Wenn Sie genau dieselben Zahlen berechnen müssen, die sich auf der Seite "Tabelleneigenschaften - Speicher" in SSMS befinden, müssen Sie sie mit der gleichen Methode wie in SSMS zählen (funktioniert für SQL Server 2005 und höher ... und auch Funktioniert korrekt für Tabellen mit LOB-Feldern - weil nur das Zählen von "used_pages" nicht ausreicht, um die genaue Indexgröße anzuzeigen:

;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
        END) as pages
FROM sys.dm_db_partition_stats  AS s 
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
    cte.TableName, 
    cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB, 
    cast(((CASE WHEN cte.used_pages_count > cte.pages 
                THEN cte.used_pages_count - cte.pages
                ELSE 0 
          END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc

9
2018-01-31 03:39