Irina Naumova

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

По материалам статьи 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)

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

ЗАКАЗАТЬ

Обсудить книгу можно тут: http://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, чтобы создать публикацию.

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

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

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

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

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 агент. Например, для того чтобы проверить как передаются изменения хранимой процедуры на один из серверов перед развертыванием этих изменений на все промышленные сервера, вы можете отключить передачу изменений схемы в опциях подписчика диалогового окна свойств публикации, показанного ниже.

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

Репликация индексированных представлений осуществляется также как репликация таблиц; в этом случае, 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 – это зрелая технология, которая способна поддержать приложения класса предприятия. Подобно любой технологии, репликация работает хорошо, если используется к месту и решает присущие ей задачи. Убедитесь, что изучили тонкости работы репликации, и постарайтесь очень тщательно спланировать внедрение решений с ее использованием.

SQL Internals Viewer

Это еще одна полезная и бесплатная утилита для работы с SQL Server. Показывает внутреннюю структуру хранения данных: распределение страниц в памяти и размещение данных на страницах.

 

Скачать утилиту можно по следующей ссылке:

http://www.sqlinternalsviewer.com/download.html

 

Системные требования:

• Windows 2000, Windows XP, Windows Vista or Windows 2003 Server

• .NET Framework 2.0

• Microsoft SQL Server 2005 или Microsoft SQL Server 2008 July CTP. Эта программа не работает с SQL Server 2000.

Терминологический словарик.

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

Подборка общих решений по использованию T-SQL.

Недавно на сайте MSDN появился WIKI, где сотрудники Майкрософт и модераторы форумов MSDN публикуют  примеры решений и ответы на распространённые вопросы по использованию  T-SQL.  Информация в этом WIKI представляет собой предложения, идеи и мнения людей, отвечающих на вопросы посетителей форумов MSDN по SQL Server,  оформлена она в виде статей. Имеются следующие разделы:

• How To Examples of selected T-SQL programming constructs
• How To Troubleshoot and Debug T-SQL Code
• Writing Transact-SQL Statements Tutorial

Появление новых материалов удобно отслеживать, подписавшись на  новостную ленту RSS.

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

SQL job manager от Idera.

   В качестве новогоднего подарка для специалистов по SQL Server компания Idera предоставила возможность до 31 декабря бесплатно скачать их новый продукт - SQL job manager. Он позволяет отслеживать задания с нескольких серверов. Имеет ряд фильтров по состоянию выполнения заданий. Позволяет "перетаскивать" задания с одного сервера на другой. 

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

Ссылка на страницу SQL job manager

Еще раз о дефрагментации...

На Microsoft TechNet появилась еще одна интересная статья, затрагивающая проблему фрагментации данных. В ней описано почему появляется фрагментация и как с ней бороться, в том числе и про использование дополнительных методов: PageDefrag (дефрагментирует файлы, заблокированные операционной системой) и Contig (предоставляет возможность дефрагментации отдельного файла)

Ссылка на статью

Бесплатные тесты по SQL Server 2005.

 На сайте www.brainbench.com до 30 сентября предоставлена возможность бесплатно пройти тесты по множеству продуктов, включая и SQL Server 2005. По SQL Server 2005 доступны следующие тесты:

MS SQL Server 2005 Programming

MS SQL Server 2005 Administration Beta

 Для того, чтобы пройти тестирование, необходимо зарегистрироваться.  В поле "Enter your promo code (optional)" ничего вводить не нужно, можно сразу нажать кнопку <Get Test>.

Дефрагментация баз данных SQL Server с помощью утилиты Diskeeper.

  Howard Butler Sr.,Systems Engineer;

  Michael Materie,Systems Engineer MCSE,CCNA,A+,I-Net+ 

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

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

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

Базы данных SQL Server представляют собой большие файлы базы данных и журналов, для которых во время создания резервируется некоторый размер. Если при создании этих файлов на диске есть непрерывный, не занятый и достаточный по размеру отрезок, они не будут фрагментированы. Но если доступное свободное место не является непрерывным, то уже изначально база данных и журналы будут фрагментированы. Даже если первоначально база данных и журналы не фрагментированы, после их создания, они почти наверняка станут фрагментированными, поскольку база данных все время растет. Например, если Вы устанавливаете первоначальный размер базы данных равным 100 МБ, а файл журнала 10 МБ, и установили следующие параметры автоматического прироста файлов: до 5Гб файл данных и до 100 МБ файл журнала, внешняя фрагментация может быть большой. Каждый раз, когда файлы данных или журналов автоматически увеличиваются, появляется угроза внешней фрагментации.

Для устранения внешней фрагментации используется специализированная утилита операционной системы. Одним из самых популярных инструментов для дефрагментации файлов базы данных SQL Server является Deskeeper  от Executive Software. Diskeeper существует уже много лет, и многие из Вас возможно уже знакомы с ним, и не только в работе с Windows, но и в работе с серверами печати. А вот что не известно многим DBA, так это то, что Deskeeper - лучший инструмент для устранения внешней фрагментации на их серверах с SQL Server. Работа утилиты по устранению внешней фрагментации, подобной Diskeeper, не реструктурирует внутреннее содержание файла, в отличие от DBCC REINDEX. После того как Diskeeper устранит фрагментацию файла, этот не фрагментированный файл будет точным дубликатом оригинала. Поскольку свободные места внутри базы данных у не фрагментированного файла от этой операции не исчезнут, Вам нужно будет время от времени проводить реиндексацию, устраняющую именно внутреннюю фрагментацию страниц данных и индексов.

