Перейти до вмісту
Підготовка до співбесіди

Проєктування й експлуатація

Як спроєктувати схему, що переживе роки зростання, і як експлуатувати базу під навантаженням: нормальні форми й ключі, завантаження та звіти, партиціонування і зміна схеми на живій системі. На рівні Senior це розмова про компроміси й ризики, а не про синтаксис.

Перед цією темою: Індекси й плани запитів, Транзакції та блокування.

Junior+

Що таке нормалізація і коли виправдана денормалізація?

Section titled “Що таке нормалізація і коли виправдана денормалізація?”
Відповідь

Що це

Нормалізація — проєктування таблиць так, щоб один факт зберігався в одному місці. Назва магазину лежить у таблиці Store, а продаж посилається на неї за StoreId.

Нормальні форми

  1. 1НФ. У клітинці одне значення, немає груп, що повторюються: не стовпці Phone1, Phone2, Phone3, а окрема таблиця телефонів.
  2. 2НФ. За складеного ключа стовпець залежить від усього ключа. У рядку замовлення (OrderId, ProductId) назва товару залежить лише від ProductId — їй місце в Product.
  3. 3НФ. Неключовий стовпець не залежить від іншого неключового. Адреса магазину залежить від магазину, а не від продажу.

Типова помилка

Зберігати список через кому в одному стовпці: 'A12,B7,C3'. Такий список не проіндексувати, а пошук перетворюється на LIKE '%B7%'.

Як влаштовано

Нормальні форми прибирають три аномалії:

  • вставлення — не можна завести магазин, доки в нього немає продажів;
  • оновлення — адресу магазину треба змінювати в тисячах рядків, і частина копій лишиться старою;
  • видалення — видалили останній продаж і втратили дані про магазин.
Форма Вимога
1НФ атомарні значення, немає груп, що повторюються
2НФ неключові стовпці залежать від усього складеного ключа
3НФ неключові стовпці не залежать один від одного
BCNF будь-який детермінант — потенційний ключ

Коли що обирати

  • Транзакційна база — 3НФ. Запис дешевий, дані несуперечливі, читання пришвидшують індекси.
  • Денормалізація — коли виміряно, що з’єднання чи агрегати на гарячому шляху дорогі: підсумок у шапці чека, залишок товару, звітна таблиця.

Пастки

  • Історія — не дубль. Ціна в рядку чека — ціна на момент продажу, окремий факт. Посилання на поточну ціну з довідника спотворило б старі чеки.
  • Копія без власника. Денормалізований стовпець змінюють з кількох місць, і він розходиться з джерелом.

Правило

Спершу 3НФ, денормалізація — за вимірюваннями та з явним механізмом синхронізації.

Під капотом

  • Функціональна залежність X → Y: значення X однозначно визначає Y. 3НФ допускає залежність, якщо Y входить до потенційного ключа; BCNF забороняє і її.
  • Розбіжність 3НФ і BCNF буває лише за кількох складених ключів, що перетинаються, тому на практиці таблиця в 3НФ майже завжди вже в BCNF.

Ціна денормалізації

Спосіб Хто синхронізує Ціна
Обчислюваний стовпець PERSISTED рушій лише вирази за своїм рядком
Індексоване подання рушій, у тій самій транзакції дорожчий запис у базові таблиці, багато обмежень
Стовпець-агрегат: підсумок, залишок код або тригер гарячий рядок, ризик розбіжності
Звітна таблиця завантаження за розкладом дані відстають

Компроміси

  • Денормалізація переносить вартість з читання на запис. Залишок товару в одному рядку стає точкою конкуренції: усі продажі товару оновлюють його.
  • Звітні бази денормалізують навмисно: їх пише одне завантаження, а читають багато запитів.
  • Перебір теж шкодить. Модель EAV («сутність — атрибут — значення») заради гнучкості втрачає типи й обмеження, а кожен атрибут перетворюється на з’єднання.

Як перевірити в продакшені

  • Регулярне звіряння: агрегат проти перерахунку з вихідних рядків.
  • Гарячий рядок-агрегат видно за очікуваннями блокувань LCK_M_U і LCK_M_X на одному ключі.

