====== Налаштування самого SQL ====== ===== Рекомендації ===== - **Пам'ять — правило 80%**: SQL + 1С процеси + ОС не повинні перевищувати 80% RAM. При перевищенні 1С іде в файл підкачки. Для 185 GB: SQL отримує 95 GB (51%), решта 29% — для 1С сервера і ОС. - **MAXDOP = 1** — жорстке правило для будь-якого сервера 1С. Платформа генерує сотні коротких запитів, паралелізм тільки заважає — створює overhead планувальника без виграшу в швидкості. - **Cost threshold = 25** — дефолтне значення Microsoft (5) встановлено в 1990-х під залізо того часу. На сучасному сервері запит вартістю 5 виконується за мікросекунди — будувати для нього паралельний план безглуздо. Значення 25 відсікає 95% коротких запитів 1С від непотрібного аналізу паралелізму. - **Optimize for ad hoc = 1** — без цього кожен унікальний запит 1С зберігає повний план у plan cache. При 7 базах і сотнях користувачів plan cache засмічується гігабайтами одноразових планів. - **Network packet size = 8192** — актуально коли SQL і 1С сервер на одній машині або в одній мережі. Зменшує кількість мережевих пакетів при передачі великих наборів даних — звіти, великі списки документів. - **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 ===== -- Перевіримо поточний стан 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**" {{ :1cbasesharedmemory.png?600 |}} ====== Корисні посилання ====== * [[https://its.1c.ru/db/metod8dev/content/5904/hdoc|Офіційні рекомендації ITS]] * [[https://ola.hallengren.com/|Набір T-SQL скриптів обслуговування SQL DBA Ola Hallengren]]