Подводные камни при использовании Linked Server09.06.2016 14:03

В нашу компанию пришел достаточно интересный проект, связанный с обработкой очереди задач. Проект был разработан ранее другой командой. Нам необходимо было разобраться с проблемами, возникающими при большой нагрузке на очередь, и, соответственно, исправить найденные.

Если вкратце, то проект из себя представляет несколько БД и приложений, расположенных на разных серверах. «Задача» в данном проекте — это хранимая процедура или .Net приложение. Соответственно «задача» должна быть выполнена на определённой БД и на определенном сервере.
image
Все данные, которые относятся к очереди, хранятся на выделенном сервере. На серверах, где необходимо выполнять задачи, хранятся только метаданные, т.е. процедуры, функции и служебные данные, которые относятся к этому серверу. Соответственно, данные, относящиеся к задачам, мы получаем запросами с использованием LinkedServer.

Все данные, которые относятся к очереди, хранятся на выделенном сервере. На серверах, где необходимо выполнять задачи, хранятся только метаданные, т.е. процедуры, функции и служебные данные, которые относятся к этому серверу. Соответственно, данные, относящиеся к задачам, мы получаем запросами с использованием LinkedServer.

Почему так?


  1. Удобство. Мы можем в любой момент указать, что теперь на сервере Б хранятся данные.
  2. Так было реализовано до нас.


Ниже приведены два наиболее популярных классических способа обработки очереди:

  1. Отправлять уведомление обработчику задач о наличии задачи.
  2. Производить опрос очереди на наличие задач.


Изначально в проекте был реализовал второй вариант. Чтобы минимизировать время ожидания обработки задач, наше приложение опрашивает очередь каждый 100–500ms.

Cобственно, в этом ничего страшного и нет, кроме одного — при такой реализации таблица лишний раз блокируется. Наперед скажу, в запросе используется блокировка строк с возможностью только чтения незаблокированных строк: 

READPAST, ROWLOCK, UPDLOCK


Итак, вернемся к проблеме. При анализе я обратил внимание на значение счетчика — batch requests/sec в Active Monitor. Данное значение при малом количестве (около 50) задач в очереди, зашкаливало за 1000, а также нагрузка на CPU резко возрастала.

Первая мысль: нужно переходить к реализации первого варианта (отправка уведомления обработчику задач). Данный метод был реализован с использованием службы Service Broker и SignalR:

  • Service Broker использовали для отправки уведомления о появлении задачи;
  • SignalR использовали для отправки уведомления обработчикам задач.


Почему SignalR?


Данный инструмент уже используется в проекте, а сроки были сжаты, поэтому я не стал внедрять что-то аналогичное, например, NServiceBus.

Моему удивлению не было предела, когда данное решение не помогло. Да, был получен прирост в производительности, но это не решило проблему окончательно. Для отладки был написан стресс-тест, когда в очередь добавляется более 500 задач.

Создание такого стресс-теста позволило найти »корень зла».

Анализ списка активных запросов и отчетов производительности, во время большой нагрузки показа наличие «очень интересных запросов», которые состояли из одной команды: 

fetch api_cursor0000000000000003


Дальнейший анализ показал, что это запросы с LinkedServer. Сразу возник вопрос: «Неужели запрос такого типа select * from RemoteServer.RemoteDatabase.dbo.RemoteTable where FieldId = Value порождает запрос (fetch api_cursor0000000000000003) на RemoteServer?» Оказывается, да, и даже тогда, когда LinkedServer — это MS SQL.

Для более наглядного примера созданим таблицу «Test» (код создания таблицы доступен в приложении к статье) на сервере «А», а на сервере «B» выполним запрос: 

select * from dev2.test_db.dbo.test


где dev2 — это наш сервер «А».

При первом выполнении такого запроса у нас будет подобный лог в профайлере на сервере А:

Часть лога на сервере А
e9e254f7f80e4a628ea1faf24b4b6a80.png

Полный лог доступен здесь.


А теперь выполним запросы уже по ID: 

select * from dev2.test_db.dbo.test where ID = 3


Лог профайлера для второго запроса
5698d599de39404ba1ab9b8f4ff1e135.png

Полный лог доступен тут.


Как видно на скриншоте, план запроса был добавлен в кэш. Если выполнить этот запрос второй раз, то уже немного лучше.

