Indeksy odbudowy, DB teraz 10 razy większy

13

Mam bazę danych SQL Server (2008 R2 SP1), która miała około 15 koncertów. Okazuje się, że konserwacja nie była uruchomiona od dłuższego czasu, więc stworzyłem plan konserwacji, aby odbudować wszystkie indeksy, były bardzo rozdrobnione.

Zadanie zakończone i fragmentacja zniknęła, ale teraz baza danych zawiera ponad 120 koncertów! Rozumiem, że użyłby dodatkowej przestrzeni do przeprowadzenia wszystkich przebudów, ale teraz, gdy zadanie zostało wykonane, sądzę, że cała ta przestrzeń byłaby wolną przestrzenią, ale wolne miejsce pokazuje tylko 3 koncerty, więc używa się 117 koncertów nawet jeśli zadanie odbudowywania indeksu zostało zakończone.

Jestem bardzo zdezorientowany i mogę skorzystać z pewnych wskazówek, mam przywrócić db do rozsądnego rozmiaru, nie mamy na to miejsca na dysku.

Z góry dziękuję!

Oto wyniki obu wysłanych zapytań:

log_reuse_wait_desc NIC

name    TotalSpaceInMB  UsedSpaceInMB   FreeSpaceInMB
LIVE_Data   152             123             28
LIVE_Log    18939           89              18849
LIVE_1_Data 114977          111289          3688

Trzeci plik to plik .ndf, który pokazuje tylko 3688 w nieużywanym miejscu, ale 111289 jest używany na około 15 gigabajtów danych.

sql-server sql-server-2008 sql-server-2008-r2

— AS2012
źródło

15

W międzyczasie właśnie wymyśliłem to, totalne uderzenie mózgu. Wskazałem, co moim zdaniem jest współczynnikiem wypełnienia 90 w zadaniu przebudowy, ale jest to sformułowane jako „zmień procent wolnego miejsca na”, więc używając tam wartości 90, faktycznie użyłem współczynnika wypełnienia 10 !! DOH Nic dziwnego, że urósł 10-krotnie. Mam zamiar odbudować z właściwym współczynnikiem wypełnienia, a następnie zmniejszyć. Dziękuję wszystkim za wkład.

— AS2012
źródło

1

Ten czarodziej jest po prostu okropny.

— usr

Wiem, jestem tak przyzwyczajony do wiersza poleceń, w którym podajesz FILL_FACTOR, a nie wolny%, chciałbym, żeby był spójny.

Nie reindeksuj, a potem zmniejszaj, to tylko strata czasu! Zobacz moją odpowiedź, aby uzyskać więcej informacji.

— JNK

1

JNK Wiem, co masz na myśli, nie zmniejszaj się po odbudowie, ponieważ wszystko to ponownie rozpadnie się na części. Jednak w tym konkretnym scenariuszu, w którym Jeff przez przypadek dodał 90% wolnego miejsca na każdej stronie, nie widzę innego sposobu na odzyskanie miejsca niż: przebudowanie z fillfactor 90, zmniejszenie pliku, jednak trzeba by było zrobić kolejną przebudowę z wypełnieniem 90% ponownie. A może byłby inny sposób na odzyskanie miejsca z powrotem. (Cóż, może nowa grupa plików, a następnie przebuduj ją po upuszczeniu na nową grupę plików, ale to nie działa dla wszystkich)

— Edward Dortland,

2

Odbudowywanie indeksów powoduje dodatkowe luki w bazie danych. Jest to naturalny produkt uboczny procesu ponownego indeksowania - serwer buduje nową, miejmy nadzieję, ciągłą wersję indeksu obok bieżącej wersji, a następnie kończy bieżącą wersję po jej zakończeniu.

Kurczenie się po reindeksowaniu nie ma sensu!

Po prostu ponownie fragmentujesz indeks! Zmniejszenie usuwa luki, przenosząc dane w bazie danych.

Oto fajny artykuł od Paula Randala, który podczas pracy w Microsoft był odpowiedzialny za DBCCkod, w tym także za skurcze, dlaczego nie powinieneś się kurczyć.

— JNK
źródło

0

Powinieneś był skorzystać z opcji przebudowy SORT_IN_TEMPDB=ON.

W twoim przypadku do sortowania indeksów użyto rzeczywistych plików danych, w których znajdują się dane tabele. Duża część tego 120 GB miejsca jest nadal nieużywana i w razie potrzeby zostanie wypełniona danymi strony.

Możesz zobaczyć status wykorzystanego / wolnego miejsca w tym zapytaniu (wzięte stąd ):

use [YourDatabaseNameHere]
go

select
    name,
    cast((size/128.0) as int) as TotalSpaceInMB,
    cast((cast(fileproperty(name, 'SpaceUsed') as int)/128.0) as int) as UsedSpaceInMB,
    cast((size/128.0 - cast(fileproperty(name, 'SpaceUsed') AS int)/128.0) as int) as FreeSpaceInMB
from
    sys.database_files

Aby zmniejszyć konkretny plik bazy danych (danych lub dziennika), możesz zobaczyć tutaj niektóre informacje . Ostrzeżenie przed tym, zwalnianie nieużywanego miejsca zajmuje sporo czasu dla baz danych ponad 100 Gb (zależy również od prędkości przechowywania), więc po prostu pozwól mu działać.

— Marcel N.
źródło

Tak, to nie będzie format, dodam go do pytania, za chwilę.

@JeffBane: Czy możesz dodać tę informację do swojego pytania w cytacie? Nie mogę zrozumieć, co tam jest.

— Marcel N.,

1

Podczas przebudowywania indeksu potrzeba dwa razy więcej miejsca niż indeks + 20% do sortowania. Ogólnie rzecz biorąc, aby odbudować każdy indeks w bazie danych, potrzebujesz tylko 120% swojego największego indeksu w bazie danych. Jeśli używasz SORT_IN_TEMPDB, wygrywasz tylko 20%, nadal potrzebujesz dodatkowych 100% w pliku danych. Co więcej, użycie sortowania w tempdb drastycznie zwiększa obciążenie IO, ponieważ zamiast jednokrotnego zapisu indeksu do pliku danych, teraz zapisujesz go raz w tempdb, a następnie zapisujesz w pliku danych. To nie zawsze jest idealne.

— Edward Dortland,

-1

Podejrzewam, że bez żadnych innych szczegółów musisz wykonać kopię zapasową dziennika transakcji, zanim będziesz mógł odzyskać miejsce.

Zobacz, co select name, log_reuse_wait_desc from sys.databasesci mówi. Jeśli kolumna log_reuse_wait_desc mówi, LOG_BACKUPże nie może odzyskać miejsca, dopóki nie utworzysz kopii zapasowej dziennika tran.

— dpw
źródło

Konieczność wykonania kopii zapasowej dziennika transakcji nigdy nie spowoduje zwolnienia stron.

— mrdenny,