size - tablas - tamaño base sql server

Tamaño de tabla SQL Azure

Question

En mssql2005 cuando quiero obtener el tamaño de la tabla en MB, utilizo EXEC sp_spaceused ''table''.

¿Hay alguna forma de obtener espacio utilizado por una tabla particular en SQL Azure usando alguna consulta o API?

Answer 1

Aquí hay una consulta que le dará por tabla el tamaño total, número de filas y bytes por fila:

select
o.name,
max(s.row_count) AS ''Rows'',
sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as ''GB'',
(8 * 1024 * sum(s.reserved_page_count)) / (max(s.row_count)) as ''Bytes/Row''
from sys.dm_db_partition_stats s, sys.objects o
where o.object_id = s.object_id
group by o.name
having max(s.row_count) > 0
order by GB desc

Y aquí hay una consulta que es la misma que la anterior pero la desglosa por índice:

select
o.Name,
i.Name,
max(s.row_count) AS ''Rows'',
sum(s.reserved_page_count) * 8.0 / (1024 * 1024) as ''GB'',
(8 * 1024* sum(s.reserved_page_count)) / max(s.row_count) as ''Bytes/Row''
from
sys.dm_db_partition_stats s,
sys.indexes i,
sys.objects o
where
s.object_id = i.object_id
and s.index_id = i.index_id
and s.index_id >0
and i.object_id = o.object_id
group by i.Name, o.Name
having SUM(s.row_count) > 0
order by GB desc

Answer 2

De Ryan Dunn http://dunnry.com/blog/CalculatingTheSizeOfYourSQLAzureDatabase.aspx

select
sum(reserved_page_count) * 8.0 / 1024 [SizeInMB]
from
sys.dm_db_partition_stats
GO
select
sys.objects.name, sum(reserved_page_count) * 8.0 / 1024 [SizeInMB]
from
sys.dm_db_partition_stats, sys.objects
where
sys.dm_db_partition_stats.object_id = sys.objects.object_id
group by sys.objects.name
order by sum(reserved_page_count) DESC

El primero le dará el tamaño de su base de datos en MB y el segundo hará lo mismo, pero divídalo por cada objeto en su base de datos ordenado de mayor a menor.

Answer 3

De esta manera puedes tener el más grande en la parte superior:

 SELECT sys.objects.name,
SUM(row_count) AS ''Row Count'',
SUM(reserved_page_count) * 8.0 / 1024 AS ''Table Size (MB)''
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name
ORDER BY [Table Size (MB)] DESC

Source