Comment trouver les plus gros objets dans une base de données SQL Server?

Question

Comment pourrais-je rechercher les plus gros objets d'une base de données SQL Server? Tout d'abord, en déterminant quelles tables (et les index associés) sont les plus grandes, puis quelles lignes d'une table sont les plus grandes (nous stockons des données binaires dans des BLOB)?

Existe-t-il des outils pour vous aider dans ce type d'analyse de base de données? Ou y a-t-il des requêtes simples que je pourrais exécuter sur les tables système?

Answer 1

J'utilise ce script SQL (que j'ai reçu de quelqu'un, quelque part - je ne peux pas reconstruire de qui il vient) - et cela m'a beaucoup aidé à comprendre et à déterminer la taille des index et des tables:

 SELECT 
    t.NAME AS TableName,
    i.name as indexName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) as TotalPages, 
    sum(a.used_pages) as UsedPages, 
    sum(a.data_pages) as DataPages,
    (sum(a.total_pages) * 8) / 1024 as TotalSpaceMB, 
    (sum(a.used_pages) * 8) / 1024 as UsedSpaceMB, 
    (sum(a.data_pages) * 8) / 1024 as DataSpaceMB
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
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id <= 1
GROUP BY 
    t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
    object_name(i.object_id) 
 

Bien sûr, vous pouvez utiliser un autre critère de commande, par exemple

 ORDER BY SUM(p.rows) DESC
 

pour obtenir les tables avec le plus de lignes, ou

 ORDER BY SUM(a.total_pages) DESC
 

pour obtenir les tables avec le plus de pages (blocs de 8K) utilisées.

Answer 2

Cette aide à la recherche pour trouver la plus grande table dans vous êtes la connexion.

SELECT TOP 1 OBJECT_NAME(OBJECT_ID) TableName, st.row_count FROM sys.dm_db_partition_stats st WHERE index_id < 2 ORDER BY st.row_count DESC

Answer 3

Vous pouvez également utiliser le code suivant:

 USE AdventureWork
GO
CREATE TABLE #GetLargest 
(
  table_name    sysname ,
  row_count     INT,
  reserved_size VARCHAR(50),
  data_size     VARCHAR(50),
  index_size    VARCHAR(50),
  unused_size   VARCHAR(50)
)

SET NOCOUNT ON

INSERT #GetLargest

EXEC sp_msforeachtable 'sp_spaceused ''?'''

SELECT 
  a.table_name,
  a.row_count,
  COUNT(*) AS col_count,
  a.data_size
  FROM #GetLargest a
     INNER JOIN information_schema.columns b
     ON a.table_name collate database_default
     = b.table_name collate database_default
       GROUP BY a.table_name, a.row_count, a.data_size
       ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC

DROP TABLE #GetLargest
 

Answer 4

Si vous utilisez SQL Server Management Studio 2008, vous pouvez afficher certains champs de données dans la fenêtre de détails de l'explorateur d'objets. Il suffit de parcourir et de sélectionner le dossier des tables. Dans la vue Détails, vous pouvez cliquer avec le bouton droit sur les titres des colonnes et ajouter des champs au "rapport". Votre kilométrage peut varier si vous utilisez SSMS 2008 Express.

Answer 5

J'ai trouvé cette requête également très utile dans SqlServerCentral, voici le lien vers le message d'origine

Sql Server plus grandes tables

   select name=object_schema_name(object_id) + '.' + object_name(object_id)
, rows=sum(case when index_id < 2 then row_count else 0 end)
, reserved_kb=8*sum(reserved_page_count)
, data_kb=8*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 )
, index_kb=8*(sum(used_page_count) 
    - 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 )
     )    
, unused_kb=8*sum(reserved_page_count-used_page_count)
from sys.dm_db_partition_stats
where object_id > 1024
group by object_id
order by 
rows desc   
 

Dans ma base de données, ils ont donné des résultats différents entre cette requête et la première réponse.

J'espère que quelqu'un trouve utile