Підсумок: нормалізують заради дешевого й несуперечливого запису; денормалізують точково, коли зрозуміло, хто і як синхронізує копію.

T-SQL
-- До: назва й адреса магазину повторюються в кожному продажу (порушення 3НФ)
CREATE TABLE dbo.SaleFlat (
    SaleId       bigint        NOT NULL PRIMARY KEY,
    StoreId      int           NOT NULL,
    StoreName    nvarchar(100) NOT NULL,
    StoreAddress nvarchar(200) NOT NULL,
    Amount       decimal(12,2) NOT NULL
);


-- Після: факт про магазин зберігається один раз
CREATE TABLE dbo.Store (
    StoreId int           NOT NULL PRIMARY KEY,
    Name    nvarchar(100) NOT NULL,
    Address nvarchar(200) NOT NULL
);


CREATE TABLE dbo.Sale (
    SaleId  bigint        NOT NULL PRIMARY KEY,
    StoreId int           NOT NULL REFERENCES dbo.Store (StoreId),
    SoldAt  datetime2(0)  NOT NULL,
    Amount  decimal(12,2) NOT NULL
);


-- Переїзд магазину: один рядок замість тисяч
UPDATE dbo.Store SET Address = N'New address' WHERE StoreId = 42;
Джерела
Junior+

Який первинний ключ обрати: identity, GUID чи природний ключ?

Section titled “Який первинний ключ обрати: identity, GUID чи природний ключ?”
Відповідь

Що це

Вид ключа Приклад
Сурогатний числовий int або bigint IDENTITY
Сурогатний GUID uniqueidentifier
Природний код товару, податковий номер, код магазину

Як користуватися

  • За замовчуванням — bigint IDENTITY(1, 1): компактний, зростає по порядку, не змінюється.
  • Природний ключ лишається стовпцем з обмеженням UNIQUE: дублікати заборонені, але зовнішні ключі посилаються на сурогатний.
  • GUID — коли ідентифікатор потрібен до вставлення: його створює застосунок або інша база.

Типова помилка

Зробити первинним ключем штрихкод або податковий номер. Штрихкоди перевикористовують, податковий номер вводять з помилкою, і змінювати доводиться ключ в усіх таблицях, що на нього посилаються.

Як влаштовано

  • Первинний ключ за замовчуванням створює кластерний індекс: рядки таблиці зберігаються впорядкованими за ключем.
  • Ключ кластерного індексу слугує вказівником на рядок у кожному некластерному індексі. GUID на 16 байтів замість bigint на 8 збільшує всі індекси таблиці.
  • Зростаючий ключ дописує рядки в останню сторінку. Випадковий NEWID() вставляє в середину: сторінка ділиться навпіл, індекс фрагментується й заповнений наполовину.

Коли що обирати

Ключ Коли
bigint IDENTITY за замовчуванням, одна база
SEQUENCE номер потрібен до вставлення або спільний для кількох таблиць
Послідовний GUID id створює застосунок, дані зливаються з кількох баз
NEWID() майже ніколи як кластерний ключ

Пастки

  • Пропуски в IDENTITY. Відкат транзакції або перезапуск сервера залишають пропуски. Номер чека без пропусків так не отримати.
  • NEWSEQUENTIALID() працює лише в DEFAULT, а після перезапуску Windows може почати з меншого значення.
  • UUIDv7. Guid.CreateVersion7() упорядкований за часом для .NET, але не для uniqueidentifier у SQL Server.

Правило

bigint IDENTITY за замовчуванням; GUID — коли id створює застосунок, і тоді послідовний у порядку SQL Server.

Під капотом

  • SQL Server порівнює uniqueidentifier не зліва направо: старшими вважаються останні 6 байтів. В UUIDv7 час лежить у перших 6 байтах, тому порядок вставлення в індекс майже випадковий.
  • NEWSEQUENTIALID() і SequentialGuidValueGenerator (генератор EF Core за замовчуванням для SQL Server) переставляють байти під цей порядок.
  • UUIDv7 зберігає порядок у binary(16), записаному в big-endian: ToByteArray(bigEndian: true) (.NET 8+). binary порівнюється побайтово.

