Obtenez de Table et d'Index taille de stockage dans sql server

Je veux obtenir les données de la table d'index et de l'espace pour chaque table dans ma base de données:

Table Name             Data Space           Index Space
-------------------------------------------------------

Comment puis-je arriver à ce résultat?

  • "Je veux <faire quelque chose d'utile> <décrit brièvement mais clairement que nécessaire pour être>" ... Fermé comme hors-sujet. Top travail, les modérateurs.
  • Pour info, simple pour une table sp_spaceused 'TableName'
InformationsquelleAutor Arian | 2013-04-09
  1. 157

    Cette requête ici la liste de la taille totale d'un tableau prend index cluster, tas et tous les non-cluster indices:

    SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
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
WHERE 
    t.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    s.Name, t.Name

    Si vous voulez séparer l'espace de la table à partir de l'index de l'espace, vous devez utiliser AND i.index_id IN (0,1) pour l'espace de la table (index_id = 0 est le segment de l'espace, index_id = 1 est la taille de l'index cluster = pages de données) et AND i.index_id > 1 pour l'index-l'espace

    • merci chers @marc_s.Si nous avons un clic droit sur un nom de table et choisissez Properties dans l'onglet stockage il y a Data Space et Index Space.Si je veux obtenir ces chiffres que puis-je faire?
    • Il devrait y avoir quelque chose de mal ici. Êtes-vous usre SUM(a.used_pages) * 8 vous donne de l'espace utilisé dans KB? Je thinkg il devrait être Byte à la place. Pourriez-vous confirmer ce? whatsabyte.com/P1/byteconverter.htm
    • le page dans SQL Server est toujours 8 KO - le nombre de pages multiplié par 8 vous donne le montant de KO qui est pris
    • Merci pour la réponse rapide. J'étais confus et mal interprété la taille d'une table SQL Management Studio est à l'affiche.
    • Vous devez ajouter schema_id dans votre GROUPE PAR (et dans la liste de sélection comme SCHEMA_NAME(t.SCHEMA_ID), sinon, vous êtes l'effondrement des tailles sur l'ensemble des schémas.
    • vrai, mais dans mon expérience, 99% ou plus de la bases de données n'ont le dbo schéma ..... mais votre point de vue est valable, SI vous avez plusieurs schémas dans votre base de données
    • mise à jour de mon instruction SQL avec les informations de schéma.
    • Toute personne qui utilise ce code va probablement vouloir supprimer le "t.NOM PAS COMME 'dt%'" condition de la clause where. Je ne peux pas deviner pourquoi il est là.
    • il est là pour filtrer les différentes tables système installé si vous utilisez la base de données de création de diagrammes fonctionnalités de SQL Server
    • Nombre de lignes doivent être supprimées à partir de clause GROUP BY et RowCounts réécrit comme la SOMME( CAS (j'.index_id=0) OU (j'.index_id=1) ALORS p.les lignes ELSE 0 END ) COMME RowCounts
    • Sinon, ce script va afficher des résultats incorrects. Exemple: la base de données a des tables avec des index filtrés (nombre de lignes dans la page d'index est différent du nombre total de lignes dans la table).
    • pour info, lors d'une grande suppression en cascade, la requête a montré plusieurs lignes (4) pour une seule table. J'ai appliqué @PavelNefyodov commentaire, mais je suis pas sûr à 100% de la somme du nombre de lignes a été correct. Exemple de sortie a été Rangées/DataMb/IndexMb: 45, 0, 27; 20, 0, 0; 21, 15, 0; 19, 0, 0;
    • j'utilise les index filtrés sur certains tableaux, j'utilise donc MAX(p.les lignes) QUE RowCounts en collaboration avec le GROUPE de t les.Nom, s.Nom

    InformationsquelleAutor marc_s
  2. 14
    with pages as (
SELECT object_id, SUM (reserved_page_count) as reserved_pages, SUM (used_page_count) as used_pages,
SUM (case 
when (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
group by object_id
), extra as (
SELECT p.object_id, sum(reserved_page_count) as reserved_pages, sum(used_page_count) as used_pages
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id
group by p.object_id
)
SELECT object_schema_name(p.object_id) + '.' + object_name(p.object_id) as TableName, (p.reserved_pages + isnull(e.reserved_pages, 0)) * 8 as reserved_kb,
pages * 8 as data_kb,
(CASE WHEN p.used_pages + isnull(e.used_pages, 0) > pages THEN (p.used_pages + isnull(e.used_pages, 0) - pages) ELSE 0 END) * 8 as index_kb,
(CASE WHEN p.reserved_pages + isnull(e.reserved_pages, 0) > p.used_pages + isnull(e.used_pages, 0) THEN (p.reserved_pages + isnull(e.reserved_pages, 0) - p.used_pages + isnull(e.used_pages, 0)) else 0 end) * 8 as unused_kb
from pages p
left outer join extra e on p.object_id = e.object_id

    Prend en compte les tables internes, tels que ceux utilisés pour le stockage XML.

    Edit: Si vous divisez le data_kb et index_kb valeurs par 1024.0, vous obtiendrez les chiffres que vous voyez dans l'interface graphique.

    InformationsquelleAutor muhmud