Jaki wpływ będzie miało zmniejszenie rozmiaru kolumny varchar na plik bazy danych?

W naszej bazie danych znajduje się wiele tabel, które mają VARCHAR(MAX)kolumny, w których wystarczy VARCHAR(500)(lub coś znacznie mniejszego niż maksimum). Oczywiście chcę je wyczyścić i sprowadzić rozmiary do bardziej rozsądnych poziomów. „Jak” to zrobić, rozumiem: moje pytanie brzmi: co zmieni te kolumny na strony i pliki na dysku? (Istnieje wiele informacji na temat tego, co dzieje się, gdy powiększysz kolumnę, ale masz problemy ze znalezieniem informacji o tym, co się stanie, gdy ją zmniejszysz.)

Niektóre tabele mają bardzo małą liczbę wierszy, więc nie martwię się kosztem zmiany, ale niektóre są dość duże i martwię się, że potencjalnie mogą się one zreorganizować i spowodować wiele blokad / przestojów. W praktyce chcę tylko oszacować okno konserwacji. Ogólnie chciałbym lepiej zrozumieć, jak zachowuje się silnik bazy danych w tym przypadku.

Z góry dziękuję!

EDYTOWAĆ:

Mam 20 tabel, na które patrzę, chociaż tylko połowa z nich ma liczbę wierszy większą niż 1000. Największy ma prawie milion wierszy. Najgorszym sprawcą jest stół z 350 000 rzędami i czterema VARCHAR(MAX)kolumnami, które można zmniejszyć do VARCHAR(500)poziomu.

Po pierwsze: ile danych jest w tabeli? Liczba rzędów i rozmiar tabeli?

Po drugie: czy możesz wykonać kopię zapasową i przywrócić tę tabelę na serwerze testowym i uruchomić instrukcję alter, aby zobaczyć wpływ (zakładając, że nie jest to niewykonalne, ponieważ tabela jest zbyt duża, aby zmieściła się w systemie nieprodukcyjnym)? Zawsze uważam, że testowanie w moim środowisku jest dokładniejsze niż porady interwebs, ponieważ istnieje kilka czynników, które mogą wpłynąć na wynik, których nie można podać w pytaniu po prostu z powodu nieświadomości, że czynniki te mogą wpłynąć na wynik.

Po trzecie: zwiększenie wielkości pola o zmiennej długości jest (zakładając, że nie przekroczysz limitu 8060 bajtów) prostą operacją na metadanych, ponieważ dla takiej operacji nie zmieniłyby się żadne rzeczywiste dane. ALE, z drugiej strony, zmniejszenie wielkości pola o zmiennej długości, nawet do czegoś, co będzie bardziej niż oczywiste, nie jest prostą zmianą metadanych, ponieważ SQL Server nie wie przed skanowaniem wszystkich wierszy , że nowo żądany rozmiar jest prawidłowy.

Dlatego: Tak, to zablokuje tabelę na pewien czas . Ile czasu? Oto test, który właśnie wykonałem:

Miałem, z niektórych innych testów, tabelę z jednym INT NOT NULLpolem i milionem wierszy. Skopiowałem go do nowej tabeli w celu wykonania tego testu przez:

SELECT *, CONVERT(NVARCHAR(MAX), NEWID()) AS [StringField]
INTO dbo.ResizeTest
FROM dbo.ClusteredUnique;

W ten sposób zacząłem od podobnego scenariusza posiadania MAXpola (właśnie zdałem sobie sprawę, że masz VARCHARi używam NVARCHAR, ale to nie powinno zmienić zachowania, które widzę), na które mógłbym następnie zmienić 500. I ma w sobie dane, które mogą łatwo zmieścić się w 500 znakach. Zajęło to kilka minut.

Potem pobiegłem:

ALTER TABLE dbo.ResizeTest ALTER COLUMN [StringField] NVARCHAR(500) NULL;

A to zajęło nieco ponad 11 minut.

Właśnie ponownie uruchomiłem test, tym razem upuszczając [ResizeTest]stół i zmieniając oba NVARCHARs, aby były sprawiedliwe VARCHAR, tylko dla pewności, że porównuję jabłka do czegoś, co przynajmniej wygląda jak jabłko ;-).

Początkowe utworzenie stołu zajęło 20 sekund, a ALTER TABLEzajęło 2 minuty.

Tak więc, jeśli chodzi o szacowanie przestojów, jest to naprawdę trudne, ponieważ opiera się na prędkościach operacji we / wy dysku, niezależnie od tego, czy w pliku danych i / lub dzienniku transakcji muszą wystąpić jakiekolwiek operacje automatycznego wzrostu. jest prawdopodobnie dużą częścią tego, dlaczego mój pierwszy test zmienił się po 11 minutach, a drugi, mimo VARCHARże jest o połowę mniejszy od NVARCHARdanych, zajął tylko 2 minuty (tj. pliki zostały wcześniej wyhodowane). Ale nadal należy pamiętać, że mój test jest uruchomiony na moim laptopie, który nie jest najszybszym dyskiem, ale był to również zaledwie milion wierszy z 2 małymi kolumnami (22 bajtów na wiersz).

A ponieważ zapytałeś, co zrobi ze stronami danych, oto twoja odpowiedź. Zrobiłem sp_spaceusedpo utworzeniu tabeli, po zrobieniu ALTER COLUMNi po zrobieniu ALTER TABLE dbo.ResizeTest REBUILD;. Wyniki (następujące liczby są oparte na drugim teście VARCHAR, a nie na pierwszym teście NVARCHAR):

After initial table creation:        526,344 KB
After ALTER COLUMN VARCHAR(500):   1,031,688 KB  <--- !! Yikes!!
After ALTER REBUILD:                 526,472 KB

Jeśli obawiasz się, że operacja będzie możliwie najkrótsza, sprawdź artykuł, który napisałem na ten temat: Restrukturyzuj 100 milionów wierszy (lub więcej) tabel w kilka sekund. SRSLY! (wymagana darmowa rejestracja).

Z tego, co zebrałem, uruchomienie instrukcji alter nie powinno zająć zbyt długo, dopóki stół nie jest zablokowany przez inny proces. Według gbn to tylko zmiana metadanych: /programming/7261909/is-it-bad-to-use-alter-table-to-resize-a-varchar-column-to-a-larger -rozmiar

Ponadto, jeśli chodzi o sposób przechowywania, wygląda na to, że SQL Server przechowywał dane varchar na stronie 8k, dopóki nie zapełni całej strony, która w tym momencie zastępuje ją wskaźnikiem i przechowuje jako BLOB.

Zakładam, że kiedy zmienisz długość, nie obetniesz żadnych rekordów. Jeśli tak, to maksymalnie dane, które konwertujesz na varchar (500), powinny mieć najwyżej 502 bajtów długości i nie powinny mieć wskaźnika.

Krótko mówiąc, niewiele powinno się zmienić, dopóki nie obetniesz żadnych danych.