лог профайлера после повторного запуска
fc85916c3cef4218b8846dce0c6632ec.png

Полный лог доступен здесь


Как мы видим, данные уже берутся из кэша.

При изменении условий мы получим аналогичную выборку — первая выборка по заданному Id. Но суть в том, что при больших количествах разных запросов кэша не хватает. И sql начинает городить кучу запросов к таблице, что приводит к «тормозам». Вы спросите: «А как же индексы?» Индексы есть, но запросы даже с условием по Primary Key (PK) порождали данную проблему.

А что Google говорит по этому поводу? А много чего, только толку нет:

  • Что запросы должны выполняться от пользователя, который относится к одной из следующих ролей: sysadmin, db_owner, db_ddladmin, чтобы можно было использовать статистику;
  • Неверно настроен LinkedServer.


Более толковые ответы были найдены только в 3-х статьях:
Насколько я разобрался, нельзя настроить LinkedServer так, чтобы всегда использовалась Pull технология для получения данных с LinkedServer. Все зависит от того, где вы обрабатываете запрос.

Время поджимало, и единственное решение, которые нас могло спасти, это переписать часть запросов на dynamic sql. Т.е. выполнять запросы на сервере, на котором хранятся данные.

Работать с данным на LinkedServer можно несколькими способами:

  1. В запросе непосредственно указать источник данных — удаленный сервер. Данная реализация имеет несколько недостатков:
    • низкая производительность;
    • возращает большой объем данных.
    select * from RemoteServer.RemoteDatabase.dbo.RemoteTable where Id = @Id

  2. Использовать OPENQUERY. Не подходит по ряду причин:
    • невозможно указать имя удаленного сервера в качестве параметра;
    • передать параметры в запрос;
    • также существуют проблемы, которые были описаны в статье Dynamic T-SQL и как он может быть полезен
    select  * from OPENQUERY(RemoteServer, 'select * from RemoteDatabase.dbo.RemoteTable').

    По ссылкам доступны примеры логов для следующих запросов. Данные запросы выполнятся на сервере «B», а логи с сервера «A»: 
    select  * from    OPENQUERY(dev2, 'select * from test_db.dbo.test') where id = 26


    select  * from    OPENQUERY(dev2, 'select * from test_db.dbo.test where ID =  26')

  3. Выполнить запрос на удаленном сервере. Аналогично OPENQUERY:
    • нельзя указать имя сервера в качестве параметра, так как имя задается на этапе компиляции процедуры;
    • также существуют проблемы, которые были описаны в статье Dynamic T-SQL и как он может быть полезен
    exec ('select * from RemoteDatabase.dbo.RemoteTable') at RemoteServer

    По ссылкам доступны примеры логов для следующих запросов: 
    exec ('select * from test_db.dbo.test') at dev2


    exec ('select * from test_db.dbo.test where Id = 30') at dev2

  4. Еще возможно выполнить запрос на удаленном сервере, выполнив процедуру sp_executesql.
    DECLARE @C_SP_CMD nvarchar(50) =  QUOTENAME(@RemoteServer) + N'.'+@RemoteDatabase +N'.sys.sp_executesql'
DECLARE @C_SQL_CMD nvarchar(4000) = 'select * from dbo.RemoteTable'
EXEC @C_SP_CMD @C_SQL_CMD

    По ссылкам доступны примеры логов выполнения запросов с использованием sp_executesql:


Четвертый способ и был использовал для решения задачи.

Ниже приведены несколько графиков входящего и исходящего трафика на сервере, где расположена основная база очереди до и после использования sp_executesql. При этом размер БД 200–300Мб.

входящий и исходящий трафик за несколько дней на сервере, до использования sp_executesql
6b66e534cbda4cac8254383842a6c4ea.png


входящий и исходящий трафик, после начала использования sp_executesql
26b804fe09fe420bb336b902ad78997c.png


Исходящие пики — это копирование backup на NFS.

Напрашивается вывод: изначально драйвер от MS для работы с «MS sql linked server» не может сам выполнять запросы на сервере источнике данных. Следовательно, коллеги, давайте стараться выполнять их на источнике данных, для решения хотя бы части вопросов с производительностью.

Файлы к статье.

© Habrahabr.ru