Ik heb een vrij grote SQL Server-database geërfd. Het lijkt meer ruimte in beslag te nemen dan ik zou verwachten, gezien de gegevens die het bevat.
Is er een gemakkelijke manier om te bepalen hoeveel schijfruimte elke tafel in beslag neemt?
Antwoord 1, autoriteit 100%
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
TotalSpaceMB DESC, t.Name
Antwoord 2, autoriteit 22%
Als u SQL Server Management Studio(SSMS) gebruikt, kunt u in plaats van een query uit te voeren (die in mijn geval dubbele rijen retourneerde) een uitvoeren standaard rapport.
- Klik met de rechtermuisknop op de database
- Navigeer naar Rapporten > Standaardrapporten > Schijfgebruik per tabel
OPMERKING: Het databasecompatibiliteitsniveau moet hiervoor op 90 of hoger worden ingesteld om correct te werken. Zie http://msdn.microsoft.com/en-gb/library/bb510680.aspx
3, Autoriteit 4%
SP_SPACEUSED kan u informatie krijgen op de schijfruimte die wordt gebruikt door een tabel, geïndexeerde weergave of de hele database.
Bijvoorbeeld:
USE MyDatabase; GO
EXEC sp_spaceused N'User.ContactInfo'; GO
Hiermee rapporteert u de schijfgebruiksinformatie voor de Contactinfo-tabel.
Om dit voor alle tabellen tegelijk te gebruiken:
USE MyDatabase; GO
sp_msforeachtable 'EXEC sp_spaceused [?]' GO
U kunt ook schijfgebruik krijgen vanuit de rechtermuisknop Standard Reports-functionaliteit van SQL Server. Om bij dit rapport te gaan, navigeren, navigeren van het Server-object in Object Explorer, ga naar het object databases en klik vervolgens met de rechtermuisknop op een dergelijke database. Selecteer Rapporten in het menu dat verschijnt, vervolgens standaardrapporten en vervolgens “Disk-gebruik door partitie: [Databasename]”.
4, Autoriteit 2%
Hier is een andere methode: met SQL Server Management Studio , in Object Explorer , ga naar uw database en selecteer Tables
Open vervolgens de details Object Explorer (door op F7 te drukken of naar View- & GT; Object Explorer Details ). Klik in de pagina Object Explorer Details met de rechtermuisknop op de kolomkop en schakel de kolommen in die u op de pagina wilt zien. U kunt de gegevens ook op elke kolom sorteren.
5, Autoriteit 2%
exec sp_spaceused N'dbo.MyTable'
Gebruik voor alle tafels .. (Toevoegen van de opmerkingen van Paul)
exec sp_MSForEachTable 'exec sp_spaceused [?]'
6
Na enige zoekopdracht kon ik geen gemakkelijke manier vinden om informatie over alle tabellen te krijgen. Er is een handige opgeslagen procedure met de naam SP_SPACEUTED die alle ruimte die door de database wordt gebruikt. Indien voorzien van een tabelnaam, retourneert het de spatie die door die tabel wordt gebruikt. De resultaten die door de opgeslagen procedure worden geretourneerd, zijn echter niet sorteerbaar, omdat de kolommen karakterwaarden zijn.
Het volgende script zal de informatie genereren waar ik naar op zoek ben.
create table #TableSize (
Name varchar(255),
[rows] int,
reserved varchar(255),
data varchar(255),
index_size varchar(255),
unused varchar(255))
create table #ConvertedSizes (
Name varchar(255),
[rows] int,
reservedKb int,
dataKb int,
reservedIndexSize int,
reservedUnused int)
EXEC sp_MSforeachtable @command1="insert into #TableSize
EXEC sp_spaceused '?'"
insert into #ConvertedSizes (Name, [rows], reservedKb, dataKb, reservedIndexSize, reservedUnused)
select name, [rows],
SUBSTRING(reserved, 0, LEN(reserved)-2),
SUBSTRING(data, 0, LEN(data)-2),
SUBSTRING(index_size, 0, LEN(index_size)-2),
SUBSTRING(unused, 0, LEN(unused)-2)
from #TableSize
select * from #ConvertedSizes
order by reservedKb desc
drop table #TableSize
drop table #ConvertedSizes
Antwoord 7
Voor Azure heb ik dit gebruikt:
Je zou SSMS v17.x+ moeten hebben
Ik gebruikte;
Hiermee, zoals Gebruiker Sparrow heeft vermeld:
Open uw Databases> en selecteer Tafels,
Druk vervolgens op de toets F7
U zou het row countmoeten zien
als:
SSMS hieris verbonden met Azure-databases
Antwoord 8
Bovenstaande zoekopdrachten zijn goed voor het vinden van de hoeveelheid ruimte die door de tabel wordt gebruikt (inclusief indexen), maar als u wilt vergelijken hoeveel ruimte wordt gebruikt door indexen in de tabel, gebruik dan deze zoekopdracht:
SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)'
FROM
sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE
i.is_primary_key = 0 -- fix for size discrepancy
GROUP BY
i.OBJECT_ID,
i.index_id,
i.name
ORDER BY
OBJECT_NAME(i.OBJECT_ID),
i.index_id
Antwoord 9
Uitbreiding naar @xav answerdie tabelpartities verwerkte om de grootte in MB en GB te krijgen.
Getest op SQL Server 2008/2012 (commentaar op een regel waar is_memory_optimized = 1)
SELECT
a2.name AS TableName,
a1.rows as [RowCount],
--(a1.reserved + ISNULL(a4.reserved,0)) * 8 AS ReservedSize_KB,
--a1.data * 8 AS DataSize_KB,
--(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS IndexSize_KB,
--(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS UnusedSize_KB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_MB,
CAST(ROUND(a1.data * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_MB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_MB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_MB,
--'| |' Separator_MB_GB,
CAST(ROUND(((a1.reserved + ISNULL(a4.reserved,0)) * 8) / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS ReservedSize_GB,
CAST(ROUND(a1.data * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS DataSize_GB,
CAST(ROUND((CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS IndexSize_GB,
CAST(ROUND((CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024.00 / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSize_GB
FROM
(SELECT
ps.object_id,
SUM (CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
--===Remove the following comment for SQL Server 2014+
--WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
--AND a2.name = 'MyTable' --Filter for specific table
--ORDER BY a3.name, a2.name
ORDER BY ReservedSize_MB DESC
Antwoord 10
Als u exact dezelfde getallen wilt berekenen, die op de pagina ‘tabeleigenschappen – opslag’ in SSMS staan, moet u ze tellen met dezelfde methode als in SSMS (werkt voor SQL Server 2005 en hoger .. en werkt ook correct voor tabellen met LOB-velden – omdat alleen het tellen van “gebruikte_pagina’s” niet voldoende is om de juiste indexgrootte weer te geven):
;with cte as (
SELECT
t.name as TableName,
SUM (s.used_page_count) as used_pages_count,
SUM (CASE
WHEN (i.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) as pages
FROM sys.dm_db_partition_stats AS s
JOIN sys.tables AS t ON s.object_id = t.object_id
JOIN sys.indexes AS i ON i.[object_id] = t.[object_id] AND s.index_id = i.index_id
GROUP BY t.name
)
select
cte.TableName,
cast((cte.pages * 8.)/1024 as decimal(10,3)) as TableSizeInMB,
cast(((CASE WHEN cte.used_pages_count > cte.pages
THEN cte.used_pages_count - cte.pages
ELSE 0
END) * 8./1024) as decimal(10,3)) as IndexSizeInMB
from cte
order by 2 desc
Antwoord 11
We gebruiken tabelpartitionering en hadden wat problemen met de bovenstaande query’s vanwege dubbele records.
Voor degenen die dit nodig hebben, vindt u hieronder de query zoals uitgevoerd door SQL Server 2014 bij het genereren van het rapport “Schijfgebruik per tabel”. Ik neem aan dat het ook werkt met eerdere versies van SQL Server.
Het werkt als een tierelier.
SELECT
a2.name AS [tablename],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name
Antwoord 12
-- Show the size of all the tables in a database sort by data size descending
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused ''?'''
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC
Antwoord 13
Een kleine verandering op Mar_c’s antwoord, aangezien ik zo vaak terug ben op deze pagina, gerangschikt volgens de meeste rijen eerst:
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
--p.rows DESC --Uncomment to order by amount rows instead of size in KB.
SUM(a.total_pages) DESC
Antwoord 14
Dit geeft u de maten en recordaantallen voor elke tafel.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Get a list of tables and their sizes on disk
ALTER PROCEDURE [dbo].[sp_Table_Sizes]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)
CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)
INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');
-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END
FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1
SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;
DROP TABLE #temp_Table
END
Antwoord 15
Om alle tabelgroottes in één database te krijgen, kunt u deze query gebruiken:
Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
En u kunt het wijzigen om het al het resultaat in de temp-tabel in te voegen en na dat uit de Temp-tabel.
Insert into #TempTable Exec sys.sp_MSforeachtable ' sp_spaceused "?" '
Select * from #TempTable
16
Van een opdrachtprompt met behulp van osql :
OSQL -E -d <*databasename*> -Q "exec sp_msforeachtable 'sp_spaceused [?]'" > result.txt
17
Ik heb nog een paar kolommen toegevoegd bovenop Marc_s Antwoord:
with fs
as
(
select i.object_id,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKb
from sys.indexes i 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
i.OBJECT_ID > 255
GROUP BY
i.object_id,
p.rows
)
SELECT
t.NAME AS TableName,
fs.RowCounts,
fs.TotalSpaceKb,
t.create_date,
t.modify_date,
( select COUNT(1)
from sys.columns c
where c.object_id = t.object_id ) TotalColumns
FROM
sys.tables t INNER JOIN
fs ON t.OBJECT_ID = fs.object_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
ORDER BY
t.Name
18
Mijn bericht is alleen relevant voor SQL Server 2000 en is getest om in mijn omgeving te werken.
Deze code heeft toegang tot Alle mogelijke databases van één instantie , niet alleen een enkele database.
Ik gebruik twee Temp-tabellen om de juiste gegevens te verzamelen en de resultaten vervolgens in één ‘Live’-tabel te dumpen.
Geretourneerde gegevens zijn: Databasename, databasetablenaam, rijen (in de tabel), gegevens (grootte van de tabel in KB het lijkt), invoergegevens (ik vind dit handig om te weten wanneer ik het script laat zien).
Downfall naar deze code is het veld ‘Data’ wordt niet opgeslagen als een int (de KB ‘worden op dat gebied gehouden), en dat zou nuttig zijn (maar niet volledig noodzakelijk) voor sorteren.
Hopelijk helpt deze code iemand anders en bespaart hij/zij wat tijd!
CREATE PROCEDURE [dbo].[usp_getAllDBTableSizes]
AS
BEGIN
SET NOCOUNT OFF
CREATE TABLE #DatabaseTables([dbname] sysname,TableName sysname)
CREATE TABLE #AllDatabaseTableSizes(Name sysname,[rows] VARCHAR(18), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
DECLARE @SQL nvarchar(4000)
SET @SQL='select ''?'' AS [Database], Table_Name from [?].information_schema.tables WHERE TABLE_TYPE = ''BASE TABLE'' '
INSERT INTO #DatabaseTables(DbName, TableName)
EXECUTE sp_msforeachdb @Command1=@SQL
DECLARE AllDatabaseTables CURSOR LOCAL READ_ONLY FOR
SELECT TableName FROM #DatabaseTables
DECLARE AllDatabaseNames CURSOR LOCAL READ_ONLY FOR
SELECT DBName FROM #DatabaseTables
DECLARE @DBName sysname
OPEN AllDatabaseNames
DECLARE @TName sysname
OPEN AllDatabaseTables
WHILE 1=1 BEGIN
FETCH NEXT FROM AllDatabaseNames INTO @DBName
FETCH NEXT FROM AllDatabaseTables INTO @TName
IF @@FETCH_STATUS<>0 BREAK
INSERT INTO #AllDatabaseTableSizes
EXEC ( 'EXEC ' + @DBName + '.dbo.sp_spaceused ' + @TName)
END
--http://msdn.microsoft.com/en-us/library/aa175920(v=sql.80).aspx
INSERT INTO rsp_DatabaseTableSizes (DatabaseName, name, [rows], data)
SELECT [dbname], name, [rows], data FROM #DatabaseTables
INNER JOIN #AllDatabaseTableSizes
ON #DatabaseTables.TableName = #AllDatabaseTableSizes.Name
GROUP BY [dbname] , name, [rows], data
ORDER BY [dbname]
--To be honest, I have no idea what exact duplicates we are dropping
-- but in my case a near enough approach has been good enough.
DELETE FROM [rsp_DatabaseTableSizes]
WHERE name IN
(
SELECT name
FROM [rsp_DatabaseTableSizes]
GROUP BY name
HAVING COUNT(*) > 1
)
DROP TABLE #DatabaseTables
DROP TABLE #AllDatabaseTableSizes
CLOSE AllDatabaseTables
DEALLOCATE AllDatabaseTables
CLOSE AllDatabaseNames
DEALLOCATE AllDatabaseNames
END
--EXEC [dbo].[usp_getAllDBTableSizes]
Voor het geval je het moet weten, de tabel rsp_DatabaseTableSizesis gemaakt via:
CREATE TABLE [dbo].[rsp_DatabaseSizes](
[DatabaseName] [varchar](1000) NULL,
[dbSize] [decimal](15, 2) NULL,
[DateUpdated] [smalldatetime] NULL
) ON [PRIMARY]
GO
Antwoord 19
Hier is een voorbeeldquery om tabellen groter dan 1 GB te krijgen, gesorteerd op aflopende grootte.
USE YourDB
GO
DECLARE @Mult float = 8
SET @Mult = @Mult / POWER(2, 20) -- Use POWER(2, 10) for MBs
; WITH CTE AS
(
SELECT
i.object_id,
Rows = MAX(p.rows),
TotalSpaceGB = ROUND(SUM(a.total_pages) * @Mult, 0),
UsedSpaceGB = ROUND(SUM(a.used_pages) * @Mult, 0)
FROM
sys.indexes i
JOIN
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN
sys.allocation_units a ON p.partition_id = a.container_id
WHERE
i.object_id > 255
GROUP BY
i.object_id
HAVING
SUM(a.total_pages) * @Mult > 1
)
SELECT
SchemaName = s.name,
TableName = t.name,
c.TotalSpaceGB,
c.UsedSpaceGB,
UnusedSpaceGB = c.TotalSpaceGB - c.UsedSpaceGB,
[RowCount] = c.Rows
FROM
CTE c
JOIN
sys.tables t ON t.object_id = c.object_id
JOIN
sys.schemas s ON t.schema_id = s.schema_id
ORDER BY
c.TotalSpaceGB DESC
Antwoord 20
Als een eenvoudige uitbreiding op het antwoord van marc_s(degene die is geaccepteerd), wordt dit aangepast om het aantal kolommen te retourneren en laat filteren toe:
SELECT *
FROM
(
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
COUNT(DISTINCT c.COLUMN_NAME) as ColumnCount,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
(SUM(a.used_pages) * 8) AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
INNER JOIN
INFORMATION_SCHEMA.COLUMNS c ON t.NAME = c.TABLE_NAME
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
) AS Result
WHERE
RowCounts > 1000
AND ColumnCount > 10
ORDER BY
UsedSpaceKB DESC
Antwoord 21
Ik vond deze zoekopdracht gemakkelijk te gebruiken & snel.
select schema_name(tab.schema_id) + '.' + tab.name as [table],
cast(sum(spc.used_pages * 8)/1024.00 as numeric(36, 2)) as used_mb,
cast(sum(spc.total_pages * 8)/1024.00 as numeric(36, 2)) as allocated_mb
from sys.tables (nolock) tab
inner join sys.indexes (nolock) ind
on tab.object_id = ind.object_id
inner join sys.partitions (nolock) part
on ind.object_id = part.object_id and ind.index_id = part.index_id
inner join sys.allocation_units (nolock) spc
on part.partition_id = spc.container_id
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(spc.used_pages) desc
22
Als u alleen om de lege verspilde ruimte in de database geeft en niet de afzonderlijke tabellen kunt u overwegen:
Als de database een grote hoeveelheid gegevensinvoeging ervaart en vervolgens verwijderingen, misschien als in een ETL-situatie, zal dit te veel ongebruikte ruimte in de database veroorzaken als de bestandsgroepen Auto-groeit maar nooit automatisch -Wrinks terug.
U kunt zien of dit het geval is door gebruik te maken van de -gebruikers pagina van uw database. U kunt krimpen (klik met de rechtermuisknop op de database en gt; taken & gt; krimpen) en claim wat ruimte terug. Maar als de onderliggende reden er nog steeds is, zal de database teruggroeien (en extra tijd consumeren om terug te groeien en dingen worden vertraagd totdat het genoeg groeit – dus doe het niet in dat geval)
23
Riffing op @mark antwoord hierboven, voegde de @ updateage = ‘TRUE’ aan om de nieuwste maatstatistieken (https://msdn.microsoft.com/en-us/library/ms188776.aspx ):
SET NOCOUNT ON
DECLARE @TableInfo TABLE (tablename varchar(255), rowcounts int, reserved varchar(255), DATA varchar(255), index_size varchar(255), unused varchar(255))
DECLARE @cmd1 varchar(500)
SET @cmd1 = 'exec sp_spaceused @objname =''?'', @updateusage =''true'' '
INSERT INTO @TableInfo (tablename,rowcounts,reserved,DATA,index_size,unused)
EXEC sp_msforeachtable @command1=@cmd1
SELECT * FROM @TableInfo ORDER BY Convert(int,Replace(DATA,' KB','')) DESC