SQL

Основные компоненты

tempDB

Суть:

• Системная БД SQL Server для временных объектов (таблиц, данных запросов).
• Пересоздаётся при каждом перезапуске SQL Server.
• Автоматически растёт порциями по 10%, если не заданы ручные настройки.
• Используется 1С для временных таблиц и SQL Server для операций GROUP BY, UNION и т.д.

Проблема:
Разрастание TEMPDB, ведущее к нехватке места на диске.

Причины:

1. Долгие транзакции, блокирующие очистку.
2. Обрывы сетевых подключений (например, зависшие сессии 1С), из-за которых SQL Server не завершает транзакции.
3. Недостаток места → SQL Server увеличивает TEMPDB, но не может освободить его из-за активных транзакций.

Решения для уменьшения размера:

1. Перезапуск SQL Server – TEMPDB сбросится к исходному размеру.
2. Команда DBCC SHRINKDATABASE(TEMPDB) – сжатие всей БД.
3. Команда DBCC SHRINKFILE – сжатие отдельных файлов данных и журнала.

Рекомендации:

• Выполнять сжатие в период минимальной активности.
• Требуются права администратора.
• Подробности – в документации Microsoft SQL Server.

Регламентные операции

1. Обновление статистики

• Зачем: SQL Server строит план выполнения запроса на основе статистики о распределении данных в таблицах и индексах.
• Проблема: Устаревшая статистика приводит к выбору неоптимальных планов (например, сканирование вместо поиска по индексу), что резко снижает скорость запросов.
• Как помогает: Регулярное обновление с FULLSCAN даёт оптимизатору точную картину данных, позволяя строить наиболее быстрые планы. Без блокировок, можно выполнять часто (рекомендуется не реже раза в день).

Что хранит статистика:

1. Количество строк в таблице — сколько всего записей
2. Количество страниц данных — сколько места занимает таблица
3. Плотность данных — процент уникальных значений в столбце
4. Гистограмма (до 200 корзин) — распределение значений:
   • Диапазон значений в каждой корзине
   • Количество строк в каждой корзине
   • Частота встречаемости каждого значения
5. Средняя длина данных — средний размер значений в байтах
6. Максимальное/минимальное значение — границы данных
7. Информация о связях — для составных индексов (корреляция между полями)

Как SQL использует статистику:

1. Для оценки объёма данных:

WHERE РегистрСведений.Цены.Номенклатура = 'Товар123'

→ Смотрит: всего уникальных номенклатур 10 000, всего строк 1 000 000 → Оценка: 1 000 000 / 10 000 = ~100 строк

2. Для выбора метода доступа:

Объём данных	Оператор	Когда используется
< 1-5% строк	Index Seek	Точечный поиск по индексу
1-30% строк	Index Scan	Перебор индекса (зависит от факторов)
> 30% строк	Table Scan	Полный перебор таблицы

3. Для выбора типа соединения:

Ситуация	Тип JOIN	Критерии выбора
Маленькая × Большая	Nested Loops	Внешняя таблица < 1000 строк
Большая × Большая (нет сортировки)	Hash Join	Нет индексов для сортировки
Большая × Большая (есть сортировка)	Merge Join	Обе таблицы отсортированы по ключу

4. Для оценки сортировки:

• < 1000 строк → Сортировка в памяти
• > 1000 строк → Может использовать tempdb (медленнее)

5. Для выбора индекса:

• Какой индекс покрывает больше условий WHERE
• Какой индекс имеет лучшую селективность

Для 1С особенно важно:

• Срезы последних часто создают сложные вложенные запросы
• Обращение через точку (Ссылка.Родитель.Наименование) убивает индексы
• Временные таблицы без индексов → всегда Table Scan
• Подзапросы не имеют собственных индексов → всегда Table Scan

Признаки проблем в плане:

• Key Lookup → не хватает покрывающего индекса
• Spill to tempdb → не хватает памяти для сортировки/хеша
• Большая разница Estimated Rows vs Actual Rows → устарела статистика

Шпаргалка для быстрой диагностики медленного запроса:

