optimizacija_nastroek_ms_sql

Налаштування самого SQL

Рекомендації

  1. Пам'ять — правило 80%: SQL + 1С процеси + ОС не повинні перевищувати 80% RAM. При перевищенні 1С іде в файл підкачки. Для 185 GB: SQL отримує 95 GB (51%), решта 29% — для 1С сервера і ОС.
  2. MAXDOP = 1 — жорстке правило для будь-якого сервера 1С. Платформа генерує сотні коротких запитів, паралелізм тільки заважає — створює overhead планувальника без виграшу в швидкості.
  3. Cost threshold = 25 — дефолтне значення Microsoft (5) встановлено в 1990-х під залізо того часу. На сучасному сервері запит вартістю 5 виконується за мікросекунди — будувати для нього паралельний план безглуздо. Значення 25 відсікає 95% коротких запитів 1С від непотрібного аналізу паралелізму.
  4. Optimize for ad hoc = 1 — без цього кожен унікальний запит 1С зберігає повний план у plan cache. При 7 базах і сотнях користувачів plan cache засмічується гігабайтами одноразових планів.
  5. Network packet size = 8192 — актуально коли SQL і 1С сервер на одній машині або в одній мережі. Зменшує кількість мережевих пакетів при передачі великих наборів даних — звіти, великі списки документів.
  6. Blocked process threshold = 5 — не впливає на продуктивність, тільки вмикає моніторинг. Без цього параметра неможливо діагностувати блокування в 1С через стандартні інструменти SQL.
  7. TempDB — розташування і кількість файлів - TempDB обов'язково розміщувати на найшвидшому диску — окремо від системного диску C: і окремо від файлів баз 1С. На VMware це означає окремий NVMe датастор. Кількість файлів даних = кількість vCPU але не більше 8. При 27 vCPU — 8 файлів. Всі файли даних однакового розміру — SQL розподіляє навантаження між ними рівномірно тільки якщо розміри рівні.
  8. Автозріст файлів баз 1С (FILEGROWTH) — аварійний механізм на випадок якщо файл несподівано заповнився. Під час автозросту SQL зупиняє всі операції запису в базу — користувачі 1С відчувають зависання. Чим більший крок зросту — тим рідше це відбувається.Рекомендовані значення: 8 файлів даних × 4096 MB = 32 GB, 1 файл логу × 2048 MB, Автозріст: 512 MB для даних, 256 MB для логу Файл даних (.mdf, .ndf)
  9. Для планів обслуговування баз SQL краще користуватись набір T-SQL скриптів DBA Ola Hallengren

Скрипти sql

-- Перевіримо поточний стан
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;

SELECT name, value, value_in_use, description
FROM sys.configurations
WHERE name IN (
    'max server memory (MB)',
    'min server memory (MB)',
    'max degree of parallelism',
    'cost threshold for parallelism',
    'optimize for ad hoc workloads',
    'network packet size (B)',
    'blocked process threshold (s)'
)
ORDER BY name;
-- ============================================================
-- VTSQLMAIN — еталонні параметри SQL Server
-- RAM: 185 GB | vCPU: 27 | Windows Server 2025 Datacenter
-- 1C 8.3.27 | 7 баз
-- ============================================================

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- ПАМ'ЯТЬ
-- 95 GB для SQL (51% від 185 GB)
-- Залишок: 40 GB для 1С + 8 GB ОС + 5 GB резерв = 80% загалом
EXEC sp_configure 'max server memory (MB)', 97280;
EXEC sp_configure 'min server memory (MB)', 4096;
RECONFIGURE WITH OVERRIDE;

-- ПАРАЛЕЛІЗМ
-- MAXDOP = 1 — для 1С завжди, платформа не підтримує паралельні плани
-- cost threshold = 25 — не будувати паралельний план для легких запитів
EXEC sp_configure 'max degree of parallelism', 1;
EXEC sp_configure 'cost threshold for parallelism', 25;
RECONFIGURE;

-- PLAN CACHE
-- Економія пам'яті — зберігає stub замість повного плану
-- при першому виклику унікального запиту 1С
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

-- МЕРЕЖА
-- 8192 замість дефолтних 4096 — менше round-trip
-- при передачі великих результатів від SQL до 1С сервера
EXEC sp_configure 'network packet size (B)', 8192;
RECONFIGURE;

-- МОНІТОРИНГ БЛОКУВАНЬ
-- 5 секунд — фіксує запити що висять довше 5 сек
-- видно через Extended Events або SQL Profiler
EXEC sp_configure 'blocked process threshold (s)', 5;
RECONFIGURE;

