Оставаясь на сайте, вы принимаете условия Политики конфиденциальности и использования файлов-cookies

Linked Server это

Подводные камни при использовании Linked Server

90
0

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


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

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

Почему Linked Server?

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

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

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

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

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

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

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

Данный метод был реализован с использованием службы Service Broker и SignalR:

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

Почему SignalR?

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

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

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

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

Дальнейший анализ показал, что это запросы с Linked Server. Сразу возник вопрос: «Неужели запрос такого типа select * from RemoteServer.RemoteDatabase.dbo.RemoteTable where FieldId = @Value порождает запрос (fetch api_cursor0000000000000003)на Remote Server

Оказывается, да, и даже тогда, когда Linked Server — это MS SQL.

Для более наглядного примера создадим таблицу «Test» (код создания таблицы доступен в приложении к статье) на сервере «А», а на сервере «B» выполним запрос select * from dev2.test_db.dbo.test, где dev2 — это наш сервер «А».

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

linked server
Часть лога, полный лог доступен по ссылке.

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

select * from dev2.test_db.dbo.test where ID = 3
linked server
Часть лога, полный лог доступен по ссылке.

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

linked server
Часть лога, полный лог доступен по ссылке.

Как мы видим, данные уже берутся из кэша. При изменении условий мы получим аналогичную выборку — первая выборка по заданному Id.

Но суть в том, что при больших количествах разных запросов кэша не хватает. И sql начинает городить кучу запросов к таблице, что приводит к «тормозам».

Вы спросите: «А как же индексы?» Индексы есть, но запросы даже с условием по PK (Primary Key) порождали данную проблему.

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

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

Более толковые ответы были найдены только в 3-х статьях:

Насколько я разобрался, нельзя настроить Linked Server так, чтобы всегда использовалась Pull технология для получения данных с Linked Server. Все зависит от того, где вы обрабатываете запрос.

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

Работа с данными на Linked Server

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

  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 
    OpenQuery – выборка с заданным Id.trc;
    select * from OPENQUERY(dev2, 'select * from test_db.dbo.test where ID = 26' 
    OpenQuery – выборка с заданным Id в качестве параметра.trc.

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

    По ссылкам доступны примеры логов для следующих запросов:
    • exec ('select * from test_db.dbo.test') at dev2exec at server — на стороне клиента.trc; exec at server – на стороне сервера.trc;
    • exec ('select * from test_db.dbo.test where Id = 30') at dev2exec at server – на стороне клиента с заданным Id.trc; exec at server – на стороне сервера с заданным Id.trc.

  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 — Полная выборка на клиенте.trc;
  • sp_executesql — Полная выборка на стороне сервера.trc;
  • sp_executesql — Выборка по Id на клиенте.trc;
  • sp_executesql — Выборка по Id на сервере.trc.

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

Ниже приведены несколько графиков входящего и исходящего трафика на сервере, где расположена основная база очереди, до и после использования sp_executesql:

linked server

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

linked server

На графике показан входящий и исходящий трафик, после начала использования sp_executesql. Исходящие пики — это копирование backup на NFS.

Вывод

Изначально драйвер от MS для работы с «MS SQL Linked Server» не может сам выполнять запросы на сервере источнике данных. Следовательно, коллеги, давайте стараться выполнять их на источнике данных, для решения хотя бы части вопросов с производительностью.

Оригинальная статья размещена на Хабрахабр.