понедельник, 30 июля 2007 г. - www.msmirnov.ru

Опыт практического применения t-sql в системах распределенных баз данных

Здесь я привожу статью "Опыт практического применения t-sql в системах распределенных баз данных", опубликованную совместно с Андреем Смирновым в журнале RSDN Magazine #2 2007 г http://www.rsdn.ru/article/db/tsql.xml




Реализация параллельного выполнения запросов на T-SQL

ПРИМЕЧАНИЕ
Стоит сразу оговориться, речь не пойдет о реализации многопоточности в ее классическом понимании, т.е. о создании потоков, объектов синхронизации и прочих сопутствующих сущностей. Речь пойдет об имитации многопоточного выполнения t-sql кода в распределенной системе (на нескольких linked серверах) и синхронизации этого кода средствами, предоставляемого sql server job agent.
Всем известно, что в MS SQL Server отсутствует встроенный механизм асинхронного выполнения запросов – вы не можете из одного запроса или хранимой процедуры запустить другой запрос так, чтобы ваш запрос продолжил свою работу, не дожидаясь его окончания. Однако в некоторых случаях такая возможность может оказаться весьма полезной. Например, если ваш запрос или процедура проводит подготовку каких-либо данных, затем запускает несколько различных, независимых друг от друга процедур, а затем выполняет некоторые завершающие дествия. В обычном случае вы вынуждены запускать эти процедуры последовательно, одну за другой. Однако, было бы намного удобнее, если бы у вас была возможность запустить их параллельно. Это позволило бы существенно сэкономить время и не повлияло бы на работу этих процедур, так как они не зависят друг от друга.
Продемонстрируем это на диаграмме:
  1. Классический вариант. Последовательный вызов процедур.

Рисунок 1.
  1. Второй вариант. Параллельный запуск процедур из тела основной процедуры или запроса.

Рисунок 2.
В этой статье будет описана реализация второго подхода, позволяющая организовать параллельное выполнение запросов и синхронизацию их завершения.
Применять описываемый подход стоит в следующих случаях:
  • Есть несколько подряд идущих процессов обработки данных, последовательность выполнения которых не важна, и которые логически не зависят друг от друга.
  • Выполнение процессов происходит на разных серверах или на одном сервере, но важна синхронизация их завершения (т.е. в ситуациях, когда итоговому процессу требуются результаты выполнения нескольких процессов – см. диаграмму 2).
  • Параллельно выполняемые запросы являются достаточно независимыми в части обновления, удаления или вставки данных. В противном случае ни о каком выигрыше в производительности не может быть и речи. Напротив, результатом будет только падение производительности из-за ожидания процессами друг друга на защелках (“pageiolatch”) или блокировках (“lock”).
  • Для большей отдачи от данного подхода рекомендуется, чтобы каждый из процессов использовал таблицы, располагающиеся в разных файловых группах, идеально – когда еще и на отдельных дисковых устройствах, хотя это и не обязательно.
Когда не стоит применять данный подход:
  • Процессы пытаются добавлять, удалять, обновлять данные в одной таблице.
  • Есть дефицит ресурсов памяти.
  • Слабая дисковая подсистема

Предварительная настройка

Для демонстрации параллельного выполнения запросов необходимо иметь два экземпляра SQL Server. Между ними должны быть корректно настроены связи (“linked server”). Назовем их linked_server_01 для первого сервера, linked_server_02 – для второго. Для корректной работы приложенных примеров необходимо также, чтобы у каждого из серверов была настроена связь, ссылающаяся на самого себя. Важно правильно настроить security context для каждого из linked server-ов – доступ должен осуществляться от имени пользователя, имеющего набор прав для управления job’ами – в простейшем случае sysadmin. Для простоты можно установить security context от имени sa. В настройках server options каждого из linked server необходимо установить свойства RPC и RPC Out.
Более того, на каждом из серверов должен быть запущен SQL Server Job Agent.
К данной статье прилагается два набора SQL-скриптов. Первый из них реализует всю функциональность, описываемую в этой статье. Он содержит весь набор хранимых процедур, который потребуется для реализации описываемого механизма. Необходимо создать отдельную базу Tools, в которой следует выполнить данные запросы, создав, таким образом, все процедуры. Второй прилагаемый набор скриптов демонстрирует пример использования данного механизма. Вы можете создать базу Article_Sample_Data на первом сервере и выполнить на ней второй прилагаемый скрипт.
Проверить правильность настройки можно, запустив на каждом из серверов системную процедуру sp_helpserver.
Вы должны получить следующий результат:

Рисунок 3.
Здесь видно, что rpc, rpc out свойства linked_server-ов настроены правильно.
Для корректной работы скриптов на linked server-ах также необходимо убедиться в том, что на каждом из серверов работает служба Microsoft Distributed Transaction Coordinator (о проблемах с ее настройкой читайте дальше в разделе “ Описание проблем при настройке DTC и возможные их решения”). MS DTC используется MS SQL Server для поддержки распределенных транзакций (а здесь будет именно такой случай). Проверить работоспособность msdtc можно, запустив на каждом из MSSQL серверов следующий скрипт:
begin transaction

execute linked_server_01.article_sample_data.dbo.sp_executesql N'select getdate()'
execute linked_server_02.article_sample_data.dbo.sp_executesql N'select getdate()'

  commit
Если вы получили текущую дату, то можно сразу перейти к описанию реализации, в противном случае посмотрите подраздел статьи «Описание проблем при настройке DTC и возможные их решения»

Описание реализации

Как уже упоминалось, никаких встроенных средств для реализации параллельного исполнения запросов в t-sql нет, но есть SQL Server Job Agent, отвечающий за выполнение запросов по расписанию. Так что параллельного исполнения T-SQL можно добиться через создание заданий (“job”) для агента. Именно этот подход и рассматривается в этой статье. Стоит отметить, что он дает возможность параллельного выполнения запросов не только на одном сервере, но и на нескольких.
Кроме того, данный подход предоставляет возможность ограничения количества одновременно выполняющихся процессов. Если количество асинхронных запросов в пуле превышает установленный максимум, то запросы дожидаются своей очереди и обрабатываются по мере высвобождения ресурсов сервера. Такая необходимость возникает, если сервер ограничен в ресурсах памяти.
В самом общем случае такая система состоит из центрального управляющего сервера, на котором располагается пул запросов, и произвольного количества управляемых серверов, которым запросы передаются на исполнение (в нашем случае управляющим сервером будет linked_server_01).

Рисунок 4.
Единственным необходимым условием работы такой системы является возможность подключения серверов к центральному серверу как linked server (на каждом из серверов должен быть запущен Sql Server Job Agent). Это необходимо для того, чтобы предоставить возможность центральному серверу контролировать ход исполнения задач.
В простейшем случае, когда требуется выполнение нескольких задач в параллельном режиме, никаких дополнительных настроек или установки linked server на себя не требуется.
Целиком весь SQL-код, реализующий управление очередью заданий, можно увидеть в коде хранимых процедур прилагаемой базы данных Tools (см. Предварительная настройка).
Этот код можно изучить и детально обсудить с авторами – здесь же мы приводим только описание его «интерфейсной» части, доступной пользователю.
Использование данного механизма состоит из двух этапов – подготовка списка запросов и собственно его обработка.
Пул запросов должен содержаться в таблице со следующей структурой:
create table [TaskList] (
  linked_server nvarchar(100) DEFAULT (''),
  job_name      nvarchar(100) DEFAULT (''),
  toDelete      bit,
  checkRun      bit,
  job_step_name        nvarchar(200),
  job_step_sql_script  nvarchar(4000),
  job_step_database    nvarchar(200),
  status               bit NULL DEFAULT (1),
  job_started          bit NULL DEFAULT (0),
  job_prepare_for_synchronize  bit NULL DEFAULT (0),
  job_executed        bit NULL DEFAULT (0),
  id          int identity(1,1) NOT NULL  
) 
Из всех полей этой таблицы обязательными к заполнению являются следующие:
  1. job_name – имя задания, которое будет создано для выполнения запроса. Это имя может помочь вам ориентироваться среди заданий, чтобы определить время или успешность его выполнения.
  2. toDelete – параметр, определяющий, следует ли удалять задание после его завершения.
  3. linked_server – имя сервера, на котором должен быть выполнен запрос.
  4. job_step_database – имя базы данных, к которой адресуется запрос.
  5. job_step_name – имя шага (“step”), который будет создан в задании (в данной реализации в каждом задании создается только один шаг).
  6. job_step_sql_script – это тот самый запрос (T-SQL), который должен быть выполнен в асинхронном режиме.