1. Какой тип доступа? (Seek vs Scan) → проверь % строк
2. Какой тип JOIN? → проверь размеры таблиц
3. Есть ли сортировка? → проверь объём данных
4. Соответствуют ли оценки? (Est vs Act) → проверь статистику

---

2. Очистка процедурного кэша

• Зачем: SQL Server кэширует планы запросов для ускорения повторных выполнений.
• Проблема: После обновления статистики в кэше могут остаться старые, неоптимальные планы, которые будут использоваться вместо новых.
• Как помогает: Очистка кэша (DBCC FREEPROCCACHE) принудительно заставляет SQL Server перестроить планы с учётом свежей статистики. Выполняется сразу после её обновления.

3. Дефрагментация индексов

• Зачем: При интенсивной работе данные на диске фрагментируются (разбрасываются).
• Проблема: Фрагментация заставляет диск совершать лишние операции чтения (I/O), замедляя запросы, особенно диапазонные.
• Как помогает: Упорядочивает физическое хранение данных, снижая нагрузку на диск и ускоряя работу индексов. Можно выполнять часто, без длительных блокировок.

4. Реиндексация таблиц

• Зачем: Полное перестроение индексов "с нуля".
• Проблема: Со временем индексы теряют эффективность из-за высокой фрагментации или большого количества удалений.
• Как помогает: Максимально оптимизирует структуру индексов, что даёт наибольший прирост производительности для тяжёлых запросов. Блокирует таблицы, поэтому выполняется в период простоя.

Планы запросов

Что такое план запроса и зачем он нужен?

План запроса — это пошаговая инструкция от СУБД, показывающая конкретные алгоритмы выполнения SQL-запроса. Это "отладчик" для SQL-кода, который показывает реальную работу СУБД изнутри.

Зачем анализировать планы?

• Понимать реальную производительность (не только на тестовых данных)
• Находить узкие места — операции, которые "съедают" время
• Предотвращать проблемы в боевой системе при росте данных
• Оптимизировать запросы на основе реального поведения СУБД

Ключевые концепции и операторы

Базовые алгоритмы СУБД СУБД использует понятные алгоритмы, знакомые программистам:

1. Доступ к данным:

• Scan (Сканирование) — полный перебор всех записей

  • Table Scan/Sequential Scan — перебор всей таблицы
  • Index Scan — перебор индекса + обращение к таблице
  • Index Only Scan (PostgreSQL) — поиск только по индексу

• Seek (Поиск по индексу) — быстрый доступ через "оглавление"

2. Соединения (Joins):

• Nested Loops — вложенные циклы. Хорош, когда одна таблица маленькая
• Hash Join — использует хеш-таблицу. Быстр для больших наборов
• Merge Join — слияние отсортированных данных (самый быстрый для больших данных)

3. Другие операторы:

• Sort — сортировка (дорогая операция)
• Filter — фильтрация данных
• Aggregate — группировка и агрегация (SUM, COUNT, AVG)
• Key Lookup (MS SQL) — поиск недостающих данных после индекса

4. Структура плана

План — это цепочка операторов, где каждый:

• Получает данные от предыдущего
• Выполняет свою операцию
• Передаёт результат дальше

Данные движутся снизу вверх/справа налево:

Источники данных → Фильтрация → Соединения → Сортировка → Результат

Как читать и анализировать планы

Ключевые метрики:

Для MS SQL Server:

• Стоимость (%) — относительная "цена" операции
• Число строк — сколько строк обработано
• Reads — количество чтений с диска
• Duration — время выполнения

Для PostgreSQL:

• Стоимость 0.00..123.45:
  • Первое число — получение первой строки
  • Второе число — получение всех строк
  • Большая разница = проблема!
• Actual rows vs Estimated rows — сравнение оценки и реальности

Алгоритм анализа:

1. Найти самый "дорогой" оператор (самую высокую стоимость)
2. Проверить расхождения между оценкой и реальностью
3. Проанализировать вложенность (операторы внутри операторов)
4. Оценить количество обрабатываемых строк

Инструменты для работы с планами

Для MS SQL Server:

1. SQL Server Profiler

   • Что делает: Перехватывает запросы в реальном времени
   • Ключевые настройки:
     • События: RPC:Completed, Showplan XML Statistics Profile
     • Фильтры: по базе, длительности (>1000 мс), ключевому слову
   • Что смотреть: Duration, CPU, Reads, RowCount

