Підводні камені при використанні Linked Server

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

Якщо коротко, то проект з себе представляє кілька БД і додатків, розташованих на різних серверах. «Задача» в даному проекті – це збережена процедура або .Net додаток. Відповідно «завдання» повинна бути виконана на певній БД і на певному сервері.
image

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

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

Чому так?
  1. Зручність. Ми можемо в будь-який момент вказати, що тепер на сервері Б зберігаються дані.
  2. Так було реалізовано до нас.
Нижче наведено два найбільш популярних класичних способу обробки черги:

  1. Відправляти повідомлення обробникові завдань про наявність завдання.
  2. Проводити опитування черзі на наявність завдань.
Спочатку в проекті був реалізував другий варіант. Щоб мінімізувати час очікування обробки завдань, наш додаток опитує чергу кожен 100-500ms.

Власне, в цьому нічого страшного немає, крім одного – при такій реалізації таблиця зайвий раз блокується. Наперед скажу, у запиті використовується блокування рядків з можливістю читання незаблокованих рядків:

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 — це наш сервер «А».

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

Частина лода на сервері А

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

А тепер виконаємо запити по ID:

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

Лог профайлера для другого запиту

Повний лог доступний тут.

Як видно на скріншоті, план запиту був доданий кеш. Якщо виконати цей запит другий раз, то вже трохи краще.

лог профайлера після повторного запуску

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

Як ми бачимо, дані беруться з кеша.

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

А що Google говорить з цього приводу? А багато чого, тільки толку немає:

  • Що запити повинні виконуватися від користувача, який відноситься до однієї з наступних ролей: системний адміністратор, 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

вхідний та вихідний трафік, після початку використання sp_executesql

Вихідні піки – це копіювання backup на NFS.

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

Файлы до статті.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.