sql-server - una - sintaxis del top en sql

¿Cómo encontrar objetos más grandes en una base de datos de SQL Server?

Question

¿Cómo podría encontrar los objetos más grandes en una base de datos de SQL Server? Primero, al determinar qué tablas (e índices relacionados) son las más grandes y luego determinar qué filas en una tabla en particular son más grandes (estamos almacenando datos binarios en BLOB).

¿Hay alguna herramienta para ayudar con este tipo de análisis de bases de datos? ¿O hay algunas consultas simples que podría ejecutar contra las tablas del sistema?

Answer 1

En SQL Server 2008, también puede ejecutar el uso estándar de disco de informe por tablas principales. Puede encontrarlo haciendo clic con el botón derecho en la base de datos, seleccionando Informes-> Informes estándar y seleccionando el informe que desea.

Answer 2

Encontré esta consulta también muy útil en SqlServerCentral, aquí está el enlace a la publicación original

Tablas más grandes del servidor Sql

  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   

En mi base de datos dieron diferentes resultados entre esta consulta y la primera respuesta.

Espero que alguien encuentre útil

Answer 3

Esta consulta ayuda a encontrar la tabla más grande en la que está la conexión.

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 4

He estado usando esta secuencia de comandos SQL (que obtuve de alguien, en algún lugar, no puedo reconstruir de quién vino) durante años y me ayudó bastante a comprender y determinar el tamaño de los índices y las tablas:

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) 

Por supuesto, puede usar otros criterios de pedido, por ejemplo

ORDER BY SUM(p.rows) DESC

para obtener las tablas con la mayor cantidad de filas, o

ORDER BY SUM(a.total_pages) DESC

para obtener las tablas con la mayor cantidad de páginas (bloques de 8K) utilizadas.

Answer 5

Si está utilizando Sql Server Management Studio 2008, hay ciertos campos de datos que puede ver en la ventana de detalles del explorador de objetos. Simplemente busque y seleccione la carpeta de tablas. En la vista de detalles, puede hacer clic con el botón derecho en los títulos de las columnas y agregar campos al "informe". Su millaje puede variar si está en SSMS 2008 express.

Answer 6

También puede usar el siguiente código:

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