Ирина Наумова

Фильтр по тегу: sql server


Распараллеливание хэш-соединений

 По материалам статьи Craig Freedman: Parallel Hash Join

SQL Server использует один из двух вариантов стратегии распараллеливания хэш-соединения. Наиболее часто встречается хэш-секционирование (Hash Partitioning). Реже можно встретить Broadcast-секционирование; эту стратегию часто называют "Broadcast hash join".

Хэш-секционирование

Типичное распараллеливания хеш-соединения включает распределение строк компановки (т.е. строк из первого входого потока) и пробных строк (т.е. строк из второго входного потока) между отдельными потоками хэш-соединения с использованием хэш-секционирования. Если строки компановки и пробы имеют одни и те же значения ключа (т.е. они участвуют в соединении), такие хэши гарантированно попадут в один и тот же поток хэш-соединения. После того как данные были секционированы по хэшам между потоками, все экземпляры хэш-соединений работают полностью независимо от соответствующих им наборов данных. Отсутствие любых межпоточных зависимостей гарантирует, что эта стратегия будет давать очень хорошую масштабируемость по мере повышения степени параллелизма (т.е. числа потоков).
Как и в других рассмотренных ранее примерах с параллелизмом, в этой статье используется достаточно большая таблица, что бы, работая с ней, оптимизатор выбирал параллельный план исполнения запроса. Если вы будете воспроизводить эти примеры у себя, учтите, что создание этой таблицы может затянуться на несколько минут.

 

create table T1 (a int, b int, x char(200))
set noco...

читать дальше...
добавлено: 20 май 14 просмотры: 2096, комментарии: 0



Распараллеленное соединение вложенных циклов (Nested Loops)

По материалам статьи из блога Craig Freedman: Parallel Nested Loops Join

Перевод Ирины Наумовой

Техническая редакция Александра Гладченко

SQL Server распараллеливает соединение вложенных циклов (Nested Loops Join), распределяя в случайном порядке строки внешней таблицы по потокам вложенных циклов. В данном случае, речь идёт о строках, которые поступают первыми, и мы их видим вверху, на графическом плане запроса. Например, если на входе соединения вложенных циклов имеется два потока, каждый поток получит приблизительно половину строк. Потоки проходятся по строкам внутренней таблицы соединения (то есть, по строкам, поданным во вторую очередь, мы их видим ниже в плане запроса), точно по такому же алгоритму, как это было бы реализовано в сценарии с последовательной обработкой строк. Таким образом, для каждой обрабатываемой потоком строки внешней таблицы, поток обеспечивает соединение своей внутренней таблицы, используя эту строку в качестве источника коррелированных параметров. Это позволяет потокам работать независимо друг от друга. При этом для внутренней таблицы соединения вложенных циклов SQL Server не добавляет операторы параллелизма и работу с ней не распараллеливает.

Простой пример

Давайте рассмотрим простой пример. Чтобы вынудить оптимизатор выбрать параллельный план, создадим таблицу с большим числом строк. Если Вы решите проверить эти примеры у себя, учтите, что заполнение таблицы тестовыми данными может занять несколько минут.