-- БЕЗПЕКА
-- Ніколи не вмикати для 1C, залишити у 0
EXEC sp_configure 'priority boost', 0;
EXEC sp_configure 'lightweight pooling', 0;
RECONFIGURE;

EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

рекомендовані налаштування для кожної бази в SQL

  • READ_COMMITTED_SNAPSHOT - Усуває блокування між читанням і записом
  • ALLOW_SNAPSHOT_ISOLATION - Дозволяє 1С використовувати версіонування в транзакціях
  • AUTO_UPDATE_STATISTICS - Оптимізатор завжди має актуальну статистику
  • AUTO_CREATE_STATISTICS - Оптимізатор не «вгадує» плани для нових запитів
  • PAGE_VERIFY CHECKSUM - Захист від тихого пошкодження даних
  • AUTO_UPDATE_STATISTICS_ASYNC - Вмикає асинхонне оновлення статистики без пауз для користувачів

Більшість цих параметрів зберігаються в самій базі тому при відновленні з бекапу з іншими налаштуваннями потрібно по новому їх встановлювати 

-- ============================================================
-- Поточний стан параметрів для всіх баз 1С
-- ============================================================
SELECT 
    name                                AS база,
    is_auto_update_stats_on             AS auto_update_stats,
    is_auto_update_stats_async_on       AS auto_update_async,
    is_auto_create_stats_on             AS auto_create_stats,
    snapshot_isolation_state_desc       AS snapshot_isolation,
    is_read_committed_snapshot_on       AS rcsi,
    page_verify_option_desc             AS page_verify
FROM sys.databases
WHERE database_id > 4
  AND state_desc = 'ONLINE'
ORDER BY name;
-- ============================================================
-- Налаштування параметрів для всіх баз 1С
-- Працює на будь-якому сервері (VTAPPVIA або VTSQLMAIN)
-- ============================================================

USE master;

DECLARE @dbname NVARCHAR(128);
DECLARE @sql    NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE database_id > 4
      AND state_desc = 'ONLINE'
      AND name NOT IN ('distribution')
    ORDER BY name;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '>>> Обробляємо базу: ' + @dbname;

    -- AUTO_UPDATE_STATISTICS
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET AUTO_UPDATE_STATISTICS ON;';
    EXEC sp_executesql @sql;
    PRINT '    AUTO_UPDATE_STATISTICS        ON';

    -- AUTO_UPDATE_STATISTICS_ASYNC
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET AUTO_UPDATE_STATISTICS_ASYNC ON;';
    EXEC sp_executesql @sql;
    PRINT '    AUTO_UPDATE_STATISTICS_ASYNC  ON';

    -- AUTO_CREATE_STATISTICS
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET AUTO_CREATE_STATISTICS ON;';
    EXEC sp_executesql @sql;
    PRINT '    AUTO_CREATE_STATISTICS        ON';

    -- PAGE_VERIFY CHECKSUM
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET PAGE_VERIFY CHECKSUM;';
    EXEC sp_executesql @sql;
    PRINT '    PAGE_VERIFY                   CHECKSUM';

    -- ALLOW_SNAPSHOT_ISOLATION
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET ALLOW_SNAPSHOT_ISOLATION ON;';
    EXEC sp_executesql @sql;
    PRINT '    ALLOW_SNAPSHOT_ISOLATION      ON';

    -- READ_COMMITTED_SNAPSHOT (потребує SINGLE_USER)
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
    EXEC sp_executesql @sql;

    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_COMMITTED_SNAPSHOT ON;';
    EXEC sp_executesql @sql;

    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET MULTI_USER;';
    EXEC sp_executesql @sql;
    PRINT '    READ_COMMITTED_SNAPSHOT       ON';

    PRINT '    OK';
    PRINT '';

    FETCH NEXT FROM db_cursor INTO @dbname;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

PRINT '============================================';
PRINT 'Готово. Перевірка поточного стану:';
PRINT '============================================';

-- Фінальна перевірка
SELECT
    name                            AS база,
    is_auto_update_stats_on         AS auto_update,
    is_auto_update_stats_async_on   AS auto_async,
    is_auto_create_stats_on         AS auto_create,
    page_verify_option_desc         AS page_verify,
    snapshot_isolation_state_desc   AS snapshot,
    is_read_committed_snapshot_on   AS rcsi
FROM sys.databases
WHERE database_id > 4
  AND state_desc = 'ONLINE'