2. SQL Sentry Plan Explorer

   • Визуализация сложных планов
   • Фильтр по % стоимости для поиска "тяжёлых" операций

Для PostgreSQL:

1. Визуализаторы онлайн:

   • https://explain.depesz.com/ — дерево операторов
   • https://tatiyants.com/pev/ — табличное представление + справка по операторам

2. Источники планов:

   • Технологический журнал 1С
   • PgAdmin (через EXPLAIN ANALYZE)

Типичные проблемы 1С и их признаки в планах

1. Общий поиск ("Поиск подобно")

• Признак: LIKE '%значение%' по нескольким полям
• Проблема: Полное сканирование таблиц (Scan)
• Решение: Отключить общий поиск в динамических списках

2. Сортировка по неиндексированным полям

• Признак: ORDER BY по полям без индекса или через точку
• Проблема: Дорогая операция Sort
• Решение: Убрать сортировку или добавить индекс

3. Отсутствующие индексы

• Признак: Key Lookup (MS SQL) / Index Scan вместо Index Only Scan
• Проблема: Обращение к таблице после поиска по индексу
• Решение: Добавить покрывающий индекс

4. Большие временные таблицы

• Признак: RowsAffected > 100 000
• Проблема: Перегрузка tempdb, нехватка памяти
• Решение: Добавить отборы, оптимизировать алгоритм

5. Проблемы с "Срезом последних"

• Признак: Сложные вложенные запросы, много loops
• Проблема: 1С генерирует неоптимальные запросы
• Решение:
  • Создать временную таблицу с измерениями
  • Добавить индексы
  • Упростить группировку

6. Пропуск поля в составном индексе

• Пример: Индекс по (Организация, Склад, Номенклатура)
• Запрос: WHERE Организация = ? AND Номенклатура = ? (пропущен "Склад")
• Результат: Index Scan может быть МЕДЛЕННЕЕ Sequential Scan

Процесс анализа на боевой базе

Этап 1: Поиск проблемных запросов

1. Настроить технологический журнал на захват "долгих запросов" (порог: 10-60 сек)
2. Использовать мониторинг
3. Сортировать по Duration, RowsAffected

Этап 2: Предварительный анализ

1. По полю Context определить источник проблемы
2. Изучить SQL-текст, найти антипаттерны:
   • LIKE '%...%'
   • ORDER BY по сложным полям
   • Отсутствие отборов

Этап 3: Глубокий анализ

1. Перехватить запрос через Profiler
2. Получить план выполнения
3. Проанализировать в Plan Explorer или визуализаторе
4. Найти самый "дорогой" оператор

Этап 4: Оптимизация

1. Исправить антипаттерны в коде 1С
2. Добавить недостающие индексы
3. Переписать сложные запросы (особенно "Срезы последних")
4. Проверить актуальность статистики

Критические выводы и рекомендации

Общие принципы:

1. Не тестируйте на маленьких данных! Поведение на миллионах записей — совершенно другое
2. Статистика должна быть актуальной — основа правильных планов
3. План запроса — это просто — цепочка вызовов функций

Для разработчиков 1С:

1. Избегайте антипаттернов
2. Используйте отборы везде, где возможно
3. Следите за индексами:
   • Не пропускайте поля в составных индексах
   • Используйте покрывающие индексы

Для администраторов:

1. Регулярное обслуживание:

   • Обновление статистики (ежедневно)
   • Очистка процедурного кэша (после обновления статистики)
   • Дефрагментация индексов (еженедельно)

2. Мониторинг:

   • Размер tempdb
   • Долгие запросы
   • Блокировкив

80% проблем производительности решаются анализом планов без глубокого погружения в SQL.

Материалы

• Работа с базой данных TEMPDB

• Регламентные операции на уровне СУБД для MS SQL Server

• Смотрим запросы 1С через Microsoft SQL Profiler по следам ошибок разработчиков, приводящих к проблемам производительности

• Планы запросов - это просто!

• Планы запросов - это просто! Разбор оптимизаций запросов PostgreSQL на живых примерах