Остальные поля используются в процессе управления запросами.
В качестве простейшего примера приведем формирование пула запросов:
insert into [TaskList] (
      linked_server, job_name, toDelete, job_step_name,
      job_step_sql_script, job_step_database)
    select   '',
        'demo_UpdateCustomers',
        1,
        'step 01',
        N'update Customers set Region = ‘’N/A’’ where Region is null',
        'Northwind'
  ----------------------------------------------------------------------------
    insert into [TasksList] (
      linked_server, job_name, toDelete, job_step_name, job_step_sql_script,
      job_step_database)
    select   '',
        'demo_RemoveOrders',
        1,
        'step 01',
        N'delete from Orders where ShipCity is null',
        'Northwind'

  ----------------------------------------------------------------------------
    insert into [TasksList] (
      linked_server, job_name, toDelete, job_step_name, job_step_sql_script,
      job_step_database)
    select   '',
        'update_Employees',
        1,
        'step 01',
        N'update Employees set Region = ‘’London’’ where Country = ‘’UK’’',
        'Northwind'
Как вы видите, эти SQL-запросы подготавливают пул из трех запросов на текущем сервере в базе Northwind:
update Customers set Region = ’N/A’ where Region is null
delete from Orders where ShipCity is null
update Employees set Region = ‘London’ where Country = ‘UK’
Данные запросы довольно просты, но понятно, что вместо них могут использоваться сложные запросы или вызовы хранимых процедур, выполнение которых может занимать несколько часов.
Для запуска этих запросов в параллельном режиме используется процедура support_StartJobsAndWaitFor (Tools.dbo.support_StartJobsAndWaitFor), которая реализует всю логику управления пулом, запуска запросов и обработки результатов их работы.
Основные параметры процедуры:
CREATE PROCEDURE [dbo].[support_StartJobsAndWaitFor]
    @temporary_table_name  nvarchar(100),
    @SimultaneousJobsMaxCount  tinyint = 10
Здесь @temporary_table_name – имя таблицы-списка запросов, @SimultaneousJobsMaxCount – максимальное количество параллельно запущенных задач.
Примером вызова может быть следующий запрос:
execute support_StartJobsAndWaitFor 
  @temporary_table_name = 'Northwind.dbo.[TasksList]'
После запуска процедуры три подготовленных запроса будут выполнены в параллельном режиме:

Рисунок 5.
Все задания, созданные процедурой support_StartJobsAndWaitFor, будут запущены, и по их окончании процедура закончит свою работу.
Рассмотрим более сложный пример: допустим, требуется произвести вышеупомянутые t-sql-операции на двух настроенных серверах параллельно. Более того, допустим, что одна из выполняемых операций приведет к ошибке:

Рисунок 6.
Данная ситуация реализована в коде хранимой процедуры Linked_server_01.Article_Sample_Data.dbo.demo_StartJobsAndWaitFor.
Запустим ее на выполнение, предварительно открыв Job Activity Monitor на серверах. Мы должны увидеть следующее:
На linked_server_01 были созданы и выполнены следующие задания:

Рисунок 7.
На linked_server_02 также были созданы и выполнены следующие задания:

Рисунок 8.
После выполнения мы увидим сообщение об ошибке выполнения последнего задания на linked_server_02:

Рисунок 9.
Скрипт ниже реализует этот пример – он создает и запускает по три запроса на каждом из linked server-ов. При этом в одном из запросов содержится заведомо некорректный t-sql-код, сообщение об ошибке выполнения которого мы увидим после выполнения всех процессов:
--  ------------------------------------------------------------------------
  --  1.2  Формируем список job-ов
  --  ------------------------------------------------------------------------
    insert into [TaskList] (
      linked_server, job_name, toDelete, checkRun, job_step_name, 
      job_step_sql_script, job_step_database)
    select   '',
        'demo_UpdateCustomers',
        0,
        1,
        'step 01',
        N'update Customers set Region = ''N/A'' where Region is null',
        'Northwind'
  --  ------------------------------------------------------------------------
    insert into [TaskList] (
      linked_server, job_name, toDelete, checkRun, job_step_name, 
      job_step_sql_script, job_step_database)
    select   '',
        'demo_RemoveOrders',
        0,
        1,
        'step 01',
        N'delete from Orders where ShipCity is null',
        'Northwind'

  --  ------------------------------------------------------------------------
    insert into [TaskList] (
      linked_server, job_name, toDelete, checkRun, job_step_name,
      job_step_sql_script, job_step_database)
    select   '',
        'update_Employees',
        0,
        1,
        'step 01',
        N'update Employees set Region = ''London'' where Country = ''UK''',
        'Northwind'

  --  ------------------------------------------------------------------------
  --  !!!    --  Регистрация задачи на удаленном linked_server
    insert into [TaskList] (
      linked_server, job_name, toDelete, checkRun, job_step_name, 
      job_step_sql_script, job_step_database)
    select   'linked_server_02',
        'demo_UpdateCustomers',
        0,
        1,
        'step 01',
        N'update Customers set Region = ''N/A'' where Region is null',
        'Northwind'

    insert into [TaskList] (
      linked_server, job_name, toDelete, checkRun, job_step_name, 
      job_step_sql_script, job_step_database)
    select   'linked_server_02',
        'demo_RemoveOrders',
        0,
        1,
        'step 01',
        N'delete from Orders where ShipCity is null',
        'Northwind'


    insert into [TaskList] (
      linked_server, job_name, toDelete, checkRun, job_step_name, 
      job_step_sql_script, job_step_database)
    select   'linked_server_02',
      'update_Employees',
      0,
      1,
      'step 01',
      N'update Employees set Region = ''London'' where Country = ''UK''',
      'Northwind'
  --  Регистрация задачи на удаленном linked_server 
  --  с заведомо некоректным t-sql кодом
    insert into [TaskList] (
      linked_server, job_name, toDelete, checkRun, job_step_name,
      job_step_sql_script, job_step_database)
    select   'linked_server_02',
      'Incorrect democode',
      0,
      1,
      'step 01',
      N'incorrect t-sql code',
      'Northwind'

  --  ------------------------------------------------------------------------
  --  1.3  Запускаем подготовленные job-ы и ждем их завершения
  --  ------------------------------------------------------------------------
  --  executing and wait for
    execute Tools.dbo.support_StartJobsAndWaitFor 
      @temporary_table_name     = 'linked_server_01.Article_Sample_Data.dbo.[TaskList]',
       --  Максимальное количество одновременно работающих      @SimultaneousJobsMaxCount  = 03,
      --  timeOut между итерациями проверки статуса job-ов
      @delay_time      = '00:00:03',
      @delete_jobs      = 0
  

Комментарии к коду примера

Как указывалось выше, процедура Tools.dbo.support_StartJobsAndWaitFor выполняет всю работу по управлению пулом. В качестве ее параметров указывается количество одновременно запущенных задач и время между периодическими опросами работающих процессов (параметр @delay_time – что-то вроде Thread.Sleep (), на самом деле waitfor delay). Параметр @temporary_table_name – для данного примера мы указываем полный путь к таблице описания пула запросов, видимой как с linked_server_01, так и с linked_server_02 – поэтому здесь и уделено особое внимание описанию правильной настройки межсерверных связей на обоих серверах в самом начале статьи.
Если вы посмотрите свойства любого созданного задания, то увидите не тот скрипт, который поместили в таблицу [TaskList], а немного видоизмененный, например, такой:
declare @sql_script nvarchar(4000)
  set   @sql_script = '
    update   TaskList
    set   job_started = 1
    where   linked_server = ''linked_server_02'' and 
      job_name = ''demo_UpdateCustomers''