ORDER BY name;
-- ============================================================
-- ВІДКАТ параметрів баз 1С до стандартних значень SQL Server
-- Запускати при проблемах після увімкнення RCSI / Snapshot
-- ============================================================

USE master;

DECLARE @dbname NVARCHAR(128);
DECLARE @sql    NVARCHAR(MAX);

DECLARE db_cursor CURSOR FOR
    SELECT name
    FROM sys.databases
    WHERE database_id > 4
      AND state_desc = 'ONLINE'
      AND name NOT IN ('distribution')
    ORDER BY name;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @dbname;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT '>>> Відкат бази: ' + @dbname;

    -- AUTO_UPDATE_STATISTICS_ASYNC → OFF (дефолт)
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET AUTO_UPDATE_STATISTICS_ASYNC OFF;';
    EXEC sp_executesql @sql;
    PRINT '    AUTO_UPDATE_STATISTICS_ASYNC  OFF';

    -- ALLOW_SNAPSHOT_ISOLATION → OFF (дефолт)
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET ALLOW_SNAPSHOT_ISOLATION OFF;';
    EXEC sp_executesql @sql;
    PRINT '    ALLOW_SNAPSHOT_ISOLATION      OFF';

    -- READ_COMMITTED_SNAPSHOT → OFF (дефолт, потребує SINGLE_USER)
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;';
    EXEC sp_executesql @sql;

    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET READ_COMMITTED_SNAPSHOT OFF;';
    EXEC sp_executesql @sql;

    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET MULTI_USER;';
    EXEC sp_executesql @sql;
    PRINT '    READ_COMMITTED_SNAPSHOT       OFF';

    -- AUTO_UPDATE_STATISTICS → ON (дефолт, не змінюємо — вже був ON)
    -- AUTO_CREATE_STATISTICS → ON (дефолт, не змінюємо — вже був ON)
    -- PAGE_VERIFY → залишаємо CHECKSUM (краще ніж дефолт NONE)

    PRINT '    OK';
    PRINT '';

    FETCH NEXT FROM db_cursor INTO @dbname;
END;

CLOSE db_cursor;
DEALLOCATE db_cursor;

PRINT '============================================';
PRINT 'Відкат завершено. Перевірка поточного стану:';
PRINT '============================================';

-- Фінальна перевірка
SELECT
    name                            AS база,
    is_auto_update_stats_on         AS auto_update,
    is_auto_update_stats_async_on   AS auto_async,
    is_auto_create_stats_on         AS auto_create,
    page_verify_option_desc         AS page_verify,
    snapshot_isolation_state_desc   AS snapshot,
    is_read_committed_snapshot_on   AS rcsi
FROM sys.databases
WHERE database_id > 4
  AND state_desc = 'ONLINE'
ORDER BY name;

AUTO_UPDATE_STATISTICS, AUTO_CREATE_STATISTICS і PAGE_VERIFY CHECKSUM не відкочуємо — вони або вже були увімкнені до нас (AUTO_*), або є корисними незалежно від проблеми (CHECKSUM). Відкочуємо тільки три параметри які реально змінили поведінку бази.

Вимкнення FILESTREAM

Реальний ефект буде мінімальний для кінцевих користувачів. Головне що він не буде створювати зайвий фоновий процес і не буде займати ресурси без жодної користі для 1С. 

-- Вимкнути FILESTREAM — не потрібен для 1С
EXEC sp_configure 'filestream access level', 0;
RECONFIGURE;
-- Перевірка
EXEC sp_configure 'filestream access level';
-- run_value має стати 0

Налаштування 1C

Підключення кластера 1С до баз SQL

замість localhost (протокола TCP/IP) краще використовувати sharedmemory. Перевірка поточного протоколу підключення 

SELECT
    s.program_name,
    c.net_transport,
    c.client_net_address,
    COUNT(*) AS кількість
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id
WHERE s.program_name LIKE '%1C%'
   OR s.program_name LIKE '%1cv8%'
   OR s.program_name LIKE '%Enterprise%'
GROUP BY s.program_name, c.net_transport, c.client_net_address
ORDER BY кількість DESC;

Якщо «net transport» не написав «sharedmemory» то в консолі 1С налаштування підключення кластера то SQL краще писати підключення «lpc:VTSQLMAIN»

Корисні посилання

  • /sites/data/pages/optimizacija_nastroek_ms_sql.txt
  • Последнее изменение: 2026/04/25 12:06
  • — tro