Ціна

  • Зростаючий ключ на гарячій таблиці впирається в конкуренцію за останню сторінку (PAGELATCH_EX). OPTIMIZE_FOR_SEQUENTIAL_KEY (SQL Server 2019) її згладжує.
  • GUID — 16 байтів у кожному індексі та зовнішньому ключі: на сотнях мільйонів рядків це гігабайти буферного пулу.
  • Послідовні id легко вгадати: публічний номер замовлення відокремлюють від внутрішнього ключа.

Компроміси

  • Природний ключ економить з’єднання, але його зміна каскадом іде всіма посиланнями. Виняток — короткі незмінні коди на кшталт ISO-коду валюти.
  • Первинний ключ не зобов’язаний бути кластерним: GUID-ключ роблять некластерним, а кластеризують за зростаючим стовпцем.
  • IDENTITY відомий лише після вставлення, тому пакетне вставлення пов’язаних рядків потребує OUTPUT або SEQUENCE.

Як перевірити в продакшені

sys.dm_db_index_physical_stats показує фрагментацію і заповненість сторінок (avg_page_space_used_in_percent), а очікування PAGELATCH_EX на одній сторінці — конкуренцію за кінець індексу.

Підсумок: ключ обирають за розміром, порядком вставлення й місцем генерації; «послідовний» GUID має бути послідовним у порядку порівняння SQL Server.

T-SQL
CREATE TABLE dbo.Product (
    -- Сурогатний ключ: 8 байтів, зростає по порядку, не змінюється
    ProductId bigint IDENTITY(1, 1) NOT NULL
        CONSTRAINT PK_Product PRIMARY KEY CLUSTERED,
    -- Природний ключ: унікальний, але зовнішні ключі на нього не посилаються
    Sku       varchar(20)   NOT NULL CONSTRAINT UQ_Product_Sku UNIQUE,
    Name      nvarchar(200) NOT NULL
);


CREATE TABLE dbo.Receipt (
    -- GUID, послідовний у порядку порівняння SQL Server
    ReceiptId uniqueidentifier NOT NULL
        CONSTRAINT DF_Receipt_Id DEFAULT NEWSEQUENTIALID()
        CONSTRAINT PK_Receipt PRIMARY KEY CLUSTERED,
    StoreId   int          NOT NULL,
    CreatedAt datetime2(0) NOT NULL
);


-- Гаряче вставлення в кінець індексу (SQL Server 2019+)
ALTER INDEX PK_Product ON dbo.Product SET (OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);
C#
using System.Data.SqlTypes;


var ids = new List<Guid>();
for (var i = 0; i < 5; i++)
{
    ids.Add(Guid.CreateVersion7()); // .NET 9+: час у мс + випадкові біти
    Thread.Sleep(2);                // у межах однієї мс порядок не гарантований
}


Console.WriteLine(ids[0].Version);                 // 7
Console.WriteLine(ids.SequenceEqual(ids.Order())); // True: у .NET порядок за часом


// SqlGuid порівнює як SQL Server: старші — останні 6 байтів, а там випадкові біти
var asSqlServer = ids.OrderBy(id => new SqlGuid(id));
Console.WriteLine(ids.SequenceEqual(asSqlServer)); // False (майже завжди)


// binary(16) порівнюється побайтово: big-endian зберігає порядок за часом
var asBinary = ids.OrderBy(id => Convert.ToHexString(id.ToByteArray(bigEndian: true)), StringComparer.Ordinal);
Console.WriteLine(ids.SequenceEqual(asBinary));    // True

SqlGuid з System.Data.SqlTypes повторює правила порівняння SQL Server, тому показує, як UUIDv7 ляже в індекс за uniqueidentifier.

Джерела
Middle+

Як влаштований ETL і як будують завантаження в SSIS?

Section titled “Як влаштований ETL і як будують завантаження в SSIS?”
Відповідь

Що це

ETL — три кроки:

  1. Extract — прочитати дані з джерела: бази, файлу, API.
  2. Transform — очистити й привести до формату цілі: типи, довідники, дублікати.
  3. Load — записати в цільову базу.

