Багато адміністраторів Microsoft SQL Serverстикалися з проблемою значного збільшення фізичного розміру бази даних і файлів журналу транзакцій і, звичайно ж, їм хотілося б якимось чином зменшити цей розмір, щоб не робити будь-які дії, пов'язані зі збільшенням вільного простору на жорсткому диску. Спосіб зменшити фізичний розмірбази даних та файлів журналу транзакцій у SQL серверіє – це стиск.
Що таке стиснення в Microsoft SQL Server?
Стиснення- це процес видалення простору, що не використовується, у файлах бази даних і журналу транзакцій.
Фізичний розмір файлів бази даних з часом зростає, це пов'язано з додаванням даних, але при їх видаленні фізичний розмір файлів залишається незмінним, проте в даних файлах з'являється логічне простір, що не використовується, яке і можна видалити.
Найбільший ефект від стиснення досягається тоді, коли операція стиснення виконується після операції видалення таблиць з БД або видалення даних таблиць.
Слід відрізняти процедуру стиснення журналу транзакцій від процедури усічення журналу транзакцій. Стиснення - це зменшення фізичного розміру журналу за рахунок видалення простору, що не використовується, а усічення - це звільнення місця в логічному журналі для повторного використання (тобто. утворюється простір, що не використовується) журналом транзакцій при цьому розмір фізичного файлуне зменшується.
Усічення журналу транзакцій відбувається автоматично:
- У простій моделі відновлення – після досягнення контрольної точки, яка може виникнути, наприклад, після створення BACKUPбази даних, при явному виконанні інструкції CHECKPOINT, або коли розмір логічного журналу транзакцій заповнюється на 70 відсотків, у всіх цих випадках відбувається автоматичне очищеннянеактивної частини журналу, тобто. його усічення;
- У моделі повного відновленняабо в моделі відновлення з неповним протоколюванням - після створення резервної копії журналу за умови, що з моменту створення останньої резервної копії журналу було досягнуто контрольної точки.
Якщо Ви використовуєте модель повного відновлення або модель відновлення з неповним протоколюванням і у Вас файли журналу транзакцій занадто великі, то швидше за все Ви досить довго не робили BACKUP ( резервну копію) журналу транзакцій. У даному випадкуВам необхідно зробити спочатку BACKUP журналу транзакцій, а потім виконати стиснення журналу транзакцій, яке ми якраз і розглянемо трохи нижче.
Також можливий розмір файлів журналу транзакцій занадто великий ( як при простій, так і при повній моделі відновлення) з допомогою затримки процедури усічення, тобто. Розмір журналу, складається в основному з активної частини журналу, а активну частину зрізати не можна, тому фізичний розмір журналу зростає. На затримку процедури усічення впливають такі фактори як: активні тривалі транзакції, деякі сценарії відображення дзеркальних баз даних та журналу транзакцій, деякі сценарії при реплікації транзакцій та журналу транзакцій, а також усічення журналу неможливе під час операцій резервного копіюваннята відновлення даних. В даному випадку Вам потрібно усунути причини затримки, потім зробити усічення ( тобто. наприклад, для повної моделі відновлення BACKUPжурналу), а потім стиск до прийнятних розмірів.
Зазвичай якщо на постійній основіз певною періодичністюстворюються резервні копіїжурналу транзакцій чи бази даних ( при простій моделі відновлення), файли журналу транзакцій не зростають, і не виникає переповнення журналу транзакцій.
Як стиснути базу даних у MS SQL Server?
Стиснути файли бази даних та журналу транзакцій можна і за допомогою графічного інтерфейсу Management Studio та за допомогою інструкцій Transact-SQL: DBCC SHRINKDATABASEі DBCC SHRINKFILE. Також можна налаштувати базу даних на автоматичне стискування шляхом виставлення параметра БД AUTO_SHRINK у значення ON.
Примітка! Стиснення бази даних я розглядатиму на прикладі Microsoft SQL Server 2016 Express.
Стискаємо базу даних за допомогою середовища Management Studio
Запускаємо Management Studio і в браузері об'єктів відкриваємо об'єкт « Бази даних». Потім клацаємо правою кнопкоюмиші по БД, яку необхідно стиснути, далі вибираємо Завдання ->Стиснути -> База даних (або Файли, якщо, наприклад, потрібно стиснути лише журнал транзакцій)». Я для прикладу вибираю База даних».
У результаті у Вас відкриється вікно. Стиснення бази даних», в якому Ви, до речі, можете спостерігати розмір бази даних, а також доступне вільне місце, яке можна видалити ( тобто. стиснути). Натискаємо « ОК».
Через деякий час, залежно від розміру бази даних, стиснення буде завершено.
Стискаємо базу даних за допомогою інструкцій SHRINKDATABASE та SHRINKFILE
У MS SQL Server для виконання стиснення файлів бази даних та журналу транзакцій існують дві інструкції SHRINKDATABASE та SHRINKFILE.
- DBCC SHRINKDATABASE- Це команда для стиснення бази даних;
- DBCC SHRINKFILE– за допомогою цієї команди можна виконати стиск деяких файлів бази даних ( наприклад, лише журналу транзакцій).
Для того щоб виконати стиск БД ( наприклад, TestBase) Так само як ми це зробили трохи раніше в Management Studio, виконайте таку інструкцію.
DBCC SHRINKDATABASE(N"TestBase")
SHRINKDATABASE має такі параметри:
- database_name або database_id – ім'я або ідентифікатор бази даних, яку необхідно стиснути. Якщо вказати значення 0, то використовуватиметься поточна база даних;
- target_percent – вільний простіру відсотках, що має залишитися у базі даних після стиснення;
- NOTRUNCATE – стискає дані у файлах за допомогою переміщення розподілених сторінок з кінця файлу на місце нерозподілених сторінок на початку файлу. Якщо вказано цей параметр, фізичний розмір файлу не змінюється;
- TRUNCATEONLY - звільняє весь вільний простір у кінці файлу операційній системіале не переміщує сторінки всередині файлу. Файл даних скорочується лише до останнього виділеного екстенту. Якщо зазначений цей параметр, то параметр target_percent не обробляється;
- WITH NO_INFOMSGS - придушує все інформаційні повідомленняіз ступенями серйозності від 0 до 10.
Синтаксис SHRINKDATABASE
DBCC SHRINKDATABASE (database_name | database_id | 0 [ , target_percent ] [ , ( NOTRUNCATE | TRUNCATEONLY ) ]) [ WITH NO_INFOMSGS ]
Для того, щоб стиснути тільки журнал транзакцій, можна використовувати інструкцію SHRINKFILEнаприклад.
DBCC SHRINKFILE (N"TestBase_log")
В даному випадку ми здійснимо стиснення файлу журналу ( TestBase_log – це назва файлу журналу транзакцій), до початкового значення, тобто. до значення за замовчуванням. Щоб стиснути файл до певного розміру, вкажіть другим параметром розмір у мегабайтах. Наприклад, наступною інструкцієюми зменшимо розмір файлу журналу транзакцій до 5 мегабайт.
DBCC SHRINKFILE (N"TestBase_log", 5)
Також необхідно врахувати, що якщо Ви вкажете розмір менше того, ніж потрібно для зберігання даних у файлі, файл до цього розміру стиснутий не буде. Наприклад, якщо ви вказали 5 мегабайт, а для зберігання даних у файлі потрібно 7 мегабайт, файл буде стиснутий лише до 7 мегабайт.
SHRINKFILE також має параметри NOTRUNCATE та TRUNCATEONLY.
Синтаксис SHRINKFILE
DBCC SHRINKFILE (( file_name | file_id ) ( [ , EMPTYFILE ] | [ [ , target_size ] [ , ( NOTRUNCATE | TRUNCATEONLY ) ] ])) [ WITH NO_INFOMSGS ]
- Операція стиснення бази даних може викликати фрагментацію індексів та уповільнити роботу БД. Тому занадто часто не рекомендується виконувати стиснення бази даних;
- Стискати БД краще до операції перебудови індексів, тобто. після стиснення запустіть процедуру перебудови індексів;
- Параметр бази даних AUTO_SHRINK ( автоматичний стиск) краще не виставляти значення ON, а залишати за замовчуванням, тобто. у OFF, якщо, звичайно, у Вас немає на це достатньо серйозних підстав;
- Інструкція SHRINKDATABASE не дозволяє зменшити розмір бази даних до розміру, меншого за початковий, тобто. мінімального. Однак інструкція SHRINKFILE зробити це може ( другим параметром вказуємо розмір менший за мінімальний). Мінімальний розмірбази даних - це розмір, який вказаний при створенні бази даних або явно встановлений операцією зміни розміру бази даних, такий як DBCC SHRINKFILE або ALTER DATABASE. Наприклад, якщо база даних була створена з розміром 10 мегабайт, потім збільшилася до 100 мегабайт, її можна стиснути за допомогою SHRINKDATABASE тільки до початкових 10 мегабайт, навіть якщо всі дані були вилучені з бази даних;
- Стискати файли бази даних та журналу транзакцій не можна, коли йде їх резервування. І навпаки, створювати резервні копії бази та журналу транзакцій не можна поки що йде процес їх стиснення;
- Виконання інструкції DBCC SHRINKDATABASE без вказівки параметра NOTRUNCATE або TRUNCATEONLY рівносильне до виконання інструкції DBCC SHRINKDATABASE з параметром NOTRUNCATE після виконання інструкції DBCC SHRINKDATABASE з параметром TRUNCATEONLY;
- У процесі стиснення бази даних користувачі можуть працювати в ній ( тобто. переводити БД в однокористувацький режим не потрібно);
- У будь-який час Ви можете перервати процес виконання операцій SHRINKDATABASE і SHRINKFILE, при цьому вся виконана робота зберігається;
- Перед запуском процедури стиснення перевірте, чи є вільний простір видалення файлах бази даних, тобто. можна взагалі стиснути файли, виконавши наступний запит ( він покаже у мегабайтах, на скільки Ви можете зменшити файли БД).
На цьому у мене все, сподіваюся, стаття була Вам корисна, удачі!
Стиск даних у SQL Server 2008.
Нещодавно мені довелося виконувати міграцію свого сховища даних із SQL Server 2005 на SQL Server 2008. Як відомо, однією з нововведень SQL Server 2008 є стиснення даних. Ця можливість покликана збільшити продуктивність бази даних за рахунок стиснення даних та індексів у таблицях та індексованих уявленнях і, як наслідок, зменшення операцій введення-виведення. Також, завдяки стиску, може суттєво зменшиться розмір бази, що полегшує адміністрування та управління. Все це звучало привабливо, і я вирішив використати цю можливість.
Причиною, що спонукала мене більш уважно розібратися з даною функціональністю, і зрештою написати цю статтю, стало отримання зовсім іншого результату, ніж очікувалося:)
Я вирішив провести стиснення трьох найбільших таблиць фактів, що являють собою структури, з максимально компактними, наскільки це можливо, стовпцями, що посилаються на довідники-вимірювання та містять ряд показників, які агрегуються у звітах. Нижче буде наведено типову структуру таблиці фактів та типовий запит до цієї таблиці у спрощеному вигляді.
Але на мій подив, після стиснення, я не тільки не отримав приросту продуктивності, але навпаки - виконання запитів сповільнилося. У деяких випадках продуктивність залишилася на колишньому рівні.
Було вирішено дослідити ефект від стиснення даних та провести тестування, щоб відповісти на питання щодо падіння продуктивності.
Підготовка тесту.
Отже, є таблиця, назвемо її ProductMMR містить деякі факти у кількох розрізах.
Ось її структура:
Склад
Товар
Дата
Тип складу
Кількість
Вихідний розмір таблиці – 16 ГБ, індексів – 18 ГБ (я скористався системною ХПsp_spaceused для визначення розмірів даних та індексів).
Тепер саме час вирішити за якими критеріями оцінюватимемо ефективність стиснення.
Оскільки практично кожного розробника чи адміністратора хвилює продуктивність його серверів і додатків, очевидно, що головним критерієм оцінки ефективності стиснення буде час, витрачений виконання запитів, кількість операцій вводу-вывода, кількість витраченого процесорного часу. Також буде прийнято до уваги те, що звільнилося дисковий простірвнаслідок стиснення.
Ось тестовий запит на вибірку до цієї таблиці:
SET STATISTICS TIME ON - для вимірювання часу виконання запиту
SET STATISTICS IO ON -- для вимірювання логічних та фізичних операцій вводу-виводу
GO
SELECT
fact.DateID,
fact.StockID,
SUM(fact.Qty) AS Qty
FROM fact.ProductMMR fact
WHERE (fact.DateID BETWEEN @DateIDBegin AND @DateIDEnd)
GROUP BY fact.DateID, fact.StockID
Був заданий часовий проміжок 30 днів (у таблиці фактів це більше 150 млн. записів).
Визначення стратегії стиснення та її реалізація.
Детально прореалізацію стиску можна почитати у MSDN. Там же описана реалізація стиснення длясторінок і длярядків . Ефект від стиснення залежить від даних у таблиці - наскільки багато там повторюваних значень і який тип даних.
Тепер перейдемо до реалізації стиснення, попередньо визначивши його стратегію.
Sunil Agarwal У своємублозі наводить ряд рекомендацій з цього приводу, дозволю собі їх узагальнити та навести тут:
1. Немає сенсу стискати дані чи індекси мають малий розмір. Якщо таблиця займає 10 сторінок, і буде стиснута до однієї, це не принесе вигоди у разі. Необхідно пам'ятати про те, що стислі дані повинні бути розпаковані щоразу, коли до них здійснюється доступ. Перед застосуванням стиснення необхідно оцінити поточний розмір таблиці/індексу та прогнозований розмір після стиснення.
2. Якщо таблиця інтенсивно використовується операторами DMLта SELECT, то в результаті стиснення ви можете отримати надмірне навантаження на процесори в результаті розпакування при кожному зверненні до цих даних. В цьому випадку необхідно особливо ретельно підійти до питання доцільності стиснення таблиці/індексу.
3. Якщо економія від стиснення невисока, стиснення проводити не рекомендується. Бувають такі випадки, коли розмір стислих даних виявляється більшим за стислий. Це говорить про те, що в таблиці використовуються найбільше компактні типиданих.
4. Якщо у вас типова OLTP-додаток, у загальному випадку вам слід вибирати стиск типу ROW. Цей тип стиснення менш витратний з погляду розпакування даних. Однак, як правило, стиснення типу PAGE є більш ефективним, у плані потенційного вільного простору.
Оцінити вигоду від стиснення можна або в майстрі, або за допомогою процедури, що зберігається.sp_estimate_data_compression_savings.
У моєму випадку я отримав такі результати:
Таблиця 1.
Ефективність стиснення даних.
Тип стиску | Розмір до стиснення | Розмір після стиснення | % стиснення |
ROW | 33,4 ГБ | 22,7 ГБ | 32 % |
PAGE | 33,4 ГБ | 18,3 ГБ | 45 % |
Як видно з таблиці, отримати ефект від стиснення даних можна. Хоча в даному випадку це не самий гарний показникУ багатьох випадках дані стискаються на 70-80%.
У багатьох випадках коефіцієнт стиснення буде більшим, а в деяких - набагато більшим, ніж вийшов у мене в цьому тесті. Все залежить від типів даних і від того, наскільки багато даних, що повторюються, у ваших таблицях.
Видно, що в тестовій таблиці стиснення на рівні рядків буде не таким ефективним, як стиснення сторінок. Необхідно взяти до уваги, що стиснення PAGE є надмножиною стиснення ROW.
Реалізувати стиснення таблиці типу PAGE/ROW можна через майстер стиснення, що генерує такий код:
ALTER TABLE. REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = ROW
)
Застосувати стиснення типу PAGE можна за допомогою параметра DATA_COMPRESSION = PAGE.
За допомогою DATA_COMPRESSION = NONE можна скасувати стиснення даних.
Я не буду наводити тут синтаксис стиснення індексів і партій, який цікавиться знайде їх в BOL.
Потрібно пам'ятати, що перед увімкненням або вимкненням стиснення рядка або сторінки необхідно стільки ж місця на диску, як і для створення або перебудови індексу.
Результати тестування.
Отже, до і після стиснення типу PAGE був виконаний тестовий запит.
Ось його результати, на «розігрітому» кеші:
Таблиця 2.
Результати тесту №1*.
Тип стиску | Час виконання запиту (мс) | Операцій логічного читання** | Витрачений процесорний час (мс) |
Без стиску | 26 147 | 1 419 113 | 308 736 |
Стиснення PAGE | 41 104 | 709 360 | 486 453 |
*Запит виконувався на сервері з 12 ядрами та 32 Гб ОЗУ, дискова підсистема 10 RAID.
** Показано лише операції логічного читання, т.к. фізичного читання був - дані перебували у кеші.
Побачивши ці результати, можна здивуватися - адже операцій логічного читання на стислих даних було зроблено вдвічі менше, але виконання запиту виявилося на 36 % більше. А вся справа мабуть у тому, що хоч операцій читання менше і читається все з пам'яті, але великі витрати на розпакування даних. Адже розпаковується не сторінка цілком, а кожен запис окремо.
Можна припустити, що якщо дані будуть незакешовані, то в цьому випадку можна досягти приросту продуктивності за рахунок зменшення кількості дорогих операцій фізичного читання у разі стислих даних.
Тому вирішили провести ще один цикл тестів, але вже на холодному кеші.
Був виконаний той же тестовий запит, але попередньо був очищений кеш процедур і буфер, за допомогою командDBCC FREEPROCCACHE і DBCC DROPCLEANBUFFERS .
Ось результати тестового запиту до і після стиснення на «холодному» кеші:
1 419 105
1 420 868
235 266
Стиснення даних PAGE
48 887
707 495
710 105
416 689
Ось ці результати підтверджують висловлене раніше припущення. Як видно, час виконання відрізняється на 12% замість 36% з першого тесту.
Статистика з операцій логічного читання така сама, але в даному тестіє фізичне читання, яке суттєво позначається на продуктивності запиту (порівняйте час виконання з першим тестом). І, судячи з усього, стиснення даних даватиме позитивний ефект у плані продуктивності тоді, коли виконуватимуться запити до рідко використовуваних великих масивів даних, стиснення яких дозволитьзаощадити на операціях фізичного читання , У порівнянні з якими розпакування даних з буферного пулу менш дорога операція. Гарним прикладомможе бути стиснення партій з даними за ранні періоди в сховищі або стиснення інших великих рідко використовуваних таблиць. Нагадаю, що стискати дані можна як на рівні таблиць, так і на рівні партій та індексів. Причому можна поєднувати типи стиснення.
Мені вдалося домогтися те, що у тестової таблиці вибірка стислих даних, за період, стала виконуватися приблизно одному рівні, з вибіркою стиснутих даних у той самий період, тобто. що більше масив даних, яких звертався запит, то позитивний ефект від стиску було більше, т.к. серверу спочатку доводилося звертатися за великим обсягом даних до дисковій системіа чи не буферному пулу.
Але сама Головна причинатого, що в моєму випадку впала продуктивність запитів – це відносно невисокий коефіцієнт стиснення менше 50 %. Я провів ще кілька тестів і виявив, що на таблицях, які стискалися на 60-75 %, продуктивність запитів збільшувалася порівняно з нестислими таблицями.
Очевидно, що чим вищий відсоток стиснення, тим сильніше це позначиться на прирості продуктивності.
У будь-якому випадку, перш ніж приступати до цієї операції, необхідно провести тестування та оцінити ефект від стиснення даних.
Сергій Харибін, MCTS SQL Server.
I) Проблеми, які ми намагаємося вирішувати пакунком БД
1) Збільшення розміру БД
2) Низька продуктивністьвиконання запитів
3) Великий обсяг"непотрібних даних" які заважають роботі користувачів
а) Поділ БД на файлові групи
б) Розміщення БД або її частини мережному диску
в) Стиснення таблиць БД
г) Секціонування таблиць БД
2) Проблема низької продуктивності запитів
3) Проблема великого обсягу "непотрібних даних", які заважають роботі користувачів
У сучасних умовахдуже дивно іноді буває чути " нам потрібно згорнути БД 1С - її обсяг перевищує 50 ГБ " . Якби таке збиралися зробити адміністратори систем SAP R3 або Oracle e Business Suite або навіть MS Dynamics Ax їх напевно звільнили б. Тим не менш, для 1С це є "стандартною практикою".
Для файлових версійісторія тягнеться ще з версії 1С 7.7 з обмеженням 2ГБ на розмір бази. Тепер обмеження 2ГБ вже тільки на розмір таблиці, розмір файлу вже може вийти дуже і невеликим. Щоправда, якщо база у вас зросла до такого розміру, то напевно туди активно вносилися дані - може потрібно задуматися про клієнт-сервер?
Власне метою цієї статті є "відмовити" від виконання згортки БД користувачів клієнт-серверного варіанту 1С, за рахунок використання дещо більш "просунутих" технологій.
I) Проблеми, які ми намагаємося вирішувати пакунком БД
1) Збільшення розміру БД
Власне головне питання: а навіщо зменшувати розмір БД?
Давайте прикладемо трохи математики:
Серверний жорсткий дискна 500 ГБ коштує близько 10 УРАХУВАННЯМ. Об'єднати в RAID 1 для надійності буде 20 УРАХУВАННЯМ.
Природно, можуть бути проблеми відсутності місця під нові. жорсткі дискиу самому сервері...
А придбання зовнішнього дискового масиву вже обійдеться не так дешево. Що ж робити?
Та все просто – розмістити файли БД на мережному диску, а як? Ну про цю статтю далі.
Збільшення доступного для БД дискового простору обійдеться нам у 20 УРАХУВАННЯМ. + 10 хвилин роботи спеціаліста. Скільки годин роботи фахівця вимагатиме згортка БД? А скільки часу простою може вийти? За найскромнішими оцінками за згортку УПП обсягом гігабайт у 60, із середньою кількістю помилок, партійним обліком з перевіркою результатів згортки, виправлення цього ж партійного обліку візьмуться тисячі за 30-40.
Універсальною обробкою все і відразу навряд чи згорнеться, особливо якщо у вас база практично ніколи не зупиняється. Партійний облік у будь-якому випадку виправлятиме. Загалом багато там роботи. А найголовніше, що підсумкова перевірка має бути дуже ретельною, і все одно залишаться помилки.
Крім того, якщо база вже розміром не 60, а, наприклад, 120 ГБ... найменша помилка в коді 1С при згортці і все... процедура закінчується не вдало. А помилки точно будуть. Як "недостатньо пам'яті" при роботі з ТЗ, так і помилки на кшталт
Підсумкова цифра виходить 30-40 т. мінімум проти 20-25 у разі покупки жорсткогодиска, та отримання 500 ГБ додаткового місця
Тому з'являються продукти на кшталт [необхідно зареєструватися для перегляду посилання]
Гарні напевно продукти, і цілі свої виконують. Ось тільки змінюється структура таблиць від версії до платформи. 1З нам про це не раз говорили. З'явився роздільник даних у 14-му релізі і все... швидше за все, ця обробка для 14 релізу вже не підійде. Та й страшно якось, не говорячи вже про порушення ліцензійної угоди.
І навіть після цього знайдуться користувачі, яким "раптом несподівано знадобилися" стерті дані, які "якраз хотіли поправити" якусь циферку, яка "не впливає на послідовність" у документі закритого періоду. А гірше, якщо з'ясується, що хтось ці документи дивився постійно для якихось тільки йому ведених цілей. Звичайно це все лише помилки в методиці роботи, але невдоволення користувачів буде.
2) Низька продуктивність виконання запитів
Ну хто ж вам сказав що "що менше тим швидше"? Для коректно розробленої ІВ це твердження неправильне.
На малюнку нижче коротко і "пташиною мовою" наведено найпростіший прикладвибірки за індексом типу B-Дерева запису в таблиці адрес:
У тему індексів заглиблюватися не хочу, тим більше там дещо складніше. Найголовніше - пошук проходить не "горизонтально" за таблицею, а "вертикально" за "рівнями" індексу.
Аналогія - записна книжка: Кожна сторінка починається зі своєї літери, тільки ось на кожній сторінці ще така ж записник в якій ви можете вибрати другу літеру в слові, і так до тих пір, поки не зустрінете ту сторінку, на якій буде один або кілька записів. Зручно? Звичайно зручно, якщо у вас більше кількох сотень контактів. А якщо у вас їх лише десять? Чи не простіше їх просто записати на один листочок, який можна переглянути очима? Ось і у випадку індексів так само. Він ефективний якщо в таблиці кілька тисяч записів, а от якщо одна єдина – не дуже. Благо СУБД навчилися самостійно обирати "план запиту" та вирішувати використовувати чи не використовувати той чи інший індекс. Ось тільки у разі "перебору" всіх рядків таблиці без індексу СУБД дуже часто блокує всю цю таблицю, і ви спостерігаєте "незрозуміло, звідки взялися блокування" після згортки ІБ.
Згортаємо зазвичай таблиці залишків. У таблиці залишків першим полем у всіх індексах буде період. Тобто за будь-яких запитів на самому верхньому рівнііндексу буде вже відібрано розраховані залишки на потрібний період. Отже на запити щодо залишків згортка бази матиме найменший вплив, повторюся, при правильної організаціїсистеми.
Про це ви користувачам спершу зробіть розсилку. І отримайте купу повідомлень, що "дані непотрібними не бувають". Проте багатьом не подобається "бачити документи за минулі періоди" та "архівні дані", з ними не можна не погодитися. Але чи вирішує звіряння ці проблеми? Чи видаляє вона непотрібні номенклатури з номенклатурного довідника? Контрагентів, з якими більше не вестиметься робота? А як показує практика, більшість проблем саме в цьому.
II) "Технологічне" вирішення проблем
1) Проблема збільшення розміру БД
а) поділяємо БД на файлові групи
Відкриваємо Management Studio у списку баз вибираємо потрібну, відкриваємо її властивості.
- Переходимо на вкладку "Файлові групи", як показано на малюнку, і додаємо ще одну файлову групу (на прикладі вона названа SECONDARY
Переходимо на вкладку "Файли" та додаємо новий файлдля якого вибираємо створену файлову групу. Цей файл МОЖНА РОЗМІСТИТИ НА ІНШОМУ ДИСКУ
Тепер використовуючи обробку наприклад: http://infostart.ru/public/78049/ визначаємо які таблиці ми можемо сміливо "пожертвувати" більш повільний (ну чи навпаки все на повільний, інші - більш швидкий) носій. Правило 80/20 діє. 80% операцій проводяться з 20% даними, тож думайте які таблички вам потрібні оперативно, а які не дуже. "Сховище додаткової інформації", документи введення початкових залишків, документи які вже не використовуєте відразу визначайте як ті, які можна перенести в "повільну" файлову групу.
Вибираємо таблицю, яку потрібно перенести в іншу файлову групу - вибираємо меню зміни таблиці (проект) і у властивостях змінюємо файлову групу:
індекси таблиці у своїй теж переносяться у цю файлову групу.
Досить зручний механізм розподілу таблиць дисковим масивом різної швидкості. Ліцензійна угодаце суперечить, т.к. у вирішенні ми не використовуємо доступ до даних та до інформаційної базизасобами, відмінними від платформи 1С. Ми лише організуємо зберігання цих даних зручним чином.
б) Зберігаємо БД на мережному диску
DBCC TRACEON (1807)
Пишемо цю командув Management Studio, виконуємо та можемо успішно створювати бази по мережі. Само собою при цьому екземпляр SQL Server-амає бути запущений від імені доменної облікового запису, і цей запис повинен мати права на потрібну мережеву папку.
Але прошу бути дуже уважними при використанні цієї команди, якщо у вас пропаде мережа при роботі з БД вся БД на час її відсутності буде недоступною. Майкрософт не дарма закрили цю можливість для масового використання. Взагалі ця можливість передбачається для створення баз на NAS сховищах, що й наполегливо рекомендую. Підійде так само стабільний та надійний файловий сервер, що має пряме підключеннядо сервера на якому запущено MS SQL СУБД.
Докладніше про інші прапори трасування можна прочитати у статті [необхідно зареєструватися для перегляду посилання]
Тобто. частину файлову групу можна взагалі зберігати в мережі, а там дисковий простір розширюється без проблем.
в) Стиснення таблиць бази даних
EXEC sp_MSforeachtable "ALTER INDEX ALL ON ? REBUILD WITH (DATA_COMPRESSION = PAGE)" GO
Після виконання цього коду всі таблиці БД будуть стиснуті. Очевидно, що можна стискати і таблиці окремо... це на ваш вибір. Що дає стиск?
- Економія дискового простору
- Зниження навантаження на дискову підсистему
Що витрачається? - Процесорний час.
Отже, якщо у вас процесор завантажений весь час на 70% і вище - стиснення вам використовувати не можна. Якщо 20-30% завантаження процесора, і при цьому черга до диска зростає до 3-4... то стиснення таблиць - саме "ліки" для вас. Докладніше про стиснення таблиць БД - [необхідно зареєструватися для перегляду посилання]
Важливе зауваження- функція стиснення таблиць доступна лише для власників версії Enterprise SQL Server
г) Секціонування таблиць БД
Розділити таблиці на різні файлові групи звичайно добре... але ви скажете, що є тут парочка таблиць... які тягнуться з 2005 року... і вже займають з десяток гігабайт.. ось би в них усі дані та окремий диск покласти, а поточні залишити.
Ви не повірите, але і це теж можливо, хоч і не дуже просто:
Створюємо функцію секціонування за датою:
create partition function YearSection(datetime)
as range right for values ("20110101");
Все, що до 2011 року потраплятиме в одну секцію, все, що після - в іншу.
- Створюємо схему секціонування
create partition scheme YearScheme
як партія YearSection to (SECONDARY, PRIMARY);
Цим говоримо, що всі дані до 11 року потраплятимуть у файлову групу "Secondary", а потім - у "Primary"
Тепер залишилося таблицю перебудувати з розподілом на секції. Для цього найпростіше скористатися вже management studioтому що процес не простий. Вам потрібно перебудувати кластерний індекс для таблиці (який, по суті, і є самою таблицею), вибравши для індексу створену схему секціонування:
На малюнку ви бачите, що вибір не доступний - все правильно, секціонування таблиць можливе тільки у версії Enterprise MS SQL Server. Кластерний індекс відрізнити легко – картинка з круглими дужками. Для РН та всіх об'єктів 1С він створюється. Для РН кластерний індекс за періодом є завжди. Для документів і довідників добре б звичайно створити інший, який включає реквізит, за яким буде секціонування... але це вже буде порушенням ліцензійної угоди.
2) Низька продуктивність виконання запитів.
Всі дії, описані вище, не повинні вплинути на швидкість виконання основних запитів. Більш того, використання файлових груп і секцій таблиць дозволить вам розмістити дані, що найчастіше використовуються, на швидких дискових масивах, дозволить поміняти конфігурацію дискових масивів, використовувати невеликі за розміром i/o accelerator. Таким чином швидкість виконання запитів лише підвищиться. Стиснення таблиць дозволить вам додатково розвантажити дискову підсистему, якщо вона була вузьким місцем. А взагалі якщо говорити про швидкість виконання запитів, то аналіз їх планів виконання, оптимізація запитів для грамотного використання індексів дасть набагато суттєвіший приріст продуктивності, ніж усі "хитрощі" на рівні MS SQL.
3) Великий обсяг "непотрібних даних", які заважають роботі користувачів
Але для цього потрібно не згортати базу, а зробити таке:
а) Пояснити всім, як користуватися відборами, як вони зберігаються, як користуватися інтервалами журналу, як вони зберігаються
б) Позначити на видалення непотрібні дані якщо вони не несуть ніякого смислового навантаження (контрагентів та номенклатуру, з якими більше не працюєте) - цим ви принесете користувачам більше користініж згорткою. У разі наявності ресурсів налаштувати автоматичну позначку на видалення об'єктів, що не використовуються, і зробити відбір за замовчуванням в програмному кодідля того щоб не відображалися за замовчуванням потрібні користувачамоб'єкти - позначені видалення
в) Налаштувати інші корисні "відбори за замовчуванням" - наприклад, щоб кожен менеджер за умовчанням бачив лише свої документи. А якщо хоче подивитись документи "товариша" - потрібно відключати відбір.
За всіма реквізитами, які беруть участь у відборі не забувайте ставити ознаку "Індексувати з додатковим упорядкуванням" - тоді на продуктивності системи такі "зручності" не позначаться.
Tags: 1С, 1С Оптимізація, sql, Адміністрація 1С, Навчання, Програмування, Технології
[необхідно зареєструватися для перегляду посилання]
У сучасних умовах дуже дивно іноді буває чути "нам потрібно згорнути БД 1С - її обсяг перевищує 50 ГБ". Якби таке збиралися зробити адміністратори систем SAP R3 або Oracle e Business Suite або навіть MS Dynamics Ax їх напевно звільнили б. Тим не менш, для 1С це є "стандартною практикою".
Для файлових версій історія тягнеться ще з 1С 7.7 з обмеженням в 2ГБ на розмір бази. Тепер обмеження 2ГБ вже тільки на розмір таблиці, розмір файлу вже може вийти дуже і невеликим. Щоправда, якщо база у вас зросла до такого розміру, то напевно туди активно вносилися дані - може потрібно задуматися про клієнт-сервер?
Власне метою цієї статті є "відмовити" від виконання згортки БД користувачів клієнт-серверного варіанту 1С, за рахунок використання дещо "просунутіших" технологій.
Підсумкова цифра виходить 30-40 т. мінімум проти 20-25 у разі купівлі жорсткого диска, та отримання 500 ГБ додаткового місця
Тому з'являються продукти начебто
Гарні напевно продукти, і цілі свої виконують. Ось тільки змінюється структура таблиць від версії до платформи. 1З нам про це не раз говорили. З'явився роздільник даних у 14-му релізі і все... швидше за все, ця обробка для 14 релізу вже не підійде. Та й страшно якось, не говорячи вже про порушення ліцензійної угоди.
І навіть після цього знайдуться користувачі, яким "раптом несподівано знадобилися" стерті дані, які "якраз хотіли поправити" якусь циферку, яка "не впливає на послідовність" у документі закритого періоду. А гірше, якщо з'ясується, що хтось ці документи дивився постійно для якихось тільки йому ведених цілей. Звичайно це все лише помилки в методиці роботи, але невдоволення користувачів буде.
-
Відкриваємо Management Studio у списку баз вибираємо потрібну, відкриваємо її властивості.
- Переходимо на вкладку "Файлові групи", як показано на малюнку, і додаємо ще одну файлову групу (на прикладі вона названа SECONDARY)
- Переходимо на вкладку "Файли" та додаємо новий файл, для якого вибираємо створену файлову групу. Цей файл МОЖНА РОЗМІСТИТИ НА ІНШОМУ ДИСКУ
-
Тепер використовуючи обробку наприклад: визначаємо які таблиці ми можемо сміливо "пожертвувати" більш повільний (ну чи навпаки все на повільний, інші - більш швидкий) носій. Правило 80/20 діє. 80% операцій проводяться з 20% даними, тож думайте які таблички вам потрібні оперативно, а які не дуже. "Сховище додаткової інформації", документи введення початкових залишків, документи, які вже не використовуєте, відразу визначайте як ті, які можна перенести в "повільну" файлову групу.
Вибираємо таблицю, яку потрібно перенести в іншу файлову групу - вибираємо меню зміни таблиці (проект) і у властивостях змінюємо файлову групу:
індекси таблиці у своїй теж переносяться у цю файлову групу.
Досить зручний механізм розподілу таблиць дисковим масивом різної швидкості. Ліцензійному угоді це суперечить, т.к. у рішенні ми не використовуємо доступ до даних та до інформаційної бази засобами, відмінними від платформи 1С. Ми лише організуємо зберігання цих даних зручним чином.
DBCC TRACEON (1807)
Пишемо цю команду в Management Studio, виконуємо та можемо успішно створювати бази по мережі. При цьому екземпляр SQL Server повинен бути запущений від імені доменного облікового запису, і цей запис повинен мати права на потрібну мережну папку.
Але прошу бути дуже уважними при використанні цієї команди, якщо у вас пропаде мережа при роботі з БД вся БД на час її відсутності буде недоступною. Майкрософт не дарма закрили цю можливість для масового використання. Взагалі ця можливість передбачається для створення баз на сховищах NAS, що і настійно рекомендую. Підійде також стабільний і надійний файловий сервер, що має пряме підключення до сервера на якому запущено MS SQL СУБД.
Докладніше про інші прапори трасування можна прочитати у статті http://msdn.microsoft.com/ru-ru/library/ms188396.aspx
Тобто. частину файлову групу можна взагалі зберігати в мережі, а там дисковий простір розширюється без проблем.
Розділити таблиці на різні файлові групи звичайно добре... але ви скажете, що є тут парочка таблиць... які тягнуться з 2005 року... і вже займають з десяток гігабайт.. ось би в них усі дані та окремий диск покласти, а поточні залишити.
Ви не повірите, але і це теж можливо, хоч і не дуже просто:
Створюємо функцію секціонування за датою:
create partition function YearSection(datetime)
as range right for values ("20110101");
Все, що до 2011 року потраплятиме в одну секцію, все, що після - в іншу.
Створюємо схему секціонування
create partition scheme YearScheme
як партія YearSection to (SECONDARY, PRIMARY);
Цим говоримо, що всі дані до 11 року потраплятимуть у файлову групу "Secondary", а потім - у "Primary"
Тепер залишилося таблицю перебудувати з розподілом на секції. Для цього найпростіше скористатися вже management studio, тому що процес не простий. Вам потрібно перебудувати кластерний індекс для таблиці (який, по суті, і є самою таблицею), вибравши для індексу створену схему секціонування:
На малюнку ви бачите, що вибір не доступний - все правильно, секціювання таблиць можливе лише у версії Enterprise MS SQL Server. Кластерний індекс відрізнити легко – картинка з круглими дужками. Для РН та всіх об'єктів 1С він створюється. Для РН кластерний індекс за періодом є завжди. Для документів і довідників добре б звичайно створити інший, який включає реквізит, за яким буде секціонування... але це вже буде порушенням ліцензійної угоди.
Але для цього потрібно не згортати базу, а зробити таке:
а) Пояснити всім, як користуватися відборами, як вони зберігаються, як користуватися інтервалами журналу, як вони зберігаються
б) Позначити на видалення непотрібні дані якщо вони не несуть ніякого смислового навантаження (контрагентів та номенклатуру, з якими більше не працюєте) - цим ви принесете користувачам більше користі, ніж згорткою. У разі наявності ресурсів налаштувати автоматичну позначку на видалення об'єктів, що не використовуються, і зробити відбір за замовчуванням у програмному коді для того щоб не відображалися за замовчуванням не потрібні користувачам об'єкти - помічені на видалення
в) Налаштувати інші корисні "відбори за замовчуванням" - наприклад, щоб кожен менеджер за умовчанням бачив лише свої документи. А якщо хоче подивитись документи "товариша" - потрібно відключати відбір.
За всіма реквізитами, які беруть участь у відборі не забувайте ставити ознаку "Індексувати з додатковим упорядкуванням" - тоді на продуктивності системи такі "зручності" не позначаться.