'
execute linked_server_01.Article_Sample_Data.dbo.sp_executesql @sql_script
update Customers set Region = 'N/A' where Region is null
Обновление флага job_started требуется для того, чтобы зафиксировать момент старта задания. Дело в том, что SQL Server не запускает задание на выполнение непосредственно в тот момент, когда получает соответствующую команду. Если в этот момент нагрузка на сервер будет достаточно высока или количество работающих заданий будет достаточно велико, то он отложит момент старта на неопределенное время – до тех пор, пока не посчитает текущую нагрузку приемлемой. Все это время задание будет находиться в состоянии ожидания – промежуточном состоянии между Not Running и Executing. Поэтому, в общем случае, не стоит ожидать от SQL Server запуска ваших заданий точно в то время, на которое они были запланированы. Однако требуется где-то зафиксировать момент начала выполнения задания, и только после этого начинать анализировать его состояние – в работе оно или уже закончило свое выполнение. Единственным возможным подходом представляется изменение скрипта самого задания – первым делом после старта задание само изменяет свой статус в соответствующей таблице.

Описание проблем при настройке DTC и возможные их решения

Сообщение об ошибке:
ПРЕДУПРЕЖДЕНИЕ
MSDTC on server '' is unavailable.
Возможные варианты разрешения проблемы:
Необходимо запустить службу msdtc. Для этого в командной строке нужно запустить dcomcnfg (Component services), в списке computers найти текущий, из контекстного меню вызвать start MS DTC.

Рисунок 10.
В некоторых ситуациях все равно не удается запустить службу msdtc. Тогда необходимо убедиться в том, что запущены следующие сервисы:
  1. Remote Procedure Call (RPC) Locator.
  2. Remote Procedure Call (RPC).
  3. Должен быть запущен COM+ System Application – вот с запуском этой службы могут возникнуть проблемы. После старта может быть выдано сообщение о том, что сервис не может стартовать. В таком случае необходимо просмотреть event log – если он содержит сообщение такого плана:
Event Type:  Error
Event Source:  COM+
Event Category:  (98)
Event ID:  4822
Date:    4/22/2007
Time:    1:08:54 PM
User:    N/A
Computer:  DUSHES-MOBILE
Description:
A condition has occurred that indicates this COM+ application is in an unstable state or is not functioning correctly. Assertion Failure: SUCCEEDED(hr)

Server Application ID: {02D4B3F1-FD88-11D1-960D-00805FC79235}
Server Application Instance ID:
{88E823F3-8336-4499-82BF-5670D772B9F7}
Server Application Name: System Application
The serious nature of this error has caused the process to terminate.
Error Code = 0x8000ffff : Catastrophic failure
COM+ Services Internals Information:
File: d:\qxp_slp\com\com1x\src\comsvcs\tracker\trksvr\trksvrimpl.cpp, Line: 3000
Comsvcs.dll file version: ENU 2001.12.4414.258 shp
то нужно поступить по инструкции:
1.  Click Start, click Run, type secpol.msc, and then click OK. 
2.  In the left pane, expand Local Policies, and then click User Rights Assignment.
3.  In the right pane, double-click Impersonate a client after authentication.
4.  Click Add User or Group.
5.  Type service in the Enter the object names to select box, and then click OK two times.
6.  Restart the computer.
1. Щелкнуть по «Start», выбрать пункт «Run», ввести команду «secpol.msc», и затем щелкнуть по «ОК» — откроется окно консоли «Local Security Settings».
2. В левой панели окна консоли развернуть ветку «Local Policies» и затем выбрать «User Rights Assignment».
3. В правой панели консоли двойным щелчком мыши вызвать диалог «Impersonate a client after authentication».
4. Щелкнуть по «Add User or Group».
5. Ввести в текстовое поле «the Enter the object names to select box» значение «service» и затем дважды нажать «OК», закрыв все открытые диалоги консоли «Local Security Settings».
6. Перезапустить компьютер.
Если же и после этого сервис COM+ System Application не запускается, тогда последнее, что можно сделать, это перерегистрировать следующие com-серверы:
regsvr32 comsvcs.dll
regsvr32 ole32.dll
regsvr32 oleaut32.dll
Если выдается сообщение об ошибке:
ПРЕДУПРЕЖДЕНИЕ
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[SQLSTATE 42000] (Error 7391) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
[SQLSTATE 01000] (Error 7300) [SQLSTATE 01000] (Error 7312). The step failed.
То, возможно, неправильно настроена Security Configuration у службы MSDTC. Для ее настройки нужно открыть Component Services, в списке Computers выбрать текущий, в контекстном меню выбрать properties, в открывшемся диалоге – вкладку MSDTC, и внести, например, такие настройки:

Рисунок 11.

Master.dbo.sysProcesses как альтернатива Activity monitor, недокументированные возможности

ПРЕДУПРЕЖДЕНИЕ
Нижеприведенный код будет работать на MSSQL 2000 Server только при условии установки Service Pack 3 и выше. Код является рабочим также и для MS SQL 2005.
Activity monitor из состава MS SQL Server 2005 и вкладка Current Activity в Enterprise manager из состава MS SQL Server 2000 являются очень полезными инструментами для поиска проблемных мест в текущей работе сервера. Они позволяют обнаружить проблемную сессию, причину возникновения проблемы и многое другое - например, заблокированную сессию, ресурс, из-за которого произошла блокировка, список текущих сессий, их состояния и т.д. О работе этих инструментов написано достаточно, и не имеет смысла описывать здесь их функциональность. Однако часто приходится видеть процесс, очень долго выполняющий какой-либо пакет SQL-команд – и хотелось бы иметь возможность узнать, а какая именно команда выполняется в текущий момент времени.
Оказывается, все это можно увидеть и получить с помощью master.dbo.sysprocesses.
Для следующей демонстрации стоит открыть две сессии, необязательно на тех базах данных, скрипты для создания которых идут вместе с данной статьей. В первой сессии выполним следующее:
select  @@spid
waitfor delay '00:10'
Сначала запустим только select @@spid, чтобы увидеть spid текущей сессии, потом можно запустить весь скрипт – для данной задачи нам нужно запомнить только идентификатор текущей сессии (в моем случае это было значение 57).
Во второй сессии выполним следующее:
select  spid,
  cpu,
  stmt_start,
  stmt_end, 
  sql_handle,  *
from  master..sysprocesses processes (nolock)
where  dbid  = db_id() 
order  by processes.cpu desc
Получим результат, аналогичный показанному ниже:

Рисунок 12.
Обратите внимание на поля sql_handle, stmt_start, stmt_end. Зная sql_handle, можно получить t-sql-код, который выполняется в текущей сессии, соответственно поля stmt_start и stmt_end – начальная и конечная позиция текущей команды в batch. Поскольку используется unicode, то придется произвести дополнительно еще и деление на 2 для получения реальной позиции в тексте.
Пока в первой сессии выполняется скрипт waitfor, во второй запустим следующее (после предыдущего select):
Declare  @sql_handle  varbinary(20),
    @sql_text  nvarchar(4000),
    @stmt_start  int,
    @stmt_end  int

  select  @sql_handle  = sql_handle,
    @stmt_start  = stmt_start,
    @stmt_end  = stmt_end
  from  master..sysprocesses (nolock)
  where  spid = 57

  select  *
  from  ::fn_get_sql(@sql_handle)

  select  @sql_text = [text]
  from  ::fn_get_sql(@sql_handle)

  select  @sql_text as all_executed_code,
    substring(@sql_text, @stmt_start/ 2, 
      case when @stmt_end > 0 
      then @stmt_end /2 
      else len(@sql_text) end) 
    as current_executed_code
Здесь 57 – это идентификатор первой сессии. У вас это значение будет другим.
Здесь использована недокументированная функция ::fn_get_sql(varbinary sql handle), которая по полученному sql_handle сессии возвращает весь выполняющийся t-sql код (собственно, это то, что мы можем увидеть в activity monitor, щелкнув по интересующей нас сессии). Результат выполнения:

