Mam tabelę w produkcyjnej bazie danych o wielkości 525 GB, z czego 383 GB jest nieużywane:
Chciałbym odzyskać trochę tego miejsca, ale zanim zacznę działać z produkcyjną bazą danych, testuję niektóre strategie na identycznej tabeli w testowej bazie danych z mniejszą ilością danych. W tej tabeli występuje podobny problem:
Niektóre informacje o tabeli:
- Współczynnik wypełnienia jest ustawiony na 0
- Istnieje około 30 kolumn
- Jedna z kolumn to LOB typu image i przechowuje pliki o rozmiarach od kilku KB do kilkuset MB
- Tabela nie ma z nią żadnych hipotetycznych indeksów
Serwer działa SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64). Baza danych korzysta z SIMPLEmodelu odzyskiwania.
Niektóre rzeczy próbowałem:
- Odbudowa indeksów:
ALTER INDEX ALL ON dbo.MyTable REBUILD. Miało to znikomy wpływ. - Reorganizacja indeksów:
ALTER INDEX ALL ON dbo.MyTable REORGANIZE WITH(LOB_COMPACTION = ON). Miało to znikomy wpływ. Skopiowałem kolumnę LOB do innej tabeli, upuściłem kolumnę, ponownie utworzyłem kolumnę i skopiowałem dane z powrotem (jak opisano w tym poście: Uwalnianie nieużywanej tabeli SQL Server Space ). Zmniejszyło to nieużywane miejsce, ale wydawało się, że po prostu przekształciło je w używane miejsce:
Użyłem narzędzia bcp do wyeksportowania tabeli, obcięcia jej i ponownego załadowania (jak opisano w tym poście: Jak zwolnić nieużywane miejsce na tabelę ). Zmniejszyło to również niewykorzystane miejsce i zwiększyło używane miejsce w podobnym stopniu, jak na powyższym obrazie.
- Chociaż nie jest to zalecane, wypróbowałem polecenia DBCC SHRINKFILE i DBCC SHRINKDATABASE, ale nie miały one wpływu na nieużywane miejsce.
- Bieganie
DBCC CLEANTABLE('myDB', 'dbo.myTable')nie miało znaczenia - Próbowałem wszystkich powyższych, zachowując typy danych obrazu i tekstu oraz po zmianie typów danych na varbinary (max) i varchar (max).
- Próbowałem zaimportować dane do nowej tabeli w świeżej bazie danych, co również przekształciło tylko nieużywane miejsce w miejsce używane. Szczegóły tej próby opisałem w tym poście .
Nie chcę podejmować tych prób na produkcyjnej bazie danych, jeśli takich wyników mogę się spodziewać, więc:
- Dlaczego niewykorzystane miejsce jest po prostu konwertowane na używane miejsce po niektórych z tych prób? Czuję, że nie rozumiem dobrze, co dzieje się pod maską.
- Czy jest coś jeszcze, co mogę zrobić, aby zmniejszyć niewykorzystane miejsce bez zwiększania zajętego miejsca?
EDYCJA: Oto raport Wykorzystanie dysku i skrypt dla tabeli:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
[Column1] [int] NOT NULL,
[Column2] [int] NOT NULL,
[Column3] [int] NOT NULL,
[Column4] [bit] NOT NULL,
[Column5] [tinyint] NOT NULL,
[Column6] [datetime] NULL,
[Column7] [int] NOT NULL,
[Column8] [varchar](100) NULL,
[Column9] [varchar](256) NULL,
[Column10] [int] NULL,
[Column11] [image] NULL,
[Column12] [text] NULL,
[Column13] [varchar](100) NULL,
[Column14] [varchar](6) NULL,
[Column15] [int] NOT NULL,
[Column16] [bit] NOT NULL,
[Column17] [datetime] NULL,
[Column18] [varchar](50) NULL,
[Column19] [varchar](50) NULL,
[Column20] [varchar](60) NULL,
[Column21] [varchar](20) NULL,
[Column22] [varchar](120) NULL,
[Column23] [varchar](4) NULL,
[Column24] [varchar](75) NULL,
[Column25] [char](1) NULL,
[Column26] [varchar](50) NULL,
[Column27] [varchar](128) NULL,
[Column28] [varchar](50) NULL,
[Column29] [int] NULL,
[Column30] [text] NULL,
CONSTRAINT [PK] PRIMARY KEY CLUSTERED
(
[Column1] ASC,
[Column2] ASC,
[Column3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column4] DEFAULT (0) FOR [Column4]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column5] DEFAULT (0) FOR [Column5]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column15] DEFAULT (0) FOR [Column15]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [DF_Column16] DEFAULT (0) FOR [Column16]
GOOto wyniki wykonania poleceń w odpowiedzi Maxa Vernona:
╔════════════╦═══════════╦════════════╦═════════════════╦══════════════════════╦════════════════════╗
║ TotalBytes ║ FreeBytes ║ TotalPages ║ TotalEmptyPages ║ PageBytesFreePercent ║ UnusedPagesPercent ║
╠════════════╬═══════════╬════════════╬═════════════════╬══════════════════════╬════════════════════╣
║ 9014280192║ 8653594624║ 1100376║ 997178 ║ 95.998700 ║ 90.621500 ║
╚════════════╩═══════════╩════════════╩═════════════════╩══════════════════════╩════════════════════╝╔═════════════╦═══════════════════╦════════════════════╗
║ ObjectName ║ ReservedPageCount ║ UsedPageCount ║
╠═════════════╬═══════════════════╬════════════════════╣
║ dbo.MyTable ║ 5109090 ║ 2850245 ║
╚═════════════╩═══════════════════╩════════════════════╝AKTUALIZACJA:
Uruchomiłem następujące, zgodnie z sugestią Maxa Vernona:
DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');A oto wynik:
DBCC UPDATEUSAGE: Usage counts updated for table 'MyTable' (index 'PK_MyTable', partition 1):
USED pages (LOB Data): changed from (568025) to (1019641) pages.
RSVD pages (LOB Data): changed from (1019761) to (1019763) pages.To zaktualizowało użycie dysku dla tabeli:
I ogólne użycie dysku:
Wygląda więc na to, że problem polegał na tym, że użycie dysku śledzone przez SQL Server stało się bardzo niezsynchronizowane z rzeczywistym użyciem dysku. Uważam ten problem za rozwiązany, ale chciałbym wiedzieć, dlaczego tak się stało!
DBCC UPDATEUSAGEzaktualizowało nieużywane miejsce i liczbę nieużywanych stron. Wygląda na to, że użycie dysku i informacje o stronie zgłaszane przez SQL Server były bardzo niezsynchronizowane - zaktualizowałem swój post ze szczegółami. Jestem ciekawy, jak by to się stało, ale przynajmniej problem został znaleziony. Dziękuję za całą pomoc, naprawdę to doceniam!