Kunnen we een SQL-query hebben die in principe helpt bij het bekijken van tabel- en indexgroottes in SQL Server.
Hoe de SQL-server het geheugengebruik voor tabellen/indexen handhaaft?
Antwoord 1, autoriteit 100%
sp_spaceused geeft je de grootte van alle indexen samen.
Als je de grootte van elke index voor een tabel wilt, gebruik dan een van deze twee zoekopdrachten:
SELECT
i.name AS IndexName,
SUM(s.used_page_count) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE s.[object_id] = object_id('dbo.TableName')
GROUP BY i.name
ORDER BY i.name
SELECT
i.name AS IndexName,
SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
db_id(), object_id('dbo.TableName'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name
De resultaten zijn meestal iets anders, maar binnen 1%.
Antwoord 2, autoriteit 62%
De exec sp_spaceusedzonder parameter toont de samenvatting voor de hele database. De voorspelbare oplossing genereert één resultatenset per tabel – die SSMS mogelijk niet aankan als u te veel tabellen heeft.
Ik heb een scriptdat de tabelinfo verzamelt via sp_spaceuseden een samenvatting weergeeft in een enkele recordset, gesorteerd op grootte.
create table #t
(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
declare @id nvarchar(128)
declare c cursor for
select '[' + sc.name + '].[' + s.name + ']' FROM sysobjects s INNER JOIN sys.schemas sc ON s.uid = sc.schema_id where s.xtype='U'
open c
fetch c into @id
while @@fetch_status = 0 begin
insert into #t
exec sp_spaceused @id
fetch c into @id
end
close c
deallocate c
select * from #t
order by convert(int, substring(data, 1, len(data)-3)) desc
drop table #t
Antwoord 3, autoriteit 21%
Op SQL 2012 is het heerlijk eenvoudig geworden om deze informatie op tabelniveau te krijgen:
SQL Management Studio -> Klik met de rechtermuisknop op Db -> Rapporten -> Standaardrapporten -> Schijfgebruik per tabel!
Geniet
Antwoord 4, autoriteit 11%
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
Antwoord 5, autoriteit 4%
--Gets the size of each index for the specified table
DECLARE @TableName sysname = N'SomeTable';
SELECT i.name AS IndexName
,8 * SUM(s.used_page_count) AS IndexSizeKB
FROM sys.indexes AS i
INNER JOIN sys.dm_db_partition_stats AS s
ON i.[object_id] = s.[object_id] AND i.index_id = s.index_id
WHERE s.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;
SELECT i.name AS IndexName
,8 * SUM(a.used_pages) AS IndexSizeKB
FROM sys.indexes AS i
INNER JOIN sys.partitions AS p
ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
INNER JOIN sys.allocation_units AS a
ON p.partition_id = a.container_id
WHERE i.[object_id] = OBJECT_ID(@TableName, N'U')
GROUP BY i.name
ORDER BY i.name;
Antwoord 6, autoriteit 4%
Het is lang geleden sinds het maken van dit bericht, maar ik wilde mijn script delen:
WITH CteIndex
AS
(
SELECT
reservedpages = (reserved_page_count)
,usedpages = (used_page_count)
,pages = (
CASE
WHEN (s.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
)
,s.object_id
,i.index_id
,i.type_desc AS IndexType
,i.name AS indexname
FROM sys.dm_db_partition_stats s
INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
)
SELECT DISTINCT
DB_NAME(DB_ID()) AS DatabaseName
,o.name AS TableName
,o.object_id
,ct.indexname
,ct.IndexType
,ct.index_id
, IndexSpace = LTRIM (STR ((CASE WHEN usedpages > pages THEN CASE WHEN ct.index_id < 2 THEN pages ELSE (usedpages - pages) END ELSE 0 END) * 8, 15, 0) + ' KB')
FROM CteIndex ct
INNER JOIN sys.objects o ON o.object_id = ct.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL , NULL) ps ON ps.object_id = o.object_id
AND ps.index_id = ct.index_id
ORDER BY name ASC
Dit werkt voor:
- SQL Server (vanaf 2008)
- Omvat informatie voor alle tabellen per huidige database
Antwoord 7, autoriteit 3%
Hier is een compactere versie van het meest succesvolle antwoord:
create table #tbl(
name nvarchar(128),
rows varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)
exec sp_msforeachtable 'insert into #tbl exec sp_spaceused [?]'
select * from #tbl
order by convert(int, substring(data, 1, len(data)-3)) desc
drop table #tbl
Antwoord 8
Er is een uitgebreide opgeslagen procedure sp_spaceuseddie deze informatie eruit haalt. Het is nogal ingewikkeld om het vanuit het datadictionary te doen, maar Deze linkfans uit naar een script dat het doet. Deze stackoverflow-vraagbevat enige informatie over de onderliggende gegevensstructuren die die u kunt gebruiken om schattingen te maken van tabel- en indexgroottes voor capaciteitsplanning.
Antwoord 9
Deze vraag is afkomstig van twee andere antwoorden:
De grootte van alle tabellen in de database ophalen
Hoe de grootste objecten in een SQL Server te vinden database?
, maar ik heb dit verbeterd zodat het universeel is. Het gebruikt het woordenboek sys.objects:
SELECT
s.NAME as SCHEMA_NAME,
t.NAME AS OBJ_NAME,
t.type_desc as OBJ_TYPE,
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.objects t
INNER JOIN
sys.schemas s ON t.SCHEMA_ID = s.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%' AND
i.OBJECT_ID > 255 AND
i.index_id <= 1
GROUP BY
s.NAME, t.NAME, t.type_desc, i.object_id, i.index_id, i.name
ORDER BY
sum(a.total_pages) DESC
;
Antwoord 10
Om de opslaggegevens van een enkele tabel (en de bijbehorende indexen) te zien:
exec sp_spaceused MyTable