Як влаштований пакет SSIS

  • Control flow — задачі та порядок їх виконання: «очистити staging», «завантажити дані», «виконати процедуру».
  • Data flow — задача, через яку йдуть рядки: джерело → перетворення → приймач.

Як користуватися

Пакет розробляють у Visual Studio, розгортають на сервер у каталог SSISDB і запускають за розкладом завданням SQL Server Agent.

Типова помилка

Щоразу перезавантажувати всю таблицю. Поки даних мало, це працює, а через рік завантаження перестає вміщатися в нічне вікно.

Як влаштовано

Інкрементальне завантаження покроково:

  1. Прочитати з керувальної таблиці останній водяний знак.
  2. Data flow забирає рядки, новіші за нього, і швидким вставленням кладе у staging.
  3. Процедура переносить staging у ціль через MERGE або UPDATE і INSERT.
  4. У тій самій транзакції зберегти новий водяний знак.

Коли що обирати

Спосіб Плюс Мінус
Водяний знак просто, без налаштування джерела не бачить видалень
Change tracking дешево, бачить видалення лише ключі, без старих значень
CDC значення до і після з журналу SQL Server Agent, навантаження на журнал

Пастки

  • Порядкова обробка. OLE DB Command і Slowly Changing Dimension виконують запит на кожен рядок. Великі обсяги обробляють у staging одним запитом.
  • Блокувальні перетворення. Sort і Aggregate чекають на весь потік і тримають його в пам’яті. Сортування віддають джерелу (ORDER BY та IsSorted на виході).
  • Дата зміни як водяний знак. Час ставиться до коміту, і рядки довгої транзакції проскакують.

Правило

Запуск безпечний для повтору: staging очищується на початку, перенесення йде за бізнес-ключем, водяний знак рухається лише після успіху.

Під капотом

  • Data flow працює буферами в пам’яті. Синхронні перетворення (Derived Column, Lookup) змінюють рядки в тому самому буфері, асинхронні копіюють їх у нові.
  • OLE DB Destination у режимі fast load вставляє пакетами; Maximum insert commit size задає розмір транзакції.
  • Checkpoints перезапускають пакет з упалої задачі control flow, але не з середини data flow, а цикли Foreach виконуються заново. Ідемпотентні кроки надійніші.

Компроміси

  • Change tracking синхронний: кожен запис платить за облік. Якщо остання синхронізація старша за CHANGE_TRACKING_MIN_VALID_VERSION, потрібне повне перезавантаження.
  • CDC асинхронний, але журнал не усікається, доки завдання захоплення його не обробило.
  • У SSIS 2025 вилучено CDC-компоненти Attunity та Microsoft Connector for Oracle, а 32-бітний режим оголошено застарілим. Для нових завантажень Microsoft пропонує Azure Data Factory і Data Factory у Fabric; готові пакети запускають в ADF на Azure-SSIS Integration Runtime.
  • Завантаження на .NET (SqlBulkCopy у staging, процедура з MERGE) простіше тестувати й версіонувати, але логування, перезапуск і паралелізм пишуться вручну.

Як перевірити в продакшені

  • Каталог SSISDB зберігає історію запусків: catalog.executions і catalog.event_messages, а з рівнем логування Performance — час компонентів у catalog.execution_component_phases.
  • Після завантаження звіряють кількість рядків і суми джерела та цілі.

Підсумок: надійний ETL — це інкремент без втрат, ідемпотентне перенесення і спостережуваність; інструмент другорядний.

T-SQL
SET XACT_ABORT ON;


DECLARE @from binary(8) = (SELECT LastVersion FROM etl.Watermark WHERE TableName = N'Sale');
-- Верхня межа: рядки незавершених транзакцій дочекаються наступного запуску
DECLARE @to binary(8) = MIN_ACTIVE_ROWVERSION();


-- Extract: у SSIS це data flow з джерела у staging
TRUNCATE TABLE stg.Sale;
INSERT INTO stg.Sale (SaleId, StoreId, Amount)
SELECT SaleId, StoreId, Amount
FROM src.Sale
WHERE RowVer >= @from AND RowVer < @to;


-- Load: повторний запуск не створить дублікатів
BEGIN TRAN;