Рисунок 13.
Во второй таблице показан весь пакет команд, выполняемых в текущей сессии, а также текущая инструкция waitfor delay (для получения которой использованы поля stmt_start, stmt_end). Именно значения полей stmt_start и stmt_end и дают нам возможность из всего пакета команд увидеть только те инструкции, которые реально выполняются в текущий момент. Данные значения содержат смещения начала и конца текущего кода в байтах.
В коде:
select  @sql_text as all_executed_code,
  substring(
    @sql_text, @stmt_start/ 2, 
    case when @stmt_end > 0 
    then @stmt_end /2 
    else len(@sql_text) end) 
  as current_executed_code
для получения кода текущей команды при помощи substring мы делим значения полей stmt_start|end на 2 намеренно, чтобы получить позиции начала и конца кода в строке всего пакета команда в формате unicode (в котором, как известно, для хранения символа используется два байта).

Динамические скрипты, вызов на linked_server

На форумах часто приходится видеть примерно такие вопросы: «Как получить данные из таблицы, имя которой зависит от ряда причин?». Ответом, как правило, является совет использовать динамические скрипты (хотя, в зависимости от задачи, возникают и варианты использования разветвления кода через if). В своей практике мы тоже часто используем динамические скрипты, например, в таких случаях, когда условие отбора записей where неизвестно и зависит от определенной логики, для описанного случая с именем таблицы, и прочих. Но отдельно нужно выделить те случаи, когда скрипт необходимо запустить на другом сервере, имя которого на момент написания скрипта неизвестно или может меняться в зависимости от каких-либо условий.
СОВЕТ
Для запуска динамических скриптов один из авторов использует вызов системной процедуры sp_executesql, у которой, по сравнению с инструкцией execute(N’string’), синтаксис гораздо богаче и позволяет параметризовать скрипт. Это удобнее для восприятия (хотя и имеется ограничение на максимальную длину скрипта в 4000 символов, в отличие от execute(N’string’), позволяющей произвести конкатенацию нескольких строк максимальной длиной по 4000 символов – речь идет об nvarchar type).
Поясним на примере:
-- таблица, используемая для теста динамического скрипта
if ( isnull(object_id('tempdb..#test'), 0) > 0 )
drop table #test
create table #test ( id int identity,
[value] int
)
insert into #test ([value])
select 1
union
select 2
union
select 3
union
select 4
union
select 5

declare @sql_script nvarchar(4000),
@where_value int -- значение для отбора записей по полю [value]
set @where_value = 3

-- использование dynamic script через execute (N'string')
set @sql_script =
N'
select *
from #test
where [value] >= ' + cast(@where_value as nvarchar(10))
execute(@sql_script)
-- использование dynamic script через sp_executesql
set @sql_script =
N'
select *
from #test
where [value] >= @value
'
execute sp_executesql @sql_script, N'@value int', @value = @where_value
Если в случае использования execute(N’string’) мы сформировали строку, где для условия where было подставлено определенное значение посредством приведения числового значения переменной @where_value к типу данных nvarchar(), то во втором случае мы явно указали объявление переменной @value, указали ее тип, и уже затем использовали ее в логике самого запроса, проинициализировав при вызове dynamic script переменную @value значением @where_value. Возможно, данный пример не является показательным, но во втором случае мы имеем явное указание параметров запроса, видим их использование в самом запросе, видим их инициализацию, аналогично объявлению хранимой процедуры и ее вызову.
Итак, для случая, когда требуется запустить скрипт на указанном linked_server, имя которого мы получаем в процессе выполнения кода, у нас есть переменная с именем этого сервера. В таком случае динамический скрипт в зависимости от сценария будет выглядеть приблизительно так:
declare @destination_linked_server nvarchar(100),
    @source_linked_server nvarchar(100),
    @execute_script       nvarchar(4000),
    @sql_script           nvarchar(4000)

  set @destination_linked_server = 'linked_server_02'
  set @source_linked_server      = 'linked_server_01'

--  1.
--  на указанном сервере нужно просто запустить хранимую процедуру ...
--  для данного случая подразумеваем, что наша БД указана, 
--  путь это будет TempDB, а вызвана будет просто 
--  системная процедура (хотя можно было подставить имя своей БД 
--  и имя конкретной процедуры)
  set  @execute_script = 'execute  ' + @destination_linked_server + '.TempDB.dbo.sp_helpserver'
  execute  sp_executesql @execute_script
--  если проанализировать профайлером, то такой вызов 
--  ничем не отличается от прямого вызова
--  execute  linked_server_02.TempDB.dbo.sp_helpserver

--  2.
--  Во втором случае рассматривается ситуация, когда имя linked–сервера
--  неизвестно на момент вызова скрипта, более того, 
--  сам скрипт должен работать на linked–сервере 
--  (скажем, затянуть данные с другого сервера, может потребоваться 
--  для реализации метода pull для передачи данных между серверами; 
--  о разнице между pull и push говорится в разделе 
--  "Как вернуть данные из temporary table чужой сессии")

  set @sql_script = 
  '
    select *
    from ' + @source_linked_server + '.master.dbo.sysservers
  '
  set @execute_script = 
    'execute  ' + @destination_linked_server 
    + '.TempDB.dbo.sp_executesql @outer_sql_script'
  execute sp_executesql @execute_script, 
    N'@outer_sql_script nvarchar(4000)', @outer_sql_script = @sql_script
Результат выполнения должен быть приблизительно таким:

Рисунок 14.
В приведенном примере рассмотрены два случая. В первом осуществляется вызов хранимой процедуры для известной БД с указанного через переменную @destination_linked_server linked-сервера.
Во втором случае имеется текст запроса, который должен быть вызван на стороне указанного сервера. Т.е. мы имеем ситуацию, когда один динамический скрипт вызывает другой.
Схематично это можно изобразить так:

Рисунок 15.
В данном случае мы сначала формируем скрипт для вызова sp_executesql на linked-сервере, т.е. в данном контексте это будет внутренний скрипт:
set @execute_script = 
  'execute ' + @destination_linked_server + 
  '.TempDB.dbo.sp_executesql @outer_sql_script'
Также укажем, что внутренний скрипт должен запустить @outer_sql_script, передаваемый снаружи. То есть @outer_sql_script является просто переменной, которую мы объявим во внешнем скрипте и проинициализируем необходимым значением (т.е. практически то же самое, что и выше, при использовании переменной @value). Наконец, во внешнем скрипте:
execute  sp_executesql @execute_script, N'@outer_sql_script nvarchar(4000)', @outer_sql_script = @sql_script
мы объявляем переменную @outer_sql_script, которая будет использована в скрипте @execute_script (во внутреннем), и инициализируем ее значением @sql_script.
Если проанализировать работу такого кода профайлером, можно убедиться, что запуск скрипта
select  *
  from  linked_server_01.master.dbo.sysservers
на удаленном сервере (@destination_linked_server) ничем не отличается от нашего вызова.

Проверка linked_server на loopback

Иногда возникает необходимость проверить, ссылается linked_server сам на себя или нет (является ли он loopback-сервером) – хотя бы для того, чтобы избежать проблем, описанных в BOL (только при использовании на серверах MS SQL 2000, проблема loopback linked servers решена на MS SQL 2005).
ПРЕДУПРЕЖДЕНИЕ
Loopback-серверы нельзя использовать в распределенной транзакции.
Loopback linked servers cannot be used in a distributed transaction. Attempting a distributed query against a loopback linked server from within a distributed transaction causes an error:

Описание реализации

Собственно, алгоритм достаточно прост. Ищем указанный сервер в списке master.dbo.sysservers, анализируем DataSource на соответствие хосту текущего сервера (host_name()) или IP-адресу (IP-адреса текущего сервера получаем, запустив через xp_cmdShell команду ipconfig) – если есть совпадение, то считаем что linked_server ссылается сам на себя. Собственно, данный алгоритм и реализован в коде прилагаемой хранимой процедуры Tools.dbo.support_CheckLinkedServer.
Для демонстрации запустим следующий скрипт на ранее настроенном сервере, где размещена база данных Tools:
declare  @isRemote  bit
  declare  @isExists  bit
  

