Примеры "тяжелых" запросов в системе 1С 7.7, неэффективное использование хинтов оптимизации запросов в 1С   

1. В любой ИТ системе могут существовать «тяжелые»(несбалансированные) запросы существенно понижающие масштабируемость ИТ системы, приводящие к замедлению обслуживания операций других пользователей. Следствием этого является, что при наличии системы и технологии мониторинга производительности существенно понижаются риски остановки системы или потери части ее функциональности.
2. Оптимизация индексов и планов запросов может существенно (на порядки) поднять скорость их выполнения и снизить ресурсы на их обслуживание (впрочем неправильно выполненные подобные работы могут привести к плачевным последствиям).

 Эксперимент

В данном примере берется запрос 1с, отображающий справочник клиентов с сортировкой по реквизиту адрес.
SC46 – SQL таблица соответствующая справочнику Клиенты

Row_ID (int) – Уникальный идентификатор строки. 
Descr char(100)– Наименование справочника Клиенты
SP4135 char(254)- Реквизит Адрес справочника Клиенты

Индекс VI4135 состоит из следующих полей (по порядку слева направо ): SP4135, Descr, Row_ID

Количество записей в таблице = 163101.

Обращение к SQL серверу формирующееся при открытии справочника, с заданной сортировкой по реквизиту Адрес, является динамическим курсором. Содержание курсора следующее:

Select  * from SC46(NOLOCK INDEX=VI4135) order by SP4135, ROW_ID.

Select  top 50 * from SC46(NOLOCK INDEX=VI4135) order by SP4135, ROW_ID

Обратите внимание, что в конструкции используется явное указание индекса NOLOCK INDEX=VI4135. Это указывает серверу, что стоит осуществлять сканирование и соответственно выбирать четкий план запроса по индексу VI4135. В этом содержится серьезная ошибка с точки зрения производительности и масштабируемости системы. Индекс VI4135 составной и он в себя включает три поля: SP4135, DESCR, SP4135. В запросе 1С поле DESCR исключается, в результате чего этот запрос становится неэффективным во всех отношениях.

Этот пример является достаточно показательным во всех отношениях. На этом примере можно рассматривать и работу буферизации MSSQL, и проблемы нехватки оперативных ресурсов (оперативной памяти), и проблемы нагрузки на дисковую подсистему и т.д. и т.п. В данной статье я рассмотрю кратко все области понемногу.

Рассмотрим как в QA будет выполняться три запроса:

Select  top 50 * from SC46(NOLOCK INDEX=VI4135) order by SP4135, ROW_ID

Select  top 50 * from SC46 order by SP4135, ROW_ID

Select  top 50 * from SC46(NOLOCK INDEX=VI4135) order by SP4135, Descr, ROW_ID 

Запрос №1 
Select  top 50 * from SC46(NOLOCK INDEX=VI4135) order by SP4135, ROW_ID

Статистика по операциям чтения:

Table 'SC46'. Scan count 1, logical reads 529256, physical reads 7096, read-ahead reads 13103.

Основная статистика по плану запросов:

Sort/TopN 

Cost : 266 (35%)
CPU Cost: 2
IO Cost: 262
Estimated Row Cost: 1093
Row count: 163101


Bookmark Lookup
Cost : 476(63%)
CPU Cost: 0
IO Cost: 476 
Estimated Row Cost: 1109
Row count: 163101

Время выполнения:

10203 мс

Интересно будет также понаблюдать за нагрузкой на дисковые массивы и на эффективность используемой свободной оперативной памяти на сервере.

В момент запуска этого запроса показания счетчика Page life expectancy резко уменьшилось с 1800 до 200. Значения счетчика Avg Disk Queue length наоборот увеличилось с 0,6 до среднего 23 за промежуток времени, который выполняется запрос.

Запрос №2 
Select  top 50 * from SC46 order by SP4135, ROW_ID 

Статистика по операциям чтения:

Table 'SC46'. Scan count 1, logical reads 23338, physical reads 1, read-ahead reads 4212.


Основная статистика по плану запросов:

Sort/TopN 

Cost : 266 (94%)
CPU Cost: 2
IO Cost: 262
Estimated Row Cost: 50
Row count: 50


Bookmark Lookup
Cost : 17(6%)
CPU Cost: 0
IO Cost: 17
Estimated Row Cost: 163101
Row count: 163101

Время выполнения:

4105 мс

Системные счетчики: 