MERGE dw.Sale AS t
USING stg.Sale AS s ON t.SaleId = s.SaleId
WHEN MATCHED THEN UPDATE SET t.StoreId = s.StoreId, t.Amount = s.Amount
WHEN NOT MATCHED THEN INSERT (SaleId, StoreId, Amount) VALUES (s.SaleId, s.StoreId, s.Amount);


-- Водяний знак рухається разом з даними
UPDATE etl.Watermark SET LastVersion = @to WHERE TableName = N'Sale';


COMMIT;

Якщо взяти @@DBTS замість MIN_ACTIVE_ROWVERSION(), рядок із транзакції, яка закомітиться пізніше, отримає версію нижчу за збережену межу й не завантажиться ніколи.

Джерела
Middle+

Як побудувати звітність, щоб звіти не поклали робочу базу?

Section titled “Як побудувати звітність, щоб звіти не поклали робочу базу?”
Відповідь

Що це

OLTP OLAP
Запити короткі, за ключем агрегати за мільйонами рядків
Приклад продаж на касі виторг за магазинами за рік
Схема нормалізована денормалізована, «зірка»

Навіщо розділяти

Важкий звіт займає процесор і диск робочої бази та може блокувати запис. Каси й сайт починають чекати.

Як користуватися

  • Звіти будують у SSRS: звіт описаний у файлі RDL, сервер звітів виконує запит і віддає результат у браузер, Excel або PDF.
  • Важкі звіти підключають не до робочої бази, а до її копії або до сховища даних.

Типова помилка

Лікувати блокування звітів підказкою NOLOCK. Звіт починає читати незакомічені дані й може пропустити або двічі порахувати рядки.

Як влаштовано

Звіт заважає робочій базі трьома шляхами:

  1. Ресурси. Скани й сортування займають CPU, диск і пам’ять під запити.
  2. Кеш. Прочитані сторінки витісняють з буферного пулу гарячі дані.
  3. Блокування. Під READ COMMITTED читання ставить спільні блокування й конфліктує із записом.

Коли що обирати

Рішення Затримка даних Коли
RCSI на робочій базі немає звіти легкі, заважають лише блокування
Кеш і знімки SSRS хвилини або доба один звіт відкривають багато людей
Репліка Always On секунди оперативні звіти, Enterprise
Вітрина даних до наступного ETL аналітика, історія, важкі агрегати

Пастки

  • Репліка — та сама схема. Індекс під звіт створюють на первинній базі, і він уповільнює запис. Власні індекси можливі лише у вітрині або в базі-передплатнику реплікації.
  • Параметри без меж. Період «за весь час» за замовчуванням перетворює звіт на повний скан.
  • Підписки в один час. Сотні розсилок о 9:00 — пік навантаження, який легко розмазати.

Правило

Спершу знайти важкі звіти, потім відводити їх за допустимою затримкою: оперативні — на репліку, аналітику — у вітрину.

Під капотом

  • Репліка для читання застосовує журнал потоком redo. Усі запити на ній ідуть під snapshot isolation, підказки блокувань ігноруються.
  • Платить первинна база: за наявності репліки для читання до змінених рядків додаються 14 байтів під версії, що може спричинити розділення сторінок.
  • Довгий звіт на репліці тримає Sch-S і може затримати redo DDL та очищення ghost-записів на первинній.
  • Columnstore зберігає дані за стовпцями зі стисненням і обробляє пакетами рядків. Із SQL Server 2016 некластерний columnstore на робочій таблиці оновлюваний — це real-time operational analytics.

Компроміси

Варіант Ціна
Репліка Always On Enterprise; схема й індекси як у первинної; лаг росте під навантаженням
Columnstore на робочій таблиці дорожчий кожен запис, місце під індекс
Вітрина із зіркою ETL, затримка, ще одна модель даних

Basic availability group у Standard репліки для читання не дає.

Звітний сервер у 2025+

SSRS 2022 — остання версія, підтримка до 11 січня 2033 року. Із SQL Server 2025 локальна звітність — Power BI Report Server: ті самі RDL-звіти плюс інтерактивні PBIX, право на нього входить у ліцензії Enterprise і Standard. Хмарний шлях — Power BI, де RDL працюють як paginated reports.

Як перевірити в продакшені

  • ExecutionLog3 у базі сервера звітів показує для кожного запуску час отримання даних, обробки й рендерингу.
  • Відставання репліки — sys.dm_hadr_database_replica_states: log_send_queue_size, redo_queue_size.

Підсумок: звітність розводять із транзакціями за допустимою затримкою, а платформу обирають з урахуванням кінця лінійки SSRS.

T-SQL
-- База сервера звітів: за замовчуванням ReportServer, журнал зберігається 60 днів
USE ReportServer;


-- Які звіти найдовше чекали на базу за тиждень
SELECT TOP (10)
    ItemPath,
    COUNT(*)                                       AS Runs,
    SUM(CAST(TimeDataRetrieval AS bigint)) / 1000  AS DataSecTotal,
    AVG(CAST(TimeDataRetrieval AS bigint)) / 1000  AS DataSecAvg,
    MAX([RowCount])                                AS MaxRows
FROM dbo.ExecutionLog3
WHERE TimeStart >= DATEADD(DAY, -7, SYSDATETIME())
GROUP BY ItemPath
ORDER BY DataSecTotal DESC;
T-SQL
-- Оперативна аналітика просто по робочій таблиці (SQL Server 2016+):
-- транзакції пишуть у звичайний індекс, агрегати читають стиснені стовпці
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Sale
    ON dbo.Sale (StoreId, SoldAt, Amount);


SELECT StoreId, SUM(Amount) AS Revenue
FROM dbo.Sale
WHERE SoldAt >= '2026-10-01'
GROUP BY StoreId;
Джерела
Senior+

Як масштабувати базу: партиціонування, репліки, шардування й архів?

Section titled “Як масштабувати базу: партиціонування, репліки, шардування й архів?”
Відповідь

Що це

  • Партиціонування — таблиця зберігається секціями за значенням стовпця, наприклад за місяцем продажу. Для запитів це одна таблиця.
  • Репліка для читання — копія бази, на яку відводять звіти.
  • Шардування — дані різних магазинів або клієнтів лежать у різних базах на різних серверах.
  • Архівування — старі дані переносять з робочої таблиці в архів.

Навіщо

Велику таблицю довше обслуговувати, копіювати й чистити. Видалити минулий рік через DELETE — години блокувань і гігабайти журналу, а з партиціонуванням це перемикання секції.

Типова помилка

Чекати, що партиціонування саме пришвидшить запити. Якщо в умові немає стовпця секціонування, запит читає всі секції й може стати повільнішим.

Як влаштоване партиціонування

  1. Функція секціонування задає межі: RANGE RIGHT FOR VALUES ('2026-09-01', '2026-10-01').
  2. Схема секціонування розкладає секції по файлових групах.
  3. Таблицю й індекси створюють на схемі. Індекс на тій самій схемі — вирівняний; SWITCH працює лише з вирівняними індексами.

Ковзне вікно для архіву

  1. SPLIT RANGE додає порожню секцію під наступний місяць.
  2. SWITCH PARTITION переносить найстаріший місяць в архівну таблицю тієї самої структури.
  3. MERGE RANGE прибирає межу, що спорожніла.

Коли що обирати

Проблема Рішення
Таблицю довго обслуговувати й чистити партиціонування
Читання перевантажує сервер репліки для читання
Запис не вміщається в один сервер шардування

Пастки

  • Унікальний індекс містить стовпець секціонування, інакше він не вирівняний. Звідси складений ключ (SaleId, SoldOn).
  • Запит без ключа секціонування шукає в кожній секції. Microsoft прямо пише, що в OLTP партиціонування рідко пришвидшує запити.
  • SPLIT і MERGE непорожньої секції переміщують рядки й пишуть журнал, тому крайні секції тримають порожніми.

