Комбинация от различни видове компресия с помощта на ROW и PAGE алгоритми, Windows IT Pro

База данни случай "на диета"

Във втората статия показах как алгоритмите ROW и PAGE променят вътрешната структура на страниците на базата данни и обясних защо PAGE компресията може да намали данните толкова ефективно.

ROW компресия или PAGE компресия?

Все още не съм срещал система, в която използването на ROW компресия би довело до поне малък проблем. Повече редове се побират на страницата, броят на I / O операциите е намален, броят на редовете, разпределени за буферите на паметта, се увеличава - всичко изглежда показва ефективността на такова решение.

Компресирането според алгоритъма PAGE е друг въпрос. При използването му не се разглежда въпросът как да се компресират данните, поставени в определен ред. Решенията се вземат по отношение на цялата страница с данни като цяло. Този метод е подходящ, когато искате да извлечете данни или когато трябва да извършите процедура за вмъкване в края на таблица, но не и в ситуации, в които операторът трябва постоянно да променя съдържанието на редове на тези страници.

Докато използването на ROW навсякъде изглежда работи, прилагането на PAGE компресия към всички таблици вероятно ще доведе до влошаване на производителността при извършване на модификации на данни.

Следователно, оптималното решение за повечето системи обикновено се счита за използването на ROW и PAGE алгоритми в една или друга комбинация, вместо да се избира един от тях, като се изключва другият.

Същото важи и за индексите. Докато PAGE компресията може да се счита за жизнеспособен метод за намаляване на размера на таблица, оптималното решение за неклъстерни индекси на същата таблица еможе би ще има компресия според алгоритъма ROW. Всяка комбинация от алгоритми е разрешена.

Ниво на раздел

След това ще представя друга основна концепция относно компресирането на таблицата. Въпреки че преди това разгледахме цели таблици или индекси, компресията може да се приложи и на ниво дял, а не само на ниво таблица или индекс.

Различните части на всяка маса могат да се използват по различни начини. Например, промените, направени в таблица, съдържаща историята на транзакциите, най-вероятно ще бъдат върху най-новите данни; по-старите данни в същата таблица обаче може изобщо да не бъдат засегнати.

От това следва, че една разумна стратегия за работа с такава таблица може да изглежда като тази, показана на фигурата.

видове
Рисуване. Използвайте случай за компресиране на таблица

Когато се прилага PAGE компресия

Разработчиците на Microsoft започнаха да оборудват системите на SQL Server с изгледи за динамично управление (DMV) още в SQL Server 2005 и оттогава ги подобриха. Можем да използваме DMV, за да определим как се използва определена таблица или индекс. DMV sys.dm_db_index_operational_stats може да бъде от голяма помощ по този въпрос. Изгледът включва колони, показващи колко често се извършват сканирания и се прилагат различни видове актуализации. Умишлено изключих таблици, подготвени от Microsoft (вижте списък 1).

aspx) и актуализиран въз основа на споменатия код. В това ръководство Санджай Мишра е представил изчерпателен набор от препоръки. Моят опит потвърждава много от точките, посочени в него.

Като цяло препоръчвам в повечето случаиизползвайте ROW компресия, но на дялове, които се проверяват повече от 70% от времето и се актуализират по-малко от 15% от времето, според мен трябва да се използва PAGE компресия. Тези числа не се различават много от тези, дадени в това ръководство и се основават на решения, които намирам за ефективни.

В нашия случай бихме могли да пренапишем кода в листинг 1, за да вземем предвид предложените насоки (вижте списък 2).

Имайте предвид, че кодът в листинг 2 работи със статистика на индекса, която в момента е в системната памет. Тези статистики се изтриват при рестартиране на сървъра. По принцип не препоръчвам да следвате тези насоки, освен ако услугата SQL Server не е работила без прекъсване поне три месеца.

Пишем INSERT - четем UPDATE?

Обикновено кодът в листинг 2 върши добра работа, като дава препоръки. Но има и друг сценарий, който бих искал да разгледам по-отблизо. В горния код ние третираме операциите INSERT като вид модификация. В известен смисъл това е вярно, но операциите INSERT не винаги имат същия ефект върху процедурата за компресиране на PAGE като другите модификации на данни.

Заключавам, че ако всички вмъквания в таблица са в края на клъстериран индекс за таблицата, тогава мога да игнорирам тези вмъквания за целите на препоръката. За съжаление, няма лесен начин програмно да се определи дали това е така.

Понякога можем да използваме много просто решение. Говорим за случаи, в които таблицата има колона IDENTITY и тази колона е клъстерен ключ (и често, макар и не винаги,първичен ключ). В такива ситуации промените на страницата, които привличат вниманието ни, когато анализираме компресията на PAGE, изглежда нямат голямо значение.

Това обаче не е единственият начин да разберем какво всъщност се случва и в много случаи можем да определим реда, в който се извършват операциите INSERT само ако имаме конкретна информация за приложението.

Прилагане на компресия на ROW или PAGE

Компресията се извършва в момента, в който данните се въвеждат в страниците, така че за да получим ефекта от компресията, трябва да пренапишем всички страници. Това може да се направи чрез използване на командата ALTER INDEX за повторно изграждане на индекс или командата ALTER TABLE за повторно изграждане на купчината или двоичното дърво HoBT за дадена таблица.

Като пример за прилагане на ROW компресия към всички индекси в таблицата Production.Product в базата данни AdventureWorks, можем да изпълним следния кодов фрагмент:

И така, какво общо има всичко това с нашата клиентска база данни, която трябва да "мине на диета"? Както вече отбелязах, след прилагане на компресия към всички данни чрез метода ROW, размерът на базата данни беше намален от 3,8 на 2,6 TB. След селективно компресиране на отделни компоненти с помощта на алгоритъма PAGE, в съответствие с препоръките, посочени в статията, размерът на базата данни беше намален до 1,4 TB, докато производителността на приложението се увеличи. Въпреки че използването на процесора на страница се е увеличило, струва си да се отбележи, че огромното намаляване на страниците компенсира значителна част от това натоварване.

И какво, "отслабването" свърши? Нищо не се е случило. Направих много допълнителни манипулации на въпросната база данни и ще говоря за тях по-нататъкстатия. Големите XML низове и стойности все още не са компресирани, така че това ще направим.

Споделяйте материал с колеги и приятели