Система сбора и анализа информации по производительности работы баз данных, работающих  под связкой «кластер 1С 8.2/8.3 — Microsoft SQL server»

    Идея довольно проста. Берем парсер файлов техжурнала, настраиваем ТЖ на ловлю запросов долее N секунд, данные из парсера заливаем в sql-табличку. Рядом с парсером настраиваем трассировку длительных запросов (долее M секунд) уже со стороны sql-сервера (либо server-side trace sp_trace_create, либо используем eXtended Events) — результат тоже в sql-табличку. Делаем это непрерывно, данные старше 2 недель удаляем.  Две таблички вполне можно связать друг с другом — получим с одной стороны статистику из трасс (Duration, cpu_time, logical_reads, physical_reads, writes), а с другой — контекст исполнения, пользователя в 1С, имя клиентского компа и т.д.  Текст запроса огрубляем.

    Теперь можно анализировать загрузку sql-сервера за период — выявление самых длительных запросов, нагружающих CPU, читающих и т.д.  При этом, выявив "тяжелый" запрос — сразу видим и контекст исполнения, и историю исполнения, а при желании — и текст запроса в терминах конфигурации.

    Далее, к получившейся программе (парсер + трасировщик) добавляем еще и периодический опрос sys.dm_exec_query_stats — и складываем статистику исполнения в третью sql-табличку. Теперь дополнительно добавляется еще и возможность поймать быстрые (быстрее M секунд), но очень массовые запросы, пожирающие время сессий, CPU, генерящие физические и логические чтения.  Также, теперь при просмотре "тяжелых" запросов из предыдущего пункта — можно исхитриться и подсмотреть план исполнения запроса (и в терминах конфигурации тоже).

  Используется все это, как правило, в двух режимах:

1. Анализ активности сервера за длительный период. За две недели, например, последовательно выявляем top 5 наиболее длительных запросов, каждый отдельно рассматриваем — нет ли грубых ошибок (сканы крупных таблиц в планах и т.п.), тормозят ли они живых людей или роботов, можно ли их ускорить, нужно ли их вообще трогать. Если нужно и можно — создаем задачу разработчикам — с детальным описанием проблемы, указанием проблемного контекста и еще прилагаем планы исполнения. Если есть проблемы с загрузкой sql-сервера по CPU — смотрим top 5 наиболее потребляющих CPU запросов (и частенько видим "знакомые все лица" из top 5 длительных) — и обрабатываем их аналогичным образом. Если есть проблемы с памятью на сервере (низкое значение Page Life Expectancy) — есть смысл отработать top 5 по physical reads. Top 5 по logical reads частенько может показать какие-то ошибки разработчиков в запросах (впрочем — может и НЕ показать). Top 5 по записям, бывает, может намекнуть на какую-то лишнюю активность в базах.

2. Расследование проблем. Если известно, что "в пятницу с трех до пяти тормозили базы А, Б и В на сервере С" и выявлено, что "в пятницу с трех до пяти загрузка sql-сервера С по CPU была ~100% " — можно посмотреть первую пятерку запросов по CPU в указанный период. Работает не всегда. Если запрос убил админ — он не попадет в трассу. Тем не менее, данный способ расследования много раз выручал. Само-собой, только данной системы здесь недостаточно — как минимум дополнительно должен быть настроен сбор счетчиков производительности. 

    Также, можно отдельно смотреть таблицу событий техжурнала — выводить top 5 контекстов по времени (тут, увы — только по времени), если были жалобы от МарьИванны — смотреть события от МарьИванны (или от ее компа GLAVBUH001), и т.д. Это ж sql-табличка, какой хотим запрос — такой и напишем..

    Ну и добавим в нашу программу еще несколько полезных штук: будем периодически делать снимки содержимого кеша данных sql-сервера, содержимого клерков памяти, ожиданий сервера, статистики ввода-вывода по файлам баз. 

    ВАЖНО. Выдержка из мануала про возможные проблемы:

Посему первым делом предупреждения:

1. Включение технологического журнала 1С на сбор данных по dbmssql  (а также по tlock / tdeadlock / ttimeout )  может привести к тем же переполнениям дисков. А т.к. нынче популярны SSD, размеры которых не поражают величиной – требуется отслеживать свободное место на дисках под сбор данных техжурнала и менять пороги времени (<gt property="duration" value="XXX" />) при необходимости. Вручную.

2. Сбор данных на sql-сервере происходит с помощью Extended Events. Расширенные события, конечно, позиционируются как «легковесные, не требующие много ресурсов», и эксперименты подтверждают, что старым добрым SQL Server Profiler-ом свалить сервер не в пример легче.. Но! У всего есть где-то свой предел, не удивлюсь, что и расширенными событиями можно отправить сервер в нокаут. Значит, обращаться следует как с трассировками: ничего лишнего, ненужное выключаем, пороги по времени задаем разумные, отслеживаем зависшие сессии.

3. Сама бд, которая хранит данные о производительности – получается весьма объемной.  Т.к. хранит тексты запросов и планов исполнения. Изначальная идея «чтоб все влезло в 10 Gb и шло на ноутбуке с ms sql enterprise» почти не работает.. если только задрать настройки. Т.е. придется следить за объемом базы для сбора данных, и корректировать параметры глубины хранения истории (закладка «вспомогательные настройки»)