Під капотом

  • SWITCH змінює належність сторінок, дані не копіюються. Умови: однакові стовпці й індекси, та сама файлова група, ціль порожня; під час завантаження в секцію джерело підтверджує діапазон CHECK-обмеженням.
  • SWITCH потрібне монопольне блокування обох таблиць. Із SQL Server 2014 WAIT_AT_LOW_PRIORITY не дає йому стати в чергу перед усіма запитами.
  • TRUNCATE TABLE ... WITH (PARTITIONS (2)) (SQL Server 2016+) очищує секцію без архіву. Партиціонування є в усіх редакціях із SQL Server 2016 SP1.

Ціна

  • До 15 000 секцій, але тисячі секцій коштують пам’яті, а запити без виключення секцій сповільнюються.
  • Репліки для читання — лише Enterprise; basic availability group у Standard не дає читати з вторинної.

Компроміси шардування

Що втрачаємо Чим платимо
З’єднання й агрегати між шардами запити до всіх шардів, звіти через сховище
Транзакції між шардами саги, outbox, ідемпотентність
Глобальна унікальність GUID або ключ із номером шарда
Рівномірність перебалансування, «гарячі» шарди

Довідники на кшталт товарів і магазинів копіюють у кожен шард.

Як перевірити в продакшені

  • sys.dm_db_partition_stats показує рядки за секціями, а план — Actual Partition Count.
  • Відставання реплік — sys.dm_hadr_database_replica_states.

Підсумок: один сервер вичавлюють до кінця, партиціонують заради обслуговування, а шардують, лише коли запис уперся в сервер і є природний ключ розділу.

T-SQL
-- RANGE RIGHT: межа — перше число місяця; крайні секції тримаємо порожніми
CREATE PARTITION FUNCTION pfMonth (date)
    AS RANGE RIGHT FOR VALUES ('2026-08-01', '2026-09-01', '2026-10-01', '2026-11-01');
CREATE PARTITION SCHEME psMonth AS PARTITION pfMonth ALL TO ([PRIMARY]);


CREATE TABLE dbo.Sale (
    SaleId bigint        NOT NULL,
    SoldOn date          NOT NULL,
    Amount decimal(12,2) NOT NULL,
    CONSTRAINT PK_Sale PRIMARY KEY (SaleId, SoldOn) -- ключ містить стовпець секціонування
) ON psMonth (SoldOn);


-- Архів: та сама структура й файлова група, порожній
CREATE TABLE dbo.SaleArchive (
    SaleId bigint NOT NULL, SoldOn date NOT NULL, Amount decimal(12,2) NOT NULL,
    CONSTRAINT PK_SaleArchive PRIMARY KEY (SaleId, SoldOn)
) ON [PRIMARY];


-- 1. Порожня секція під грудень, поки даних за листопад ще немає
ALTER PARTITION SCHEME psMonth NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pfMonth() SPLIT RANGE ('2026-12-01');


-- 2. Серпень (секція 2) іде в архів: змінюються лише метадані
ALTER TABLE dbo.Sale SWITCH PARTITION 2 TO dbo.SaleArchive
    WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));


-- 3. Прибрати межу між двома порожніми секціями
ALTER PARTITION FUNCTION pfMonth() MERGE RANGE ('2026-08-01');
Джерела
Senior+

Як змінювати схему бази без простою?

Section titled “Як змінювати схему бази без простою?”
Відповідь

Що це

Зміна схеми без простою — база змінюється, поки застосунок працює й магазини продовжують продавати.

Як користуватися

Перейменування стовпця Phone на PhoneNumber покроково:

  1. Додати стовпець PhoneNumber, дозволивши NULL. Старий застосунок його не помічає.
  2. Випустити версію застосунку, яка пише в обидва стовпці.
  3. Скопіювати старі дані невеликими пакетами.
  4. Випустити версію, яка читає лише PhoneNumber.
  5. Видалити Phone, коли старих версій застосунку не лишилося.

Типова помилка

Перейменувати або видалити стовпець в одному релізі з кодом. Поки оновлено не всі сервери, стара версія звертається до стовпця, якого вже немає, і падає.

Як влаштовано

Expand/contract — три фази:

  1. Expand. Лише сумісні зміни: новий nullable-стовпець, таблиця, індекс. Старий код працює.
  2. Migrate. Код пише в старе й нове, backfill переносить історію, читання перемикається на нове.
  3. Contract. Коли старого коду немає і вікно відкату минуло, старе видаляють.