create table T1 (a int, b int,...

читать дальше...
добавлено: 27 май 13 просмотры: 1958, комментарии: 1



Поиск узких мест ввода-вывода для MS SQL Server

По материалам статьи Tibor Nagy: How to Identify I/O Bottlenecks in MS SQL Server - 17.03.2011

Проблема

   Суть проблематики данной статьи - регулярное замедление в работе баз данных SQL Server. После статей, посвящённых анализу использования памяти и CPU, мы хотели бы продолжить исследование причины замедления путём анализа узких мест ввода-вывода.

Решение

   Подсистема ввода-вывода - ключевой фактор производительности SQL Server, поскольку страницы базы данных постоянно перемещаются с диска в буферный пул или обратно. Помимо этого существенный трафик ввода-вывода генерируют журналы транзакций и системная база данных tempDB. Учитывая эти факторы, Вы должны быть уверены, что используемая подсистема ввода-вывода работает стабильно, иначе проблемы в работе этой подсистемы приведут к увеличению времени отклика запросов и частым тайм-аутам. В этой статье я опишу несколько способов поиска узких мест ввода-вывода, используя для этого встроенные инструменты, и представлю некоторые идеи, связанные с конфигурацией дисков.

Performance Monitor

   Чтобы определить загрузку подсистемы ввода-вывода, можно воспользоваться системной утилитой Performance Monitor. Перечисленные ниже счётчики производительности могут оказаться полезны для этих целей:

    PhysicalDisk Object: Avg. Disk Queue Length. Этот счетчик показывает среднее число запросов чтения и записи, которые были поставлены в очередь для указанного физического диск...

читать дальше...
добавлено: 07 май 11 просмотры: 9467, комментарии: 0



Распараллеленный Просмотр

По материалам статьи Craig Freedman: Parallel Scan

Перевод Ирины Наумовой и Александра Гладченко

В этой статье я собираюсь рассмотреть то, как SQL Server распараллеливает просмотр таблицы (сканирования - scans). Оператор просмотра - один из немногих операторов, которые адаптированы к параллелизму. Большинство других операторов ничего не знают о параллелизме, и не заботятся о том, выполняются ли они параллельно; оператор просмотра является в этом случае исключением.

Как же в действительности работает распараллеленный просмотр?

Потоки, которые составляют распараллеленный просмотр, сообща трудятся над тем, чтобы выполнить полный просмотр всех строк в таблице. Априори, нет никакого явного закрепления строк или страниц за конкретными потоками. Вместо этого движок хранилища раздаёт страницы потокам динамически. Доступ к страницам таблицы координирует поставщик распараллеленных страниц (parallel page supplier). Он гарантирует, что каждая страница будет отдана только одному потоку и, таким образом, попадёт на обработку только один раз.

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

У этого алгоритма есть пара преимуществ:

     

  • Он независим от числа потоков. Мы можем добавлять и удалять потоки из...
читать дальше...
добавлено: 17 дек 10 просмотры: 2601, комментарии: 4



Оператор распараллеливания (Exchange)



По материалам статьи Craig Freedman «The Parallelism Operator (aka Exchange)»



Перевод Ирины Наумовой


Как я уже писал в статье Введение в распараллеливание исполнения запроса , итератор параллелизма (или обмена - Exchange operator) фактически привносит в процесс выполнения запроса возможность распараллеливания задачи. Оптимизатор помещает оператор обмена в том месте, где происходит разделение на несколько потоков, и оператор обмена перемещает строки между потоками.


читать дальше...
добавлено: 14 июл 10 просмотры: 1981, комментарии: 0



Сравнения списка объектов SQL Server в PowerShell на примере сравнения логинов на двух серверах

В данном примере я покажу как с помощью PowerShell можно сравнивать списки объектов на двух серверах на примере сравнения логинов: поиск одинаковых, поиск разницы. Для этого сначала выполним подключение к серверам. В данном примере первый сервер - локальный. К нему будем подключаться с использованием Windows аутентификации.
Второй сервер - удаленный, к нему будем подключаться с использованием аутентификации SQL Server и запросом на ввод логина и пароля при подключении.

    #1. Загружаем SMO

    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo ") | Out-Null

    #2. Подключаемся к серверу 1 (Windows аутентификация):
    #Сервер1-локальный
    #Если нужен другой сервер, введите вместо точки IP-адрес \ имя сервера

    $smoserver1 =new-object("Microsoft.SqlServer.Management.Smo.Server") "."

    #3. Подключаемся к Серверу 2 (аутентификация SQL Server):
    #В следующей строке нужно вписать IP-адрес сервера 2 либо имя сервера

    $smoserver2 =new-object("Microsoft.SqlServer.Management.Smo.Server") "IP - адрес \ имя сервера2"

    #Если аутентификация SQL Server, то значение FALSE, если Windows(по умолчанию) - TRUE

    $smoServer2.ConnectionContext.set_LoginSecure($FALSE)

    #Запрос ввода имени пользователя и пароля

    $LoginCredentials = Get-Credential

    #Устанавливаем свойства ConnectionContext

    $smoServer2.ConnectionContext.set_EncryptConnection($FALSE)

    #В конце поста я расскажу зачем в строке ниже убираем слеш из имени пользователя

    $smoServer2.ConnectionContext.set_Login($LoginCredentials.UserName -replace("\"))
    $smoServer2.ConnectionContext.set_SecurePassword($LoginCredentials.Password)

    #4. Сравниваем логины
    #Командлет выводит логины, которые есть на обоих серверах

    compare-object -referenceobject $($smoserver1.logins) -differenceobject $($smoserver2.logins) -includeequal | where-object {$_.SideIndicator -eq "=="} |select-object InputObject

    #Командлет выводит логины, которые есть на сервере 2, но нет на сервере 1

    compare-object -referenceobject $($smoserver1.logins) -differenceobject $($smoserver2.logins) | where-object {$_.SideIndicator -eq "=>"} |select-object InputObject

    #Командлет выводит логины, которые есть на сервере 1, но нет на сервере 2

    compare-object -referenceobject $($smoserver1.logins) -differenceobject $($smoserver2.logins) | where-object {$_.SideIndicator -eq "<="} |select-object InputObject

    #Можно перенаправить вывод в файл

    compare-object -referenceobject $($smoserver1.logins) -differenceobject $($smoserver2.logins) -includeequal | where-object {$_.SideIndicator -eq "=="} |select-object InputObject > C:\Test.txt

По тому же принципу можно сравнивать списки и других объектов сервера, например заданий.

    #Для того, чтобы сравнить список заданий на двух серверах, можно воспользоваться
    #объектом SMOServer.JobServer.Jobs:

    compare-object -referenceobject $($smoserver1.JobServer.Jobs) -differenceobject $($smoserver2. JobServer.Jobs)

Для получения справки по командлету compare-object, в окне powershell введите:

    Get-help Compare-object -detailed

Иерархия объектов SMO приведена в документации по адресу: http://msdn.microsoft.com/ru-ru/library/ms162209.aspx


Теперь о том, почему мы убираем слеш из введенного имени пользователя (...-replace("\")...).

Командлет get-credential ждет ввода в формате Домен\Логин. В нашем примере мы используем его не для входа в домен, а для подключения к SQL Server и домен не вводим, поэтому, несмотря на то, что в окне запроса учетных данных я вводила логин "Inaumova", свойству UserName присвоилось значение "\Inaumova". Это показано на рисунках ниже. Поэтому слеш нужно удалить.

читать дальше...
добавлено: 18 ноя 09 просмотры: 3089, комментарии: 0



Индексные объединения

По материалам статьи Craig Freedman: Index Union
Перевод Ирины Наумовой

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

Начнем:

create table T (a int, b int, c int, x char(200))
create unique clustered index Ta on T(a)
create index Tb on T(b)
create index Tc on T(c)

insert T values (1, 1, 1, 1)
insert T values (2, 2, 2, 2)
insert T values (3, 3, 3, 3)

select a from T where b = 1 or b = 3

  |--Index Seek(OBJECT:([ Т ].[ Тb ]), SEEK:([ Т ]. [ b ]=(1) OR [ Т ]. [ b ]=(3)) ORDERED FORWARD)

У нас имеется индекс на поле "b", и, как и ожидалось, оптимизатор выбирает поиск по индексу. Поскольку мы имеем два предиката для поля "b", мы получим поиск с двумя предикатами. Вначале выполнится предикат "b=1", а зетем "b=3". Обратите внимание, что поскольку мы выводим столбец "a", а он является кластеризованным (и таким образом покрывает все некластеризованные индексы), не возникает необходимости в операции BOOKMARK LOOKUP (поиск закладок). Пока нет никаких неожиданностей.
Обратите внимание, что мы могли записать этот запрос в ином виде:

select a from T where b = 1
union all
select a from T where b = 3

  |--Concatenation
     |--Index Seek(OBJECT:([ Т ].[ Тb ]), SEEK:([ Т ]. [ b ]=(1)) ORDERED FORWARD)
     |--Index Seek(OBJECT:([ Т ].[ Тb ]), SEEK:([ Т ]. [ b ]=(3)) ORDERED FORWARD)

Оптимизатор не объединяет эти операции в одну операцию INDEX SEEK (поиска по индексу), но запросы и планы логически идентичны.
Рассмотрим следующий запрос:

select a from T where b = 1 or c < 3

  |--Clustered Index Scan(OBJECT:([Т].[Тa]), WHERE:([ Т ]. [ b ]=(1) OR [Т]. [ c ]<(3)))

У нас имеются индексы на столбцах "b" и "c", но оптимизатор их не использовал. Почему? Нам нужны все строки, удовлетворяющие любому из предикатов. Мы могли бы использовать индекс на столбце "b", чтобы получить строки, удовлетворяющие предикату "b=1", но при этом мы можем пропустить строки, которые удовлетворяют предикату "с<3", и для которых "b!=1". Например, мы пропустили бы строку со значением (2,2,2,2). Та же самая проблема возникает при использовании индекса на столбце "с", чтобы удовлетворить предикату "c<3". (В моем примере данные не включают строк со значением "b=1", для которых выполняется предикат "с>3", но такая строка могла бы существовать, поэтому мы должны предусмотреть и такой вариант).

Индексные объединения

И так, давайте разберёмся, будет ли SQL Server так выполнять декомпозицию запроса, чтобы использовать два индекса? Да! Сначала для того чтобы оптимизатор выбрал другой план, в котором не будет сканирования кластерного индекса, мы должны добавить достаточно большое количество данных в таблицу, чтобы сделать операцию сканирования кластерного индекса более дорогостоящей.
Обратите внимание, что к используемой ранее таблице я добавил столбец типа char(200), чтобы строки стали больше. Добавление этого столбца приведет к тому, что таблица будет занимать больше страниц, что также сделает операцию просмотра более дорогостоящей.

truncate table T

set nocount on
declare @i int
set @i = 0
while @i < 1000
  begin
    insert T values(@i, @i, @i, @i)
    set @i = @i + 1
  end

select a from T where b = 1 or c < 3

  |--Sort(DISTINCT ORDER BY:([T]. [ а ]ASC))
     |--Concatenation
        |--Index Seek(OBJECT:([T].[Tb]), SEEK:([T]. [ b ]=(1)) ORDERED FORWARD)
        |--Index Seek(OBJECT:([T].[Tc]), SEEK:([T]. [ c ] < (3)) ORDERED FORWARD)

Этот план очень похож на приведенный выше план с оператором UNION ALL. Оптимизатор выполнил декомпозицию таким образом, что запрос стал выглядеть так:

select a from T where b = 1
union
select a from T where c < 3

Однако, стоит обратить внимание на то, что два объединяемых запроса могут возвратить дубликаты строк, поэтому нужно использовать оператор UNION (который устраняет дубликаты), а не UNION ALL (который этого не делает). Оператор CONCATENATION реализует конструкцию UNION ALL, а SORT DISTINCT устраняет дубликаты, превращая UNION ALL в UNION. Такой тип плана исполнения запроса можно считать индексным объединением.

Merge Join

Давайте немного изменим запрос:

select a from T where b = 1 or c = 3

  |--Stream Aggregate(GROUP BY:([Т]. [ a ]))
     |--Merge Join(Concatenation)
        |--Index Seek(OBJECT:([ Т ].[ Тb ]), SEEK:([ Т ]. [ b ]=(1)) ORDERED FORWARD)
        |--Index Seek(OBJECT:([Т].[Тc]), SEEK:([Т]. [ c ]=(3)) ORDERED FORWARD)

Теперь, вместо операторов CONCATENATION и SORT DISTINCT мы имеем MERGE JOIN (Concatenation) и STREAM AGGREGATE. Что же произошло? MERGE JOIN (Concatenation) или "MERGE UNION" в действительности ничего не соединяют. Это осуществляется также, как с помощью итератора MERGE UNION, но в действительности выполняется операция UNION ALL с сохранением порядка входных строк. После этого используется STREAM AGGREGATE, который устраняет дубликаты (для получения более подробной информации, изучите статью "Агрегат потока (Stream Aggregate)", в которой написано об использовании STREAM AGGREGATE для устранения дубликатов). Такой план исполнения запроса будет работать лучше, потому что не будет использовать SORT DISTINCT, который использует память и может стать причиной сброса страницы данных на диск, если действие выполняется за пределами памяти. В этом плане исполнения запроса используется STREAM AGGREGATE, который память не использует.

Тогда почему же мы не использовали этот план с самого начала? Точно так же как MERGE JOIN, MERGE UNION требует, чтобы входные данные были отсортированы по ключу слияния (в нашем случае это столбец "a"). Некластеризованный индекс "Tb" покрывает ключ индекса "b" и ключ кластеризованного индекса "a". Таким образом, этот индекс возвращает строки в порядке (b, a). Однако, это эквивалентно предикату "b = 1", столбец "b" - константа, этим мы фактически упорядочиваем строки по столбцу "a". То же самое случается с индексом Tc и предикатом "c = 3". Таким образом, у нас имеется два входных потока, которые оба упорядочены по столбцу "а", и мы можем использовать MERGE UNION.
В предшествующем примере, одним из предикатов был "c 3". Поскольку этот предикат - неравенство, INDEX SEEK возвращает строки в порядке (c, a). И так как строки не отсортированы по столбцу "a", мы не сможем использовать MERGE UNION.

Объединение трех индексов

Оператор CONCATENATION может поддерживать больше двух входных потоков:

select a from T where a = 1 or b = 2 or c < 3

  |--Sort(DISTINCT ORDER BY:([Т]. [ а ]ASC))
     |--Concatenation
        |--Clustered Index Seek(OBJECT:([Т].[Тa]), SEEK:([Т]. [ a ]=(1)) ORDERED FORWARD)
        |--Index Seek(OBJECT:([ Т ].[ Тb ]), SEEK:([ Т ]. [ b ]=(2)) ORDERED FORWARD)
        |--Index Seek(OBJECT:([Т].[Тc]), SEEK:([Т].[с] < (3)) ORDERED FORWARD)

MERGE UNION поддерживает только два входных потока, но входные потоки можно объединять каскадом, чтобы позволяет в итоге объединить больше двух входных потоков:

select a from T where a = 1 or b = 2 or c = 3

  |--Stream Aggregate(GROUP BY:([Т]. [ a ]))
     |--Merge Join(Concatenation)
        |--Merge Join(Concatenation)
        |   |--Clustered Index Seek(OBJECT:([Т].[Тa]), SEEK:([Т]. [ a ]=(1)) ORDERED FORWARD)
        |   |--Index Seek(OBJECT:([ Т ].[ Тb ]), SEEK:([ Т ]. [ b ]=(2)) ORDERED FORWARD)
        |--Index Seek(OBJECT:([Т].[Тc]), SEEK:([Т]. [ c ]=(3)) ORDERED FORWARD)

Какие столбцы возвращает объединение?

Объединение возвращает только те столбцы, которые являются общими для всех его входных потоков. Во всех приведенных выше примерах индексных объединений, единственным столбцом, который у индексов был общим, являлся ключ кластеризованного индекса - колонка "a" (это как если бы индекс Tb состоял из столбцов "b, a", а индекс Tc состоял из "c, a"). Таким образом, наше объединение может вернуть только столбец "a". Если будут запрошены и другие столбцы, будет использоваться BOOKMARK LOOKUP. Так будет даже в том случае, если один из индексов в объединении будет покрывающим ещё для каких-либо столбцов. Например, если мы запросим все три столбца "a", "b" и "c", в плане исполнения будет BOOKMARK LOOKUP, несмотря на то, что столбец "b" будет покрываться индексом Тb, а столбец "c" будет покрываться индексом Тc:

select a, b, c from T where b = 1 or c = 3

  |--Nested Loops(Inner Join, OUTER REFERENCES:([Т]. [ a ]))
     |--Stream Aggregate(GROUP BY:([Т]. [ a ]))
        |   |--Merge Join(Concatenation)
        |      |--Index Seek(OBJECT:([ Т ].[ Тb ]), SEEK:([T]. [ b ]=(1)) ORDERED FORWARD)
        |      |--Index Seek(OBJECT:([Т].[Тc]), SEEK:([Т]. [ c ]=(3)) ORDERED FORWARD)
        |--Clustered Index Seek(OBJECT:([Т].[Тa]), SEEK:([Т]. [ a ]=[Т]. [ a ]) LOOKUP ORDERED FORWARD)

читать дальше...
добавлено: 02 окт 09 просмотры: 2689, комментарии: 0



Репликация программируемых объектов БД в SQL Server 2005

Репликация SQL Server 2005/2008. Сборник статей от сообщества SQL.RU

Под общей редакцией А. Гладченко и В. Щербинина.

Москва. ЭКОМ Паблишерз, 2008Г. 288 страниц. ISBN: 978-5-9790-0086-2. Книга уже в продаже.

Эта книга - сборник статей, которые посвящены ключевым для понимания репликации SQL Server моментам. Кроме переводов наиболее интересных зарубежных авторов, являющимисяхся признанными во всём мире специалистами, в книге вы найдёте ряд статей участников сообщества SQL.RU, которые многие годы помогаю решать разнообразные проблемы на технических форумах этого замечательного интернет - ресурса. Если во время прочтения книги у вас возникнут сомнения или вопросы по поводу изложенных тут материалов, смело обращайтесь к авторам статей на форумах SQL.RU, они с удовольствием вам помогут.
В сборник включены переводы и статьи следующих участников сообщества SQL.RU: Дмитрий Артёмов, Александр Волок, Александр Гладченко, Ильдар Даутов, Григорий Кoрнилов, Алексей Ковалёв, Наталья Кривонос, Ян Либерман, Ирина Наумова и Владислав Щербинин. Причём, на момент написания книги, Александр Гладченко, Ян Либерман и Ирина Наумова являлись SQL Server MVP.
Книга в первую очередь ориентирована на администраторов баз данных, которые собираются углубить свои познания в репликации SQL Server. Назначение этой книги состоит в том, чтобы предоставить читателям набор апробированных в течение нескольких лет рецептов по использованию и настройке репликации в SQL Server.

ЗАКАЗАТЬ

Обсудить книгу можно тут: https://www.sql.ru/forum/actualthread.aspx?tid=643700

Пример статьи из сборника:

Репликация программируемых объектов БД в SQL Server 2005

По материалам статьи Байя Павлиашвили (Baya Pavliashvili) «Replicating Code Modules with SQL Server 2005».

Перевод Ирины Николаевны Наумовой

В предыдущих статьях из этой серии рассказывалось о том, как организовать в SQL Server 2005 репликацию статей представляющих собой таблицы. Как и предыдущие версии, SQL Server 2005 предоставляет возможность репликации модулей кода: хранимые процедуры, представления (включая индексированные представления) и пользовательские функции (UDF). В этой статье дается краткий обзор репликации программируемых объектов и даются рекомендации по использованию репликации таких модулей.

Настройка репликации программируемых объектов.

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

    Тип статьи

    Причина

    Представление

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

    Индексированное представление

    Таблицы, на которых базируется представление, должны существовать на подписчике. Однако эти таблицы не участвуют в репликации.

    На серверах – подписчиках должна быть установлена версия SQL Server 2000 и выше. Все подписчики должны использовать SQL Server в редакции Enterprise Edition.

    Хранимые процедуры, определяемые пользователем функции

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

Для добавления в репликацию представлений, пользовательских функций и хранимых процедур можно воспользоваться мастером создания публикации. После того как вы откроете публикацию нужного типа для издаваемой базы данных, можно приступить непосредственно к работе со статьёй. На следующем рисунке продемонстрировано добавление к публикации представления, индексированного представления, хранимой процедуры и пользовательской функции.


Рис.1.

Чтобы выбрать опции для каждой добавляемой в публикацию статей, нужно нажать кнопку Article Properties. Можно выбрать несколько опций для каждого типа реплицируемых программируемых объектов. Также можно реплицировать схему представлений, индексируемых представлений и пользовательских функций. Для хранимых процедур предусмотрена дополнительная гибкость – помимо их определения вы можете реплицировать и их выполнение. В таблицу ниже сведены опции, доступные для настройки при репликации программируемых объектов.

    Тип статьи

    Опция/значение

    Описание

    Представления

    Copy User Triggers: True or False

    Для реплицируемого представления создается триггер в базе данных подписчика, если он есть на издателе.

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

    Copy Extended Properties: True or False

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

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

    Destination Object Name/Destination Object Owner

    Вы можете создать реплицируемый объект с тем же именем что и на издателе и владельцем объекта или с другим именем и/или владельцем.

    Представления, индексированные представления, хранимые процедуры, определяемые пользователем функции.User Defined Function

    Action if name in use: Keep existing object unchanged OR Drop existing object and create a new one

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

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

    Create Schemas at Subscriber: True or False

    Определяет, должен ли выполняться оператор CREATE SCHEMA на подписчике, если там нет схемы объекта.

    Хранимые процедуры

    Replicate: Stored procedure definition only; Execution of the stored procedure; Execution in a serialized transaction of the SP.

    Определяет, должно ли реплицироваться выполнение хранимой процедуры.

    Запомните, оператор ALTER PROCEDURE будет реплицировать изменения схемы даже для публикаций, в которых реплицируется выполнение хранимой процедуры, таким образом, изменения в определении хранимой процедуры будут всегда доставлены подписчику (если только вы явно не задали репликацию изменений схемы).

Запомните: Некоторые опции для статьи нельзя изменить после создания публикации. Например, опция Procedure Replicate не может быть изменена на Stored Procedure Definition, изменить её можно только на Execution Of The Stored Procedure. Чтобы изменить эту опцию, можно удалить статью и добавить ее заново, а затем уже изменить опцию. Так что перед настройкой репликации, определите заранее какие опции вам нужно будет установить.

Как только Вы установили свойства для каждой статьи, можно создать снимок для публикации немедленно и/или создать расписание для запуска Snapshot Agent. Следующим шагом необходимо определить параметры настройки безопасности для Snapshot Agent и Log Reader agent, проанализируйте то, что должен сделать мастер, и нажмите кнопку Finish, чтобы создать публикацию.

Мастер создания публикации предоставит данные о каждом типе программируемых объектов, которые вы пытаетесь реплицировать. Однако, публикация может быть создана даже если на подписчике нет объектов, на которые она использует.

Процесс создания подписки для публикации программируемых объектов идентичен процессу репликации табличных статей. Если любой из объектов, на которые ссылается копируемый модуль кода, отсутствует на подписчике, агент распределения уведомит вас об ошибке, но подписка будет создана. Сообщения об ошибках в работе агента распределения помогают понять причины проблем, потому что с помощью этих сведений можно определить закончившуюся ошибкой команду (см. следующий рисунок).


Рис.2.

Примечание: Ошибки, возникающие при репликации программируемых объектов, обычно легко обнаружимы. Таких ошибок можно избежать, если внимательно читать экраны мастера и выполнить все требования.

Следующий сценарий создает публикацию, в которую входят хранимая процедура, представление, индексированное представление и пользовательская функция:

    exec sp_addpublication @publication = n’pub_name’,
    @description=N’Transactional publication of database ’’AdventureWorksDW’’ .’,
    @sync_method = N’concurrent’, @retention = 0, @allow_push = N’true’,
    @allow_pull = N’true’, @allow_anonymous = N’true’,
    @enabled_for_internet = N’false’, @snapshot_in_defaultfolder = N’true’,
    @compress_snapshot = N’false’, @ftp_port = 21, @ftp_login = N’anonymous’,
    @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’,
    @repl_freq = N’continuous’, @status = N’active’,
    @independent_agent = N’true’, @immediate_sync = N’true’,
    @allow_sync_tran = N’false’, @autogen_sync_procs = N’false’,
    @allow_queued_tran = N’false’, @allow_dts = N’false’, @replicate_ddl = 1,
    @allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’,
    @enabled_for_het_sub = N’false’
    GO

    --Добавление статьей в публикацию репликации транзакций
    --Определяемая пользователем функция:
    exec sp_addarticle @publication = n’pub_name’,
    @article = N’udfMinimumDate’, @source_owner = N’dbo’,
    @source_object = N’udfMinimumDate’, @type = N’func schema only’,
    @description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
    @schema_option = 0x0000000008000001, @destination_table = N’udfMinimumDate’,
    @destination_owner = N’dbo’, @status = 16
    GO

    --Хранимая процедура:
    exec sp_addarticle @publication = n’pub_name’,
    @article = N’update_factFinance’, @source_owner = N’dbo’,
    @source_object = N’update_factFinance’, @type = N’proc exec’,
    @description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
    @schema_option = 0x0000000008000001, @destination_table = N’update_factFinance’,
    @destination_owner = N’dbo’, @status = 0
    GO
    --Индексированное представление:
    exec sp_addarticle @publication = n’pub_name’,
    @article = N’View_DimCustomer_Young’, @source_owner = N’dbo’,
    @source_object = N’View_DimCustomer_Young’, @type = N’indexed view schema only’,
    @description = N’’, @creation_script = N’’, @pre_creation_cmd = N’drop’,
    @schema_option = 0x0000000008000001, @destination_table = N’View_DimCustomer_Young’,
    @destination_owner = N’dbo’, @status = 16
    GO
    --Представление:
    exec sp_addarticle @publication = n’pub_name’,
    @article = N’vTimeSeries’, @source_owner = N’dbo’,
    @source_object = N’vTimeSeries’, @type = N’view schema only’, @description = N’’,
    @creation_script = N’’, @pre_creation_cmd = N’drop’,
    @schema_option = 0x0000000008000001, @destination_table = N’vTimeSeries’,
    @destination_owner = N’dbo’, @status = 16
    GO

Изменение схемы репликации

Вспомните, в предыдущей версии SQL Server для того, чтобы определения программируемых объектов передались подписчику, нужно было запустить агента создания снимка. В SQL Server 2005 это уже не так: репликация передает операторы ALTER VIEW, ALTER FUNCTION, ALTER PROCEDURE и ALTER TRIGGER подписчику в реальном времени. Опция репликации триггеров уже не является единственной возможностью для статей соответствующих типов, но она по-прежнему позволяет копировать триггеры, определенные на таблице или представлении в публикуемой базе данных. Запомните, что нельзя реплицировать DDL триггеры (триггеры языка определения данных).

Давайте рассмотрим репликацию изменения индексируемого представления. Я создал очень простой пример индексируемого представления на издателе с помощью следующих команд:

    CREATE VIEW [dbo].[View_DimCustomer_Young]
    WITH SCHEMABINDING
    AS
    SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
    FROM dbo.DimCustomer
    WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1980’, 101))
    GO

    CREATE UNIQUE CLUSTERED INDEX [ix_DCY_CustomerKey] ON [dbo].[View_DimCustomer_Young]
    (
    [CustomerKey] ASC
    )WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF)
    ON [PRIMARY]

Это представление возвращает несколько столбцов таблицы DimCustomer для записей клиентов, которые родились после 1 января 1980 года. Я добавил это представление в публикацию репликации транзакций и создал для нее подписку на другом сервере.

Теперь давайте изменим представление, чтобы оно возвращало клиентов, родившихся после 1 января 1978 года. Для этого я выполнил следующий код:

    ALTER VIEW [dbo].[View_DimCustomer_Young]
    WITH SCHEMABINDING
    AS
    SELECT CustomerKey, GeographyKey, FirstName, LastName, BirthDate
    FROM dbo.DimCustomer
    WHERE (BirthDate > CONVERT(SMALLDATETIME, ’1/1/1978’, 101))

Теперь, если я в контексте базы данных распространителя выполняю системную хранимую процедуру sp_browserplcmds, я найду там команду ALTER VIEW, которая предназначена для передачи подписчику.

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

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


Рис.3.

Репликация индексируемых представлений как таблиц

Репликация индексированных представлений осуществляется также как репликация таблиц; в этом случае, SQL Server создает таблицу на подписчике, которая содержит те же данные, что и индексированное представление на издателе. Данные изменяются в индексированном представлении на издателе и передаются в таблицу на подписчике. Обратите внимание, что таблица, на которой основано индексированное представление, не обязана существовать на подписчике.

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

Для того чтобы выполнять репликацию индексированного представления как таблицы, нужно изменить параметр @type системной процедуры sp_addarticle. По умолчанию этот параметр установлен в значение N’indexed view logbased’. Например, следующий код добавит индексированное представление View_DimCustomer_Young в существующую публикацию как таблицу.

    exec sp_addarticle
    @publication = N’publication_name’,
    @article = N’View_DimCustomer_Young’,
    @source_owner = N’dbo’,
    @source_object = N’View_DimCustomer_Young’,
    @type = N’indexed view logbased’,
    @description = null,
    @creation_script = null,
    @pre_creation_cmd = N’none’,
    @schema_option = 0x0000000008000001,
    /* table name doesn’t have to be the same as view name */
    @destination_table = N’View_DimCustomer_Young’,
    @destination_owner = N’dbo’
    GO

После настройки репликации индексированного представления как таблицы, операторы INSERT, UPDATE и DELETE, выполненные на представлении на издателе, будут реплицированы в таблицу на подписчике.

Репликация выполнения хранимых процедур

Таким же образом можно настроить репликацию выполнения хранимых процедур, что очень полезно при больших изменениях в имеющихся данных, и при условии, что данные на подписчике и издателе идентичны. Что произойдет, если выполнение оператора UPDATE затрагивает 1000 строк реплицируемой таблицы? По умолчанию SQL Server трансформирует одну команду UPDATE в выполнение хранимой процедуры репликации 1000 раз. Этот вариант хорош тем что каждое выполнение хранимой процедуры репликации затрагивает только одну строку что не вызывает большое количество блокировок/подтверждений на подписчике.

Но что произойдет, если ваша хранимая процедура выполняет изменения, затрагивающие миллион строк в нескольких таблицах? Ваша база данных распределения будет расти экспоненциально, и время задержки репликации может стать недопустимо большим. Перед тем как передать эти команды подписчику SQL Server должен прочитать их из таблицы msrepl_commands базы данных распределения; Агент - чистильщик распределителя занимается удалением транзакции для этих таблиц, когда они уже были переданы подписчику. Если таблица msrepl_commands содержит несколько миллионов строк, чтение и удаление данных из этой таблицы будет выполняться очень медленно. Кроме того, передача больших изменений при использовании табличной статьи, оказывает большую нагрузку на сеть.

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

Другой альтернативой при репликации больших изменений одной таблицы является репликация таблицы путем выполнения одинакового оператора UPDATE на издателе и подписчике (Загляните в первую статью из этой серии, если Вы хотите узнать как это можно сделать). Репликация выполнения хранимой процедуры может быть лучшим выбором, однако, это справедливо обычно для массовых изменений в нескольких таблицах.

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

Например, предположим что у нас есть хранимая процедура, которая изменяет некоторое количество строк в таблице factFinance базы данных AdventureWorksDW:

    CREATE PROC update_factFinance (
    @PercentChange NUMERIC (3,2),
    @OrganizationKey TINYINT,
    @TimeKey INT)
    AS

    /*
    Изменяем количество выданных ключей
    */
    UPDATE factFinance
    SET amount = amount * @PercentChange
    WHERE OrganizationKey = @OrganizationKey
    AND TimeKey = @TimeKey

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

    {call "dbo"."update_factFinance " (1.10, 3, 32)}

Запомните, что репликация будет просто передавать эту команду, репликация не будет проверять, затрагивает ли выполнение команды какие-либо строки на издателе и подписчике. Таким образом, для того чтобы обеспечить целостность данных на издателе и подписчике, необходимо перед использованием репликации выполнения хранимой процедуры удостовериться в том, что данные на подписчике и издателе идентичны.

Репликация выполнения хранимых процедур внутри сериализуемой транзакции.

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

  • Уровень изоляции транзакций у подключения, в котором выполняется хранимая процедура, должен быть установлен в SERIALIZABLE.
  • Необходимо выполнять процедуру внутри явной транзакции, используя операторы BEGIN TRANSACTION / COMMIT TRANSACTION.

Если хотя бы одно условие не будет выполнено, выполнение хранимой процедуры не реплицируется. Помимо этих обязательных условий, также необходимо использовать опцию SET XACT_ABORT ON. Использование этой опции гарантирует, что изменения, внесенные транзакцией, внутри которой выполняется хранимая процедура, будут автоматически отменены, если возникнут ошибки времени выполнения.

Репликация выполнения хранимых процедур внутри сериализуемой транзакции – это рекомендуемая опция, когда необходимо поддержать целостность данных на издателе и подписчике. Почему? В каждой хранимой процедуре содержится несколько явных или неявных транзакций. Вы можете столкнуться с ситуацией когда некоторые транзакции внутри хранимой процедуры завершаются успешно, а другие с ошибкой. Если Вы заставляете SQL Server реплицировать каждое выполнение хранимой процедуры, тогда даже то выполнение, в котором транзакции завершаются ошибками, будет отправлено подписчику. Уровень изоляции транзакций - SERIALIZABLE, является самым жестким уровнем изоляции, гарантирующим, что блокировки будут установлены на всех таблицах, которые использует хранимая процедура. Блокировки будут удерживаться до тех пор, пока транзакция не будет завершена. Поэтому, использование в репликации выполнение только в пределах сериализуемой транзакции, дает гарантию того, что процедура успешно завершит работу на издателе, и только потом будет послана подписчикам.

Давайте воспользуемся процедурой update_factFinance чтобы продемонстрировать как мы можем реплицировать ее выполнение внутри сериализуемой транзакции. Выполним следующий код:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    SET XACT_ABORT ON
    BEGIN TRAN
    EXEC update_factFinance 1.10, 3, 32

    COMMIT

Однако, следующая команда не будет реплицирована, потому что она не включена в явную транзакцию:

    EXEC update_factFinance 1.10, 3, 32

Теперь мы завершим выполнение процедуры, чтобы продемонстрировать значение установки XACT_ABORT. Я изменяю тип данных столбца amount таблицы factFinance на SMALLINT, вместо INT, выполняя следующую инструкцию:

    ALTER TABLE factFinance ALTER COLUMN amount SMALLINT

Максимальное значение для типа SMALLINT – 32768; умножаем максимальное значение столбца на 1.15 чтобы результат превысил 32768, таким образом, следующее выполнение процедуры update_factFinance, приведет к ошибке:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    /* CORRECT setting*/
    SET XACT_ABORT ON
    BEGIN TRAN
    EXEC update_factFinance 1.15, 3, 32

    COMMIT

Результат таков:

    Msg 8115, Level 16, State 2, Procedure update_factFinance, Line 10
    Arithmetic overflow error converting expression to data type smallint.

Транзакция отменена, и выполнение хранимой процедуры не передано подписчику.

Далее, выполним тот же набор команд, отменив установку XACT_ABORT:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    /* НКОРРЕКТНАЯ УСТАНОВКА! Это сделано только в демонстрационных целях! */
    SET XACT_ABORT OFF
    BEGIN TRAN
    EXEC update_factFinance 1.15, 3, 32

    COMMIT

Выполнение хранимой процедуры завершится с той же ошибкой что и в первый раз. Однако при проверке базы данных распределителя, мы найдем следующую команду, посланную подписчику:

    {call "dbo"."update_factFinance" (1.15,3,32)}

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

Вывод

Данная статья рассказывает о том, как выполняется репликация хранимых процедур, представлений и пользовательских функций в SQL Server 2005. По сравнению с другими версиями, возможно, самым важным усовершенствованием является возможность применить изменения на подписчике без запуска агента создания снимка.

Эта серия статей познакомила вас с репликации транзакций в SQL Server 2005. Построенная на твердой основе предыдущих версий, репликации транзакций в SQL Server 2005 – это зрелая технология, которая способна поддержать приложения класса предприятия. Подобно любой технологии, репликация работает хорошо, если используется к месту и решает присущие ей задачи. Убедитесь, что изучили тонкости работы репликации, и постарайтесь очень тщательно спланировать внедрение решений с ее использованием.

читать дальше...
добавлено: 22 сен 09 просмотры: 3482, комментарии: 0



BOL. «Пометки на полях»

Сайт MSDN2 вместе с электронной документацией по Visual Studio 2005/2008 и SQL Server 2005/2008 содержит ещё и встроенный «MSDN Library WiKi». На страницах WiKi содержатся примечания, примеры или пояснения к статьям документации. Недавно стало возможно делать такие пометки и для переведённого на русский язык варианта BOL 2005. В русском переводе WiKi получили название «Содержимое сообщества». Подобно блогам, русскоязычное содержимое может сопровождаться тематическими тегами и имеет свой веб-канал, в котором можно отслеживать последние изменения:  http://msdn2.microsoft.com/ru-ru/library/community-edits(rss).aspx

Для того, чтобы оставлять свои комментарии к статьям BOL, нужно зарегистрироваться. Для регистрации используется LiveID. После этого, на MSDN2 будет создан ваш профиль и другие участники WiKi или читатели интернет – версии BOL смогут видеть список ваших заметок и даже подписаться на новостной Веб-канал вашего профиля.

Наиболее активные и ценные соавторы попадают на «доску почёта», которая доступна на домашней странице WiKi. Судя по активности WiKi на других языках, оказаться на такой доске почёта очень достойно ;) Пока пальму первенства там прочно удерживают сотрудники Майкрософт.

читать дальше...
добавлено: 31 мар 08 просмотры: 1494, комментарии: 0



Автообновление SQL Server Books Online 2005.

Как сообщил Бак Вуди в своем блоге, теперь SQL Server 2005 Books Online будет автоматически обновляться через службу Microsoft Update, правда пока только для английской редакции. В списке компонент, предлагаемых к установке появится февральская версия BOL (Doc Refresh 4). Скриншот установки электронной документации через Microsoft Update можно посмотреть в блоге Гленна Берри.

Поскольку полная версия BOL занимает порядка 150 МВ, Microsoft планирует обновлять электронную документацию примерно раз в квартал. А текущие автообновления будут представлены небольшими модулями.

Сначала обновляется Web-версия BOL. Насколько я поняла, помимо автообновления через Microsoft Update, предусмотрен также механизм частичной синхронизации статей справки  с текущей версией, то есть если пользователь обращается к статье, версия которой устарела,  происходит загрузка новой версии с Web. Предусмотрено два способа такой загрузки: принудительная - pull и пассивная - push. То, какой тип загрузки будет использован, зависит от выбранных параметров справки в сети, которые определяют приоритет проверки содержимого справки:

  • Try online first, then local (Проверить сначала сетевую версию, затем локальную)

  • Try local first, then online (Проверить сначала локальную версию, затем сетевую)

читать дальше...
добавлено: 17 май 07 просмотры: 1014, комментарии: 0