Запрос №3 
Select  top 50 * from SC46 order by SP4135,DESCR, ROW_ID 

Статистика по операциям чтения:

Table 'SC46'. Scan count 1, logical reads 199, physical reads 3, read-ahead reads 255.

Основная статистика по плану запросов:

Clustered Index Scan
Cost : 4
CPU Cost: 4
IO Cost: 4
Estimated Row Cost: 50
Row count: 149

Время выполнения:

156  мс

Системные счетчики: 

Выводы

Анализируя план запроса №1 видно, что основные действия по обслуживанию запроса идут на операцию Bookmark Lookup. Это явно лишняя и неоптимальная операция. Сервер выбирает ее лишь только по тому, что клиентское приложение 1С ему явно указывает в хинте запроса идти по индексу VI4135. В результате выполняется лишняя операция, которая существенно увеличивает как нагрузку на сервер, так и увеличивает время исполнения запроса. По этому запросу видно: несмотря на то что временные характеристики являются важнейшими параметрами в оценке эффективности SQL запросов, тем не менее, они не являются достаточными. Подобный запрос является крайне несбалансированным с точки зрения распределения общей нагрузки. При выполнении он может отнять очень много ресурсов и этим сильно понизить общую масштабируемость системы. Во время его выполнения SQL серверу может не хватать оперативной памяти на буферизацию данных, следствием чего станет «торможение» операций, выполняемых другими пользователями.

Во втором случае, если убрать хинты подсказок, оптимизатор SQL сервер сам выберет более оптимальный план запроса. На этом примере показано, что пользоваться хинтами и в явном виде выбирать план запроса, при недостатке знаний, лучше не стоит. Это может в некоторых случаях очень сильно подорвать масштабируемость всей системы. Проблемы будут не только у пользователя, который будет долго ожидать выполнения отчета, но и у остальных пользователей системы т.к. подобный запрос перетянет на себя большую часть ресурсов. В данном случае необходимо проводить обязательный мониторинг системы на предмет возникновения подобных отчетов. По сравнению с вариантом 1, в результатах выполнения запроса №2 отсутствует лишняя операция Bookmark Lookup и начальное сканирование идет по первичному ключу. Следствием этого является меньшее время выполнения и, что более важное, он гораздо меньше отнимает свободных ресурсов и у него меньше нагрузка на дисковые массивы.

Тем не менее, если мы посмотрим результаты запроса №3, то мы увидим, что он отнимает на порядки меньше ресурсов и на порядки меньше время выполнения. Разница в результате заключается только в том, что если вдруг в БД будет 2-а клиента с одинаковым адресом (что само по себе маловероятно) то в этом случае они будут отсортированы не по Row_ID а по Наименованию. Понятно, что это не принципиально с точки зрения результата. Почему же в этом случае возникает такая большая разница? Разгадка кроется в наличии и составе индекса VI4135. Он составной и состоит из следующих полей (по порядку слева направо): SP4135, Descr, Row_ID. В случае если мы указываем Order by SP4135, Descr, Row_ID или указываем INDEX=VI4135 – в этом случае мы фактически выбираем подряд записи из индекса. Все дополнительные ресурсоемкие операции, типа сортировки длинных текстовых полей, отсутствуют, так как сам по себе индекс уже упорядочен.

P.S. Данная конструкция для 1С 7.7. довольно типовая, и при больших объемах справочников и наличии больших строковых реквизитов с сортировкой может приводить к катастрофическим последствиям. Тем не менее эта ситуация решается. Компания "СофтПоинт" предлагает комплексные решения по внедрению системы мониторинга производительности, которая быстро поможет идентифицировать все «узкие» места производительности в вашей ИТ системе. Эта система позволят идентифицировать проблемы для произвольных ИТ систем, но для системы 1С 7.7. существует отдельная экспертная база данных настроенная с учетом ее специфики. Все подобные запросы легко «ловятся», и предлагаются варианты разрешения подобных проблем. По описанной выше проблеме существует отдельная внешняя компонента, которая подменяет вызовы 1С на более эффективные и таким образом разрешает проблемы производительности.

Перепечатка, воспроизведение в любой форме, распространение, в том числе в переводе, любых материалов с сайта www.softpoint.ru возможны только с письменного разрешения компании "СофтПоинт". Это правило действует для всех без исключения случаев, кроме тех, когда в материале прямо указано разрешение на копирование (основание: Закон Российской Федерации "Об авторском праве и смежных правах").