Скільки тримається блокування

Операція Як виконується
Новий nullable-стовпець метадані, миттєво
NOT NULL з константним default метадані в Enterprise (2012+), інакше перезапис рядків
Зміна типу, наприклад int → bigint перезапис усіх рядків під Sch-M
Індекс з ONLINE = ON онлайн, короткі блокування на початку й наприкінці; Enterprise

Пастки

  • Черга за Sch-M. ALTER чекає на довгий звіт, а всі нові запити до таблиці чекають на ALTER. Перед DDL ставлять SET LOCK_TIMEOUT і повторюють спробу.
  • Один великий UPDATE. Backfill однією транзакцією роздуває журнал, спричиняє ескалацію блокувань до таблиці й затримує репліки. Потрібні пакети по кілька тисяч рядків.
  • Перейменування в один крок. sp_rename стовпця, який читає робоча версія, — несумісна зміна.

Правило

Кожна міграція сумісна з поточною і попередньою версією застосунку.

Під капотом

  • Sch-M несумісне з жодним блокуванням, навіть зі стабільністю схеми в читачів. Важливо, скільки DDL його тримає і скільки чекає на його отримання.
  • Ескалація спрацьовує приблизно на 5 000 блокувань одного оператора на одному об’єкті. Пакети, менші за цей поріг, тримають порядкові блокування й не зупиняють інших.
  • ADR (SQL Server 2019+) робить відкат довгої транзакції миттєвим і усікає журнал під час неї; без ADR скасування годинного UPDATE може тривати стільки ж.

Зміна типу на великій таблиці

  1. Додати стовпець нового типу, nullable.
  2. Синхронізувати нові зміни — кодом або тригером.
  3. Backfill пакетами за діапазонами ключа.
  4. Перемкнути читання й запис, потім видалити старий стовпець.

ALTER COLUMN ... WITH (ONLINE = ON) (SQL Server 2016+, Enterprise) лишає таблицю доступною, але рядки однаково переписуються.

Компроміси

  • State-based розгортання (dacpac, sqlpackage) будує скрипт порівнянням схем і за деяких змін перестворює таблицю копіюванням, тому скрипт читають до продакшену. Міграції (EF Core, DbUp) явні, але порядок кроків на вас.
  • Після expand відкочують лише код: схема сумісна зі старою версією. Зворотні міграції з утратою даних у продакшені замінюють виправленням уперед.
  • Тригер синхронізації ловить усіх, хто пише, включно з ETL, але здорожує кожен запис.

Як перевірити в продакшені

  • Під час backfill стежать за log_reuse_wait_desc у sys.databases і redo_queue_size реплік; пауза між пакетами — регулятор.
  • Черга за DDL видна в sys.dm_exec_requests: wait_type LCK_M_SCH_M і blocking_session_id.

Підсумок: без простою — це сумісність із двома версіями коду, короткі блокування й керована черга за Sch-M.

T-SQL
-- DDL чекає на блокування не довше 5 секунд і не тримає чергу за собою
SET LOCK_TIMEOUT 5000;


-- Expand: новий nullable-стовпець ширший за старий — лише метадані
ALTER TABLE dbo.Receipt ADD TotalAmount decimal(14,2) NULL;


-- Backfill пакетами за діапазоном ключа: короткі транзакції без ескалації
DECLARE @batch bigint = 4000, @from bigint = 0, @max bigint;
SELECT @max = MAX(ReceiptId) FROM dbo.Receipt;


WHILE @from < @max
BEGIN
    UPDATE dbo.Receipt
    SET TotalAmount = Total
    WHERE ReceiptId > @from AND ReceiptId <= @from + @batch
      AND TotalAmount IS NULL; -- повторний запуск продовжить з місця зупинки


    SET @from += @batch;
    WAITFOR DELAY '00:00:00.100'; -- місце для робочого навантаження й реплік
END;


-- Contract — окремим релізом, коли код більше не читає старий стовпець
-- ALTER TABLE dbo.Receipt DROP COLUMN Total;

Поки триває backfill, нова версія застосунку пише в обидва стовпці, тому нові чеки вже заповнені.

Джерела