Есть два типа внешней фрагментации с которой могут справиться утилиты, подобные Diskeeper: файловая фрагментация и фрагментация свободного пространства. Файловая фрагментация затрагивает файл на диске компьютера, когда физически файл лежит не одним куском, а поделен на несколько фрагментов, которые разбросаны по всему диску; в то время как фрагментация свободного пространства означает, что пустое место на диске не лежит одним большим куском, а также раздроблено на множество частей. Файловая фрагментация приводит к проблемам с доступом к данным файла, сохраненного на диске компьютера, в то время как фрагментация свободного пространства приводит к проблемам при создании новых файлов данных или при росте старых. Работа утилиты Diskeeper приводит к дефрагментации файлов данных и журналов, и таким образом, файл физически занимает непрерывное пространство в памяти, вместо того чтобы быть разбитым на части. Кроме того, утилита Diskeeper дефрагментирует свободное пространство за счет чего рост файлов данных или журналов вызывает лишь небольшую фрагментацию, либо не вызывает фрагментации вовсе. Но такое положение вещей не длится вечно. В итоге, фрагментация снова становится проблемой, и файлы данных и журналов необходимо снова дефрагментировать. В идеале дефрагментация должна выполняться регулярно.

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

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

Всеми перемещениями файлов при работе утилиты Diskeeper во время дефрагментации непосредственно управляет операционная система. Фактически, код операционной системы, выполняющий эту функцию, который первоначально был написан Executive Software, распределяет по приоритетам безопасности что может быть дефрагментировано, а что нет. Файлы SQL Server (например .LDF и .MDF) абсолютно безопасно дефрагментировать. Если в тот момент, когда Diskeeper посылает запрос операционной системе (посредством программного интерфейса - API) на перемещение файлов, ему попадутся файлы, которые не могут быть безопасно перемещены, он через них просто перескакивает без сообщений об ошибке или иных сигналов.

Как же узнать, что файлы вашего SQL Server физические фрагментированы? К счастью, это просто. Одной из функциональных возможностей Diskeeper является анализ фрагментации, с помощью которого можно посмотреть фрагментацию относящихся к SQL Server файлов. Как и дефрагментация, эта процедура может выполняться во время работы SQL Server.

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

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

Если имеются сомнения, просто установите Diskeeper и запустите его функцию анализа, это позволит Вам узнать, на сколько частей разбиты ваши файлы. Я уверен, что Вы будете очень удивлены полученными результатами. Я видел отчёты с сайтов, у которых файлы базы данных были разбиты более чем на 287000 частей!!!!

Статья взята с SQL-Server-Performance.Com © 2000 - 2004 SQL-Server-Performance.Com

Все права зарезервированы. Executive Software International- зарегистрированные торговые знаки или торговые знаки, принадлежащие Executive Software International, Inc. Все другие торговые знаки принадлежат их владельцам.

В дополнение к статье.

• Вот еще одна программа, которая дефрагментирует файлы, открытые и заблокированные для монопольного доступа, т.е. файлы, которые обходят другие дефрагментаторы:

www.microsoft.com/.../default.aspx

• Перевод статьи Мориса Льюиса "Победа над фрагментацией (документация)", в которой описано как обнаружить фрагментацию в SQL Server и как с ней бороться:

http://www.interface.ru/home.asp?artId=7047

Как происходит фрагментация файлов в операционных системах Windows XP / Windows Server 2003

http://www.winblog.ru/admin/1147764772-14120703.html

System Center Data Protection Manager Beta 2

На англоязычном сайте Майкрософт выложена версия System Center Data Protection Manager Beta 2.  Продукт предназначен для централизованного резервного копирования и восстановления файлов.

Ресурс на русском языке, посвященный System Center Data Protection Manager, находится здесь.  Также на сайте можно получить дополнительную информацию о работе DMP, например, по работе с SQL Server имеются следующие материалы:

 
• Официальное руководство по защите SQL Server с помощью DPM version 2 (en)
• Веб-трансляция: защита SQL Server с помощью System Center Data Protection Manager version 2 (en)
• Руководство по защите SQL Server с помощью System Center Data Protection Manager Version 2 (en)

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

AdventureWorks Light

В феврале Microsoft включила в состав поставляемых к SQL Server 2005 примеров: SQL Server 2005 Samples and Sample Databases (February 2007) облегчённую версию учебной базы данных AdventureWorksLT.msi, размер которой в дистрибутиве составляет 2251КБ.
Схема облегчённой базы проще, а саму базу легче развернуть и использовать для демонстрации примеров. Появление AdventureWorksLT было с энтузиазмом встречено в сообществе специалистов SQL Server, вот несколько ссылок на сообщения об этом событии в блогах:

• New sample database - AdventureWorks light

• New, simpler AdventureWorks sample database

• Welcome, AdventureWorks Light