Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Нужна подсказка  [new]
Greenhorn
Member

Откуда:
Сообщений: 311
Добрый день.

+ SELECT @@VERSION
Microsoft SQL Server 2014 - 12.0.4416.0 (X64)
Jun 11 2015 19:18:41
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

В БД есть исполнительная подсистема на базе Service Broker позволяющая выполнять "асинхронно" (т.е. не дожидаясь результатов)
различные задания/процессы.
Основная функция БД - поддержка интеграционных сайтов. Т.е. единичный запрос с сайта превращается в процесс получения данных из кучи систем (MSSQL,Oracle,HTTP,SOAP...).
Далее обрабатывается по заданным бизнес правилам и выдает результат(ы) на сайт.
В последнее время появилось много задач когда в БД просто создается задание и выдается обратно лишь ID этого задания, а задание после выполнения само доставит результат к потребителям.
Все это прекрасно работает уже не первый год ...

Появилась задача, обеспечить очередь заданий в которую задания из внешних систем будут загружаться в больших количествах (до 10 миллионов за раз).

В связи с этим появилась необходимость контролировать кол-во отправленных на выполнение заданий, что бы не допустить перегрузки системы, и тем более тормозов старого функционала.
С этим проблем нет. Организовал "пакетный" запуск и обратную связь от исполнительной подсистемы, с автоматическим отслеживанием нагрузки и т.д. и т.п.

Собственно очередь заданий в виде секционированной таблицы уже есть и давно используется. Одним из ключевых механизмов является расчет времени следующей попытки выполнить задание.
Данный механизм был реализован на базе AFTER INSERT, UPDATE триггера на таблице - очереди.
Триггер нормально работает на единичных insert_ах/update_ах, какие и были до появления задачи по массовой загрузке.

Требуется подсказка в выборе/подходе к поддержке массовой загрузки заданий с корректировкой времени следующей попытки выполнить задание для "распределения" этой массы от текущего времени в будущее.

К сожалению не корректировать время запуска нельзя, т.к. старый функционал создавая задания будет помещать их в "конец" очереди, что недопустимо.
Можно, конечно, помещать старые задания и в "начало" очереди, но тогда пропадут необходимые "паузы" перед следующей попыткой выполнения.

И самое главное - расчет времени должен быть основан на статистики времени выполнения "запросов" на конкретных ресурсах исполнительной подсистемы. Сбор данных уже реализован и используется внешними системами мониторинга чего не попадя ...

+ Тестовый набор данных
--------- Дано:
-- Очередь заданий (задание = процесс)
if object_id('tempdb..#dst') is not NULL
  DROP TABLE #dst

CREATE TABLE #dst( id int identity(1,1), ProcName nvarchar(128), NextRun smalldatetime, SomeParams int NULL)

-- выборка: Процессы <-> Ресурсы исполнительной системы
if object_id('tempdb..#srv') is not NULL
  DROP TABLE #srv

CREATE TABLE #srv( 
    ProcName nvarchar(128)  -- Имя процесса
  , SrvName nvarchar(128)   -- Имя сервиса
  , ThCnt int               -- Макисмальное кол-во потоков в сервисе                    
  , AVGDura int             -- Среднее время выполнения процесса на этом сервисе в сек. 
  , PRIMARY KEY(ProcName, SrvName)
)
/* Где 
  (SrvName, ThCnt)              -> из справочника сервисов
  (ProcName, SrvName, AVGDura)  -> из системы runtime мониторинга 
*/

-- таблица inserted в триггере INSTEAD OF INSERT, UPDATE
if object_id('tempdb..#ins') is not NULL
  DROP TABLE #ins

CREATE TABLE #ins( id int identity(1,1), ProcName nvarchar(128), NextRun smalldatetime, Params int )

--------- Тестовый набор
INSERT INTO #srv VALUES
 ('PROC_1', 'SRV_1', 1, 20)
,('PROC_1', 'SRV_2', 2, 30)
,('PROC_2', 'SRV_2', 2, 10)
,('PROC_2', 'SRV_3', 2, 30)
,('PROC_3', 'SRV_3', 2, 10)
,('PROC_3', 'SRV_4', 4, 50)
--
--,('PROC_4', 'SRV_5', 1, 10)
-- ...

INSERT INTO #DST(ProcName, NextRun) VALUES
 ('PROC_2', '20151210 12:00')  
,('PROC_2', '20151210 12:00') 
,('PROC_1', '20151210 12:00') 
,('PROC_1', '20151210 12:01') 
,('PROC_3', '20151210 12:02') 
,('PROC_3', '20151210 12:02') 

-- новая порция заданий

INSERT INTO #ins( ProcName, NextRun ) VALUES
 ('PROC_1', '20151210 12:00')
,('PROC_1', '20151210 12:00')
,('PROC_1', '20151210 12:00')
,('PROC_2', '20151210 12:00')
,('PROC_2', '20151210 12:00')
,('PROC_3', '20151210 12:00')


+ Проверочный запрос
-- Используемые ресурсы. 
SELECT t.[RUN_DT], s.SrvName, s.ThCnt
  , [Used_Res] = s.AVGDura * SUM( CASE WHEN d.ProcName is null THEN 0 ELSE 1 END)
  , [Totall_Res] = s.ThCnt * 60 
  , [Avail_Res] = (s.ThCnt * 60) - (s.AVGDura * SUM( CASE WHEN d.ProcName is null THEN 0 ELSE 1 END))
FROM #srv as s
CROSS APPLY(
  SELECT t.[RUN_DT]
  FROM (SELECT min(d.NextRun), max(d.NextRun) from #dst as d) as c(MinNR,MaxNR)
  CROSS APPLY(
    SELECT TOP(1 + DATEDIFF(minute, MinNR, MaxNR)) 
      cast(DATEADD(minute, (ROW_NUMBER() OVER(ORDER BY 1/0)) -1, '20151210 12:00') as smalldatetime) as [RUN_DT]
    FROM sys.objects
  ) as t
) as t
LEFT OUTER JOIN #DST as d
  on s.ProcName = d.ProcName
  and d.NextRun = t.RUN_DT
GROUP BY t.[RUN_DT], s.SrvName, s.ThCnt, s.AVGDura
ORDER BY 1


В случае нехватки [Avail_Res] по любому из сервисов процесса из #ins требуется увеличить NextRun на минуту и проверить возможность вставки в получившийся диапазон.

Поле [Avail_Res] не должно стать отрицательным после вставки.
В примере длительность в секундах, на самом деле все измеряется в миллисекундах.

Заранее спасибо за идеи.
11 дек 15, 11:55    [18545290]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить