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