Различия

Показаны различия между двумя версиями страницы.

--- optimizacija_nastroek_ms_sql [2026/04/22 10:52]
tro [Рекомендации]
+++ optimizacija_nastroek_ms_sql [2026/04/25 12:06] (текущий)
tro
@@ Строка 1: / Строка 1: @@
-====== Настройки самого SQL ======
+====== Налаштування самого SQL ======
-===== Рекомендации =====
+===== Рекомендації =====
-  - Пам'ять — правило 80%: SQL + 1С процеси + ОС не повинні перевищувати 80% RAM. При перевищенні 1С іде в файл підкачки. Для 185 GB: SQL отримує 95 GB (51%), решта 29% — для 1С сервера і ОС.
+  - **Пам'ять — правило 80%**: SQL + 1С процеси + ОС не повинні перевищувати 80% RAM. При перевищенні 1С іде в файл підкачки. Для 185 GB: SQL отримує 95 GB (51%), решта 29% — для 1С сервера і ОС.
-  - MAXDOP = 1 — жорстке правило для будь-якого сервера 1С. Платформа генерує сотні коротких запитів, паралелізм тільки заважає — створює overhead планувальника без виграшу в швидкості.
+  - **MAXDOP = 1** — жорстке правило для будь-якого сервера 1С. Платформа генерує сотні коротких запитів, паралелізм тільки заважає — створює overhead планувальника без виграшу в швидкості.
-  - Cost threshold = 25 — дефолтне значення Microsoft (5) встановлено в 1990-х під залізо того часу. На сучасному сервері запит вартістю 5 виконується за мікросекунди — будувати для нього паралельний план безглуздо. Значення 25 відсікає 95% коротких запитів 1С від непотрібного аналізу паралелізму.
+  - **Cost threshold = 25** — дефолтне значення Microsoft (5) встановлено в 1990-х під залізо того часу. На сучасному сервері запит вартістю 5 виконується за мікросекунди — будувати для нього паралельний план безглуздо. Значення 25 відсікає 95% коротких запитів 1С від непотрібного аналізу паралелізму.
-  - Optimize for ad hoc = 1 — без цього кожен унікальний запит 1С зберігає повний план у plan cache. При 7 базах і сотнях користувачів plan cache засмічується гігабайтами одноразових планів.
+  - **Optimize for ad hoc = 1** — без цього кожен унікальний запит 1С зберігає повний план у plan cache. При 7 базах і сотнях користувачів plan cache засмічується гігабайтами одноразових планів.
-  - Network packet size = 8192 — актуально коли SQL і 1С сервер на одній машині або в одній мережі. Зменшує кількість мережевих пакетів при передачі великих наборів даних — звіти, великі списки документів.
+  - **Network packet size = 8192** — актуально коли SQL і 1С сервер на одній машині або в одній мережі. Зменшує кількість мережевих пакетів при передачі великих наборів даних — звіти, великі списки документів.
-  - Blocked process threshold = 5 — не впливає на продуктивність, тільки вмикає моніторинг. Без цього параметра неможливо діагностувати блокування в 1С через стандартні інструменти SQL.
+  - **Blocked process threshold = 5** — не впливає на продуктивність, тільки вмикає моніторинг. Без цього параметра неможливо діагностувати блокування в 1С через стандартні інструменти SQL.
+  - **TempDB — розташування і кількість файлів** - TempDB обов'язково розміщувати на найшвидшому диску — окремо від системного диску C: і окремо від файлів баз 1С. На VMware це означає окремий NVMe датастор. Кількість файлів даних = кількість vCPU але не більше 8. При 27 vCPU — 8 файлів. Всі файли даних однакового розміру — SQL розподіляє навантаження між ними рівномірно тільки якщо розміри рівні.
+  - **Автозріст файлів баз 1С (FILEGROWTH)** — аварійний механізм на випадок якщо файл несподівано заповнився. Під час автозросту SQL зупиняє всі операції запису в базу — користувачі 1С відчувають зависання. Чим більший крок зросту — тим рідше це відбувається.Рекомендовані значення: 8 файлів даних × 4096 MB = 32 GB, 1 файл логу × 2048 MB, Автозріст: 512 MB для даних, 256 MB для логу Файл даних (.mdf, .ndf)
+  - Для планів обслуговування баз SQL краще користуватись [[https://ola.hallengren.com/|набір T-SQL скриптів DBA Ola Hallengren]]
-===== Скрипты sql =====
+===== Скрипти sql =====
 <code>
 -- Перевіримо поточний стан
@@ Строка 27: / Строка 30: @@
 ORDER BY name;
 </code>
-====== рекомендуемые настройки для каждой базы в SQL ======
+<code>
+-- ============================================================
+-- 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;
+</code>
+====== рекомендовані налаштування для кожної бази в SQL ======
   * READ_COMMITTED_SNAPSHOT - Усуває блокування між читанням і записом
   * ALLOW_SNAPSHOT_ISOLATION - Дозволяє 1С використовувати версіонування в транзакціях
@@ Строка 33: / Строка 87: @@
   * AUTO_CREATE_STATISTICS - Оптимізатор не "вгадує" плани для нових запитів
   * PAGE_VERIFY CHECKSUM - Захист від тихого пошкодження даних
-  * AUTO_UPDATE_STATISTICS_ASYNC - Вмикаэ асинхонне оновлення статистики без пауз для користувачів
+  * AUTO_UPDATE_STATISTICS_ASYNC - Вмикає асинхонне оновлення статистики без пауз для користувачів
+<WRAP center round important 60%>
+Більшість цих параметрів зберігаються в самій базі тому при відновленні з бекапу з іншими налаштуваннями потрібно по новому їх встановлювати
+</WRAP>
 <code>
@@ Строка 141: / Строка 199: @@
 ORDER BY name;
 </code>
-====== Полезные ссылки ======
+<code>
-  * https://its.1c.ru/db/metod8dev/content/5904/hdoc
+-- ============================================================
+-- ВІДКАТ параметрів баз 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;
+</code>
+<WRAP center round info 60%>
+AUTO_UPDATE_STATISTICS, AUTO_CREATE_STATISTICS і PAGE_VERIFY CHECKSUM не відкочуємо — вони або вже були увімкнені до нас (AUTO_*), або є корисними незалежно від проблеми (CHECKSUM). Відкочуємо тільки три параметри які реально змінили поведінку бази.
+</WRAP>
+====== Вимкнення FILESTREAM ======
+Реальний ефект буде мінімальний для кінцевих користувачів. Головне що він не буде створювати зайвий фоновий процес і не буде займати ресурси без жодної користі для 1С.
+<code>
+-- Вимкнути FILESTREAM — не потрібен для 1С
+EXEC sp_configure 'filestream access level', 0;
+RECONFIGURE;
+</code>
+<code>
+-- Перевірка
+EXEC sp_configure 'filestream access level';
+-- run_value має стати 0
+</code>
+====== Налаштування 1C ======
+===== Підключення кластера 1С до баз SQL =====
+замість localhost (протокола TCP/IP) краще використовувати sharedmemory. Перевірка поточного протоколу підключення
+<code>
+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;
+</code>
+Якщо "net transport" не написав "sharedmemory" то в консолі 1С налаштування підключення кластера то SQL краще писати підключення "**lpc:VTSQLMAIN**"
+{{ :1cbasesharedmemory.png?600 |}}
+====== Корисні посилання ======
+  * [[https://its.1c.ru/db/metod8dev/content/5904/hdoc|Офіційні рекомендації ITS]]
+  * [[https://ola.hallengren.com/|Набір T-SQL скриптів обслуговування SQL DBA Ola Hallengren]]