--  loopback server
  execute  linked_server_01.Tools.dbo.[support_CheckLinkedServer]
            @linked_server_name  = 'linked_server_01',
            @isRemote    = @isRemote output,
            @isExists    = @isExists output

  select  @isRemote,  @isExists
--  not loopback
  execute  linked_server_01.Tools.dbo.[support_CheckLinkedServer]
            @linked_server_name  = 'linked_server_02',
            @isRemote    = @isRemote output,
            @isExists    = @isExists output

  select  @isRemote,  @isExists
--  linked server not exists
  execute  linked_server_01.Tools.dbo.[support_CheckLinkedServer]
            @linked_server_name  = 'not_exists_linked_server',
            @isRemote    = @isRemote output,
            @isExists    = @isExists output

  select  @isRemote,  @isExists
Результаты выполнения должны быть следующими:

Рисунок 16.
Т.е. первый сервер существует, но ссылается сам на себя (isRemote = 0, сервер не удаленный), второй сервер реально существует и является удаленным, третий сервер заведомо не существует – isЕxists = 0.

Как вернуть данные из temporary table чужой сессии (только mssql2000)

ПРЕДУПРЕЖДЕНИЕ
Все нижеописанное относится только к MSSQL 2000, для MSSQL 2005 описанный подход работать не будет.
Известно, что временная таблица (речь идет о локальных временных таблицах) живет только в пределах одной сессии, заканчивается сессия – удаляется временная таблица. Доступ к временной таблице ограничен рамками текущей сессии. Но существует прием, с помощью которого из одной сессии можно получить данные из временной таблицы другой сессии. В общем-то, такой прием был не один раз описан на www.sql.ru.
Итак, для демонстрации сделаем следующее – в той сессии, где мы собираемся создать тестовую временную таблицу, запустим следующий код:
select @@spid
  create table #test_table (id int identity,
    test varchar(100))
  insert into  #test_table
  select 'string_01'
  union  all
  select 'string_02'

  select *
    from #test_table

  select  object_id('tempdb..#test_table')
Mы должны увидеть @@spid текущей сессии, выборку из двух записей и ID временной таблицы. Зная, что временные таблицы размещаются в базе данных tempdb, мы можем также получить object_id такой временной таблицы. Результат выполнения данного скрипта:

Рисунок 17.
Откроем другую сессию. Зная object_ID временной таблицы из другой сессии, можно увидеть ее реальное имя. Для этого выполним следующий скрипт (вместо id подставьте свое полученное значение):
select *
  from tempdb..sysobjects(nolock) 
  where id = 761105802
Результат:

Рисунок 18.
Т.е. мы получили реальное имя временной таблицы, используемой в другой сессии. Но попытка использования такого имени для каких-либо запросов ни к чему не приведет. Запрос вида
select  *
from  tempdb..[#test_table_________________________________________________________________________________________________________000000000048]
приведет к сообщению об ошибке:
ПРЕДУПРЕЖДЕНИЕ
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#test_table_________________________________________________________________________________________________________000000000048'.
Теперь мы дошли до самой сути. Чтобы получить данные из такой таблицы, нужно обновить ее имя в системном каталоге, дав новое уникальное имя согласно правилам именования объектов в MSSQL. Т.е. для примера можно сделать следующее:
declare @table_name varchar(100)
set @table_name = 'temp_' + cast(newid() as varchar(36))
update tempdb..sysobjects
set name = @table_name
where id = 761105802
Здесь также может возникнуть проблема, а именно, можно получить следующее сообщение об ошибке:
ПРЕДУПРЕЖДЕНИЕ
Msg 259, Level 16, State 2, Line 3
Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.
Это значит, что мы не можем править содержимое системного каталога. Чтобы все-таки получить такую возможность, нужно правильно настроить сервер. Запустим следующий скрипт:
execute sp_configure 'allow updates', 1
reconfigure with override
execute sp_configure
Результат будет приблизительно таким:

Рисунок 19.
Нас интересует настройка сервера allow updates. Данная настройка позволяет править системный каталог, в частности, позволит произвести правку имени временной таблицы в tempdb..sysobjects.
Запустим скрипт:
declare @table_name varchar(100)
set @table_name = 'temp_' + cast(newid() as varchar(36))
update tempdb..sysobjects
set name = @table_name
where id = 761105802

select *
  from tempdb..sysobjects(nolock)
  where id = 761105802
Данный скрипт вернет новое имя таблицы. В моем случае это было значение [temp_8A04E767-299B-48DE-A4A8-B17F70BCCEF5]. Теперь, находясь в этой же сессии, выполнив следующий скрипт, подставив новое имя временной таблицы, мы получим реальные данные из временной таблицы из другой сессии:
select *
  from tempdb..[temp_8A04E767-299B-48DE-A4A8-B17F70BCCEF5]
Результат выполнения должен быть таким:

Рисунок 20.
Остается добавить, что изменение имени таблицы в tempdb..sysobjects никоим образом не повлияет на работоспособность запросов к исходной временной таблице в первой сессии. В этом легко убедиться, запустив любую из DML-инструкций к таблице.
ПРИМЕЧАНИЕ
В начале данной темы я упомянул, что мне приходится использовать такой подход для реализации передачи данных между linked-серверами.
Объясню суть возникшей передо мной в свое время проблемы и выбранное для нее решение.
Проблема: есть множество процедур, производящих определенные предварительные расчеты статистики в нашей системе. Есть необходимость пересылать полученные данные на другой сервер, который, собственно, не отвечает за предварительные расчеты, но отвечает за отображение данной статистики, т.е. на указанном сервере находятся таблицы-приемники конечных результатов. Объем полученных данных, как правило, большой. Существует два варианта передачи – условно их можно назвать pull и push.
При первом варианте (pull) данные затягиваются с сервера-источника на сервер-приемник данных скриптом, который выполняется на самом сервере-приемнике данных. То есть если бы у нас сервером-источником данных являлся linked_server_01, а сервером-приемником данных - linked_server_02, то данный вариант выглядел бы на t-sql так:
-- скрипт выполняется на Linked_server_02:
Insert into destination_table
select *
from linked_server_01.catalog.owner.source_table
В случае push – данные не забираются с сервера-источника данных, а заталкиваются на сервер-приемник данных, то есть:
-- скрипт выполняется на Linked_server_01:
Insert into linked_server_02.catalog.owner.destination_table
select *
from source_table
На первый взгляд, оба варианта равноценны, но на самом деле разница очень и очень существенная. Можно сразу сказать, что первый вариант на порядки производительнее второго, так как процесс переноса данных во втором случае происходит по сути своей построково, в чем легко убедиться путем профилирования запросов описанных типов. О разнице в использовании обоих подходов можно подробнее узнать из статьи Михаила Смирнова по следующей ссылке:
http://www.sql.ru/subscribe/2007/354.shtml#20
Полученные в результате выполнения хранимых процедур (по расчету статистики на сервере-источнике) данные сохранялись в постоянных таблицах. Затем эти данные забирались посредством хранимой процедуры, запускаемой на сервере-приемнике, которая собственно и выполняла сохранение данных методом pull, т.е. данные именно забирались с сервера-источника. Соответственно, для каждого вида статистики имелась отдельная постоянная таблица на локальном сервере (источнике) и отдельная хранимая процедура на удаленном (приемнике). Лично я противник дублирования кода и создания всякого рода дополнительных объектов в БД, так как это приводит к дополнительной нагрузке на сопровождение таких объектов – а именно, в моем случае, поддержка соответствия структуры таких таблиц как процедурам передачи данных, так и процедурам расчета.
Решение: Именно поэтому я решил отказаться от использования постоянных таблиц и перешел на использование единой процедуры передачи данных, которой передаю object_id() временной таблицы с результатами, полученными с сервера-источника данных. Процедура передачи данных описанным выше методом обновляет имя временной таблицы в tempdb сервера-источника данных и осуществляет собственно выборку данных. Не скажу, что на все 100% уверен в правильности выбора такого решения, но за полгода использования проблем не возникало. Однако при переходе на MS SQL Server 2005 возникнет проблема, так как описанное выше решение работать уже не будет. – прим. Андрея Смирнова

Возврат курсора из динамического скрипта

Следует иметь в виду, что для корректной работы из динамического скрипта можно вернуть только открытый курсор, что и демонстрируется на примере:
declare @sql_script nvarchar(4000),
  @outer_cursor cursor
set @sql_script = 
'
  set @return_cursor = cursor local forward_only for
          select ''string_01''
          union all
          select ''string_02''
          union all
          select ''string_03''
  open @return_cursor
'
execute sp_executesql @sql_script, N'@return_cursor cursor output', 
  @return_cursor = @outer_cursor output
fetch next from @outer_cursor
while (@@fetch_status = 0)
  fetch next from @outer_cursor

close      @outer_cursor
deallocate @outer_cursor

Заключение.

Все приведенные в этой статье техники, механизмы и запросы были использованы в реальных проектах и зарекомендовали себя. Авторы с радостью рассмотрят любые комментарии и вопросы по тематике статьи.


Мой сайт - www.msmirnov.ru

среда, 20 июня 2007 г. - www.msmirnov.ru

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

Здесь я привожу копию своей статьи "Практические рекомендации по организации службы поддержки в малых и средних проектах", опубликованной на сайте GotDotNet.ru: (http://www.gotdotnet.ru/LearnDotNet/Misc/481321.aspx)


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


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


Введение

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

Работа службы

Итак, как я уже упомянул выше, для эффективной работы служба технической поддержки она должна быть многоуровневой. Управляя в данный момент проектом среднего объема (10-20 человек в команде проекта и несколько сотен клиентов по всему миру), я использовал разделение службы поддержки на три уровня. Вы можете видеть эти уровни на диаграмме.

Условные обозначения
- Клиенты
- Региональные офисы поддержки
- Центральный офис поддержки
- Команда разработки
На первом уровне, самом близком к клиентам, находятся региональные офисы поддержки, обслуживающие своих локальных клиентов. На втором находится центральный офис поддержки и на третьем, самом последнем уровне, находится команда разработки. Причем, центральный офис поддержки находится в непосредственной близости от команды разработки, составляя с ней единое целое.
Рассмотрим вопросы, связанные с функционированием каждого из этих уровней.

Региональный уровень.

Первый (региональный) уровень необходим по нескольким причинам:
  1. Сотрудники региональных офисов хорошо знакомы с местной спецификой работы, культурными и языковыми особенностями и т.п. Это является очень важным моментом, особенно когда региональные офисы находятся в разных странах или обеспечивают поддержку на разных языках. Плохое знание местных нюансов делового общения, которое могут демонстрировать сотрудники центрального офиса поддержки, может отрицательно сказаться на имидже продукта и компании.
  2. Сотрудники регионального офиса работают в том же часовом поясе, что и клиенты. В этом случае вам не придется держать круглосуточный штат сотрудников в центральном офисе.
  3. Сотрудники регионального офиса обладают возможностью выезда или длительных телефонных переговоров, что часто бывает невозможно для сотрудников центрального офиса.
  4. Сотрудники регионального офиса способны быстро отреагировать на типичные затруднения клиентов, которые, в большинстве своем, связаны с вопросами по использованию продукта, а не с проблемами в его работе. Тем самым региональный офис позволяет существенно разгрузить работу центрального офиса, взяв на себя основной поток простых вопросов.
  5. Клиентам удобней работать с местной службой поддержки, так как в этом случае они ощущают большую заботу о себе со стороны компании, особенно если они имеют личного регионального менеджера.
Таким образом, региональные офисы поддержки берут на себя основную нагрузку по работе с пользователями. Они отвечают на основные вопросы, разъясняют особенности использования продукта и т.д.
Однако, в работе региональных офисов существует ряд сложностей, на которые необходимо обратить внимание.
  1. Квалификация сотрудников. Это проблема, пожалуй, основная. Не имея соответствующей подготовки, местные сотрудники могут сами быть недостаточно осведомлены о продукте, могут не знать всех его особенностей. Это может приводить к тому, что они будут давать неверные ответы клиентами или же просто служить передаточным звеном между клиентов и центральным офисов поддержки. И то и другое отрицательным образом сказывается на процессе поддержки. В первом случае клиенты получают неверную информацию, во втором случае излишне нагружается центральный офис, а региональный офис не выполняет свою основную функцию. Для решения этой проблемы я рекомендую предварительное обучение и стажировку сотрудников региональных офисов в центральном офисе, даже если они находятся в разных странах.
  2. Оторванность от процесса разработки. Поскольку в любом продукте происходят изменения, а клиенты получают обновления этих продуктов, может создаться такая ситуация, при которой сотрудники региональной службы будут не в курсе последних обновлений в продукте. Для решения такой проблемы необходимо уведомление всех сотрудников всех офисов поддержки списком изменений при каждом новом выпуске продукта. При этом, чем более детальной будет информация об обновлении, тем лучше.
  3. Неспособность решить сложные вопросы. Эта проблема перекликается с проблемой квалификации, но имеет свою особенность. Дело в том, что вопросы, которые подаются на рассмотрение, могут быть связаны со сбоями в работе продукта, которые региональный офис решить не в состоянии. В этом случае, вопрос поступает на рассмотрение в центральный офис поддержки, а затем, возможно, в команду разработчиков, что увеличивает время решения проблемы. Однако, если продукт достаточно стабилен, то проигрыш по времени в таких случаях обычно не значителен.
В случае успешного решения всех этих проблем, работа регионального офиса достаточно эффективна и приносит ощутимый эффект. Есть еще несколько вопросов, связанных с работой региональной службы поддержки, на которые я бы хотел обратить внимание.
  1. Доступ к продукту. Очень часто клиенты не в состоянии четко объяснить проблему или ситуацию, которая у них происходит. Для того, чтобы понять, с чем именно столкнулся пользователь, для службы поддержки будет очень эффективно, если она будет иметь доступ к продукту и данным клиента. Конечно, здесь возникает вопрос конфиденциальности клиентских данных, но если это не является проблемой или если этот вопрос решен, например, юридически, то предоставление доступа для службы поддержки весьма оправдано. Например, если речь идет о предоставляемой интернет-системе, то служба поддержки должна иметь возможность получить в нее доступ и проанализировать какие действия пользователь совершал и в чем состоит его затруднение. Это также необходимо и для того, чтобы предоставить полные объем информации в центральный офис, если возникнет такая необходимость. Центральный офис и команда разработки могут ощущать такой же недостаток информации, описывающей проблему, поэтому, имея подобный доступ, региональные сотрудники смогут собрать такую информацию.
  2. Протоколирование всех действий и изменений. Для работы службы поддержки будет очень полезно, если продукт будет обладать возможностью протоколирования всех действий и всех изменений, совершаемых пользователями, либо самими сотрудниками службы. Особенно это важно, когда продукт используется для управления какими-либо данными, содержащимися у клиента. Кроме того, если продукт предоставляет возможность автоматизированного управления такими данными, то все эти изменения, а также их причины, также должны быть запротоколированы. Наличие подобных протоколов позволяет решить сразу несколько проблем:
  • Сотрудники службы поддержки могут точнее проанализировать какие именно действия совершал пользователь до того, как у него возникла некоторая ситуация. Если продукт предоставляет возможность автоматизированного управления пользовательскими данными, то такие протоколы смогут показать изменения сделанные в автоматическом режиме.
  • Протоколы могут использоваться в качестве доказательства того, что какие-либо изменения были совершены самим пользователем, либо же продуктом. Периодически возникает ситуация, когда необходимо выяснить, кто именно внес те или иные изменения и почему. Такие протоколы позволяют решить эту проблему.
  • Протоколы могут дать возможность восстановления ошибочно измененных данных, если такие изменения были внесены пользователями или продуктом.
  1. Ведение базы запросов. Весьма полезно, если в региональной службе поддержки будет организована база задаваемых вопросов. Такая база позволит сохранить информацию о том, какие вопросы задавались различными клиентами и какие ответы были на них получены. Здесь также будет известно кто именно отвечал на вопрос и сколько времени это потребовало. В последствии эта информация может служить для анализа того, сколько и какие именно вопросы задает каждый клиент, какие вопросы обрабатывает каждый специалист службы, оценить общий объем и эффективность работы офиса и т.п. Также эта база может использоваться для списка часто задаваемых вопросов или для анализа удобства работы с продуктом и качества продукта. Идеально, если база вопросов будет общей для всех офисов поддержки всех уровней.

Центральный офис.

Центральный офис является промежуточным звеном между региональными офисами и командой проекта. Этот офис необходим для решения следующих задач:
  1. Оградить команду проекта от потока вопросов региональных офисов. Не смотря на то, что вопросы, поступающие от региональных офисов, могут требовать непосредственного вмешательства команды разработки, практика оказывает, что большинство из них все-таки могут быть решены на уровне поддержки. Такие вопросы могут быть связаны, например, с особенностями внутренних алгоритмов работы продукта.
  2. Упорядочить поток вопросов, которые все же требуют участия разработчиков. При наличии непосредственного доступа регионального уровня поддержки к команде разработки есть риск того, что команда разработки будет вынуждена постоянно отвлекаться на решение их вопросов, что отрицательно скажется на производственном процессе. Для решения такой проблемы предназначен центральный офис, который организует упорядоченный процесс обращения к команде проекта.
  3. Работа с особыми клиентами. Некоторые клиенты, которые обладают особыми привилегиями, могут иметь доступ непосредственно к центральному офису поддержки, минуя региональный уровень. Это может быть связано, например, с выполнением ряда дополнительных задач непосредственно для данного клиента. Такой доступ позволит им быстрее получать ответы на свои специфические вопросы, так как центральный офис находится в непосредственной близости от команды разработчиков. Однако, в общем случае, такого подхода стоит избегать.
  4. Подготовка и рассылка описаний обновлений. При каждом выпуске обновлений продукта, центральный офис поддержки должен подготавливать и рассылать во все региональные офисы подробное описание всех изменений, проведенных в продукте. Он также должен оказывать консультации сотрудникам региональных офисов и особо важных клиентов по сути проведенных изменений.
Так же, как и офис регионального уровня, центральный офис должен обладать централизованной базой вопросов и проводить ее анализ. Кроме того, центральный офис должен иметь доступ к документам производственного процесса команды разработки – к базе ошибок, планам проекта, планам выпусков и т.п. Это позволит им наладить эффективную работу с командой разработчиков.
Проблемы, которые могут возникать в данном офисе, в большинстве своем аналогичны проблемам регионального уровня – это квалификация, излишнее вовлечение команды разработки, оторванность от процесса разработки и т.п. Однако, в общем случае, эти проблемы меньше проявляются, так как, еще раз повторюсь, этот отдел находится в непосредственной близости от команды разработки.

Команда проекта.

Команда проекта, насколько это возможно, должна быть ограничена от общения с клиентами по поводу возникающих у них вопросов. В большинстве случаев клиентам нравится иметь непосредственный доступ к команде разработки, так как это дает им возможность влиять на разрабатываемую функциональность и быстро получать ответы по их конкретным ситуациям. Однако, несмотря на это, такой способ коммуникаций отвлекает разработчиков от их основных задач и крайне отрицательно сказывается на процессе разработки. Поэтому, команда разработки должна проявить некоторую твердость в этом вопросе и даже при поступлении вопросов от важных клиентов не принимать их к рассмотрению, а направлять в соответствующий офис поддержки.
Задачи, которые должна выполнять команда проекта для обеспечения процесса поддержки, следующие:
  1. Предоставить центральному офису доступ к документам, позволяющим сформировать описание изменений, протекающих в проекте.
  2. Обеспечить уведомление сотрудников центрального офиса поддержки о результатах решения вопросов, поступивших от них.
  3. Обеспечить центральному офису помощь в решении тех вопросов, которые требуют вмешательства разработчиков.

Обобщение

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

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

Я с радостью рассмотрю любые комментарии и вопросы по данной тематике.
Мои координаты доступны на сайте www.msmirnov.ru
Михаил Смирнов Руководитель проектов.
Мой сайт - www.msmirnov.ru

пятница, 25 мая 2007 г. - www.msmirnov.ru

Использование представлений для сокрытия промежуточных данных в Microsoft SQL Server

Здесь я привожу копию моей статьи "Использование представлений для сокрытия промежуточных данных в Microsoft SQL Server", опубликованной в рассылке MS SQL Server - дело тонкое #359 от 25.05.2007 г (http://www.sql.ru/subscribe/2007/359.shtml#20)

Использование представлений для сокрытия промежуточных данных в Microsoft SQL Server.

Введение
При разработке различного рода коммерческих баз данных довольно часто встает задача обновления заранее просчитанных итоговых данных. Чаще всего это требуется для предоставления различного рода отчетности. Не всегда для этого используется технология OLAP, и поэтому в этой статье я приведу описание приема, который позволяет организовать незаметное для пользователя обновление таких данных.
Для начала сформулируем проблему, которую должен решить данный механизм. Проще всего сделать это на примере. Предположим, что используя базу Northwind, нам требуется предоставлять отчетность о кол-ве и объемах продаж за любой день по любому товару. Мы можем сделать это, создав View следующего вида:

CREATE VIEW [Sales by Product]
AS
SELECT     Orders.OrderDate, [Order Details].ProductID, 
      SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, 
      SUM([Order Details].Quantity) AS Quantity
FROM         [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID
GROUP BY Orders.OrderDate, [Order Details].ProductID
Однако, если в исходных таблицах Orders или Order Details будет находится больше количество записей (сотни миллионов), то выборка из такого view будет очень медленной. Быстро получить данные из него будет невозможно.
Возможным решением проблемы могла бы быть замена view на таблицу с периодически обновляемыми данными:

CREATE TABLE [Sales by Product] (
 OrderDate datetime NOT NULL ,
 ProductID int NOT NULL ,
 Amount money NOT NULL ,
 Quantity int NOT NULL 
)
Имея возможность время от времени обновлять такую таблицу на основании данных из таблиц заказов мы могли бы предоставить пользователям отчетную информацию в удобном для них виде. На такой таблице можно было бы создать индексы, которые могут позволить пользователям очень б ыстро осуществлять запрос данных.
Например:

CREATE CLUSTERED
  INDEX [Index1] ON [Sales by Product] (OrderDate, ProductID)
Для того чтобы обновить информацию в такой таблице достаточно было бы просто очистить ее и выполнить запрос на ее заполнение.

DELETE FROM [Sales by Product]

INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity)
SELECT     Orders.OrderDate, [Order Details].ProductID, 
          SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, 
        SUM([Order Details].Quantity) AS Quantity
FROM       [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID
GROUP BY Orders.OrderDate, [Order Details].ProductID
Однако, в связи с тем, что в таблице Orders у нас находится очень много заказов, возникают две проблемы, которые не позволят нам обновлять эту таблицу в рабочее время:
Первая проблема: Очистка и обновление такой таблицы может происходить очень долго. В реальной практике расчет итоговых значений для такой таблицы может быть намного сложнее чем в данном примере. В нем могут участвовать множество таблиц с большим количеством записей в каждой, он может содержать сложные вычисления и т.п, так что он, например, может выполняться несколько часов.
Вторая проблема: В течении всего этого времени пользователи не смогу видеть данные о продажах, так как они были удалены первым запросом.
Описанный здесь механизм позволяет решить обе эти проблемы.

Описание механизма
Для решения первой проблемы нужно принять во внимание, что далеко не все строки таблицы Sales by Product нуждаются в обновлении. Заказы редко вписываются «задним числом», поэтому обычно в обновлении нуждается несколько последних дней и очень редко данные в далеком прошлом. Поэтому, для ускорения процесса расчета можно создать дополнительную вспомогательную таблицу, которая будет содержать даты, для которых требуется пересчет.

CREATE TABLE DatesForUpdates (
 UpdateDate datetime NOT NULL 
)
Такая таблица может заполняться при создании либо обновлении заказов. При наличии такой вспомогательной таблицы процесс обновления Sales by Product может выглядеть так:

DELETE [Sales by Product]
FROM [Sales by Product], DatesForUpdates
WHERE [Sales by Product].OrderDate = DatesForUpdates.UpdateDate


INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity)
SELECT     Orders.OrderDate, [Order Details].ProductID, 
          SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, 
        SUM([Order Details].Quantity) AS Quantity
FROM       [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID
   INNER JOIN DatesForUpdates on Orders.OrderDate = DatesForUpdates.UpdateDate
GROUP BY Orders.OrderDate, [Order Details].ProductID

DELETE FROM DatesForUpdates
Таким образом мы накладываем фильтр на обновляемые данные, существенным образом облегчая запрос.
Однако, это не решает второй проблемы – для конечного пользователя данные по прежнему пропадают на некоторое время.
Казалось бы, решением проблемы может быть отказ от удаления данных перед их вставкой. Мы могли бы подготовить временную таблицу с вновь рассчитанным набором данным, затем обновить строки, которые уже существуют в Sales by Product, вставить новые строки и удалить лишние (на тот случай если часть заказов была удалена). Однако, это не решит данную проблему, потому что даже в этом случае существует вероятность того, что пользователь получит некорректные данные, если, например, выполнит запрос на выборку между операциями обновления и удаления. Использование транзакции в этом случае тоже не является выходом, так как данные в этом случае будут заблокированы и недоступны для пользователя до момента завершения транзакции.
Для решения этой проблемы потребуется провести некоторые преобразования, которые как раз и отражают суть рассматриваемого механизма.
Во-первых: в таблицу Sales by Product добавим поле Version, которое означает версию строки таблицы и может содержать следующие значения:
    1 – строка, доступная для пользователя 2 – строка, содержащая новые данные. Пользователю не доступна. 3 – строка, содержащая устаревшие данные. Пользователю также не доступна
Во-вторых: создадим view

CREATE VIEW dbo.[Report by Product]
AS
SELECT     *
FROM        [Sales by Product]
WHERE     Version = 1
Именно с этим представлением Report by Product, а не с таблицей Sales by Product будет теперь работать пользователь. Видим, что это представление фильтрует строки со всеми номерами строк кроме 1, скрывая таким образом промежуточные данные.
Процесс обновления, однако, по-прежнему будет работать с таблицей Sales by Product и будет происходить так, как описано ниже.
Первоначально все строки имеют версию равную 1 и доступны для пользователя.
OrderDate ProductID Amount Quantity Version
1996-11-07 00:00:00.000 1 216.0000 15 1
1996-11-14 00:00:00.000 1 172.8000 12 1
1996-12-03 00:00:00.000 1 216.0000 15 1
Соответственно запрос к представлению

SELECT *
FROM [Report by Product]
возвращает следующий результат:
OrderDate ProductID Amount Quantity
1996-11-07 00:00:00.000 1 216.0000 15
1996-11-14 00:00:00.000 1 172.8000 12
1996-12-03 00:00:00.000 1 216.0000 15
После выполнения следующего запроса на вставку в таблицу

INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity, Version)
SELECT     Orders.OrderDate, [Order Details].ProductID, 
          SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, 
        SUM([Order Details].Quantity) AS Quantity, 2 as Version
FROM       [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID
   INNER JOIN DatesForUpdates on Orders.OrderDate = DatesForUpdates.UpdateDate
GROUP BY Orders.OrderDate, [Order Details].ProductID
в таблице Sales by Product будут содержаться следующие данные:
OrderDate ProductID Amount Quantity Version
1996-11-07 00:00:00.000 1 216.0000 15 1
1996-11-14 00:00:00.000 1 172.8000 12 1
1996-12-03 00:00:00.000 1 216.0000 15 1
1996-11-07 00:00:00.000 1 300.0000 20 2
1996-11-14 00:00:00.000 2 172.8000 12 2
1996-12-03 00:00:00.000 2 216.0000 15 2
Видим, что новые записи помечены номером версии 2. Поэтому запрос к представлению по прежнему возвращает старый набор данных, так как новые строки через него не видны.
OrderDate ProductID Amount Quantity
1996-11-07 00:00:00.000 1 216.0000 15
1996-11-14 00:00:00.000 1 172.8000 12
1996-12-03 00:00:00.000 1 216.0000 15
Следующий запрос выполнит обновление номеров версий строк:

UPDATE [Sales by Product]
SET Version = CASE WHEN Version = 2 THEN 1 
     ELSE 3
       END
FROM [Sales by Product], DatesForUpdates
WHERE [Sales by Product].OrderDate = DatesForUpdates.UpdateDate
Это приведет к тому, что номера версий строк обновятся так:
OrderDate ProductID Amount Quantity Version
1996-11-07 00:00:00.000 1 216.0000 15 3
1996-11-14 00:00:00.000 1 172.8000 12 3
1996-12-03 00:00:00.000 1 216.0000 15 3
1996-11-07 00:00:00.000 1 300.0000 20 1
1996-11-14 00:00:00.000 2 172.8000 12 1
1996-12-03 00:00:00.000 2 216.0000 15 1
Теперь старые данные стали недоступны через представление – на их место встали новые. Запрос к представлению возвращает следующий результат:
OrderDate ProductID Amount Quantity
1996-11-07 00:00:00.000 1 300.0000 20
1996-11-14 00:00:00.000 2 172.8000 12
1996-12-03 00:00:00.000 2 216.0000 15
После этого мы можем произвести очистку старых данных

DELETE FROM [Sales by Product] WHERE Version = 3

DELETE FROM DatesForUpdates
После очистки таблица вновь остается в исходном состоянии – все строки имеют номер версии равный 1.
Итак, весь процесс обновления выглядит следующим образом –

-- Вставка новых данных с номером версии строк 2
INSERT INTO [Sales by Product] (OrderDate, ProductID, Amount, Quantity, Version)
SELECT     Orders.OrderDate, [Order Details].ProductID, 
          SUM([Order Details].UnitPrice * [Order Details].Quantity) AS Amount, 
        SUM([Order Details].Quantity) AS Quantity, 2 as Version
FROM       [Order Details] INNER JOIN
                      Orders ON [Order Details].OrderID = Orders.OrderID
   INNER JOIN DatesForUpdates on Orders.OrderDate = DatesForUpdates.UpdateDate
GROUP BY Orders.OrderDate, [Order Details].ProductID

-- Обновление версий строк: 2 –> 1, 1 -> 3
UPDATE [Sales by Product]
SET Version = CASE WHEN Version = 2 THEN 1 
     ELSE 3
       END
FROM [Sales by Product], DatesForUpdates
WHERE [Sales by Product].OrderDate = DatesForUpdates.UpdateDate

-- Удаление старых данных с номером версии 3
DELETE FROM [Sales by Product] WHERE Version = 3

-- Очистка DatesForUpdates
DELETE FROM DatesForUpdates
Видим, что первый запрос вставляет в таблицу данные с версией строк равной 2 (новая строка), благодаря чему эти новые данные пользователю не доступны. Сколько бы ни шел процесс расчета и вставки, пользователь не видит этих строк – он продолжает получать старые строки, которые были созданы в процессе предыдущего обновления и имеют значение поле Version равное 1. Второй запрос изменяет номера версий строк – новые данные становятся на место старых (2 исправляется на 1), а старые помечаются как готовые к удалению (версия 1 исправляется на 3). После выполнения этого запроса у пользователя мгновенно исчезают старые данные, а вместо них появляются новые. Затем строки с устаревшими данными (номер версии 3) удаляются. Благодаря такой схеме у пользователя ни в один момент времени не пропадают данные и не возникает ситуации при которой он видит одновременно и старые и новые данные.

Заключение

Данный простой механизм был использован в ряде промышленных разработок и зарекомендовал себя.

Я с радостью рассмотрю любые комментарии и вопросы по данной тематике.
Мои координаты доступны на сайте www.msmirnov.ru

Михаил Смирнов
Руководитель проектов.
Мой сайт - www.msmirnov.ru