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

У меня есть сервер SQL, настроенный с репликацией слиянием на 800 мобильных клиентов, работающих под управлением SQL CE.

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

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

Эта вставка заняла 14 часов из-за того, что она постоянно блокировалась мобильными устройствами, пытающимися синхронизироваться.

Есть ли у кого-нибудь какие-либо советы о том, как мы можем избежать блокировок на вставках и как ускорить весь процесс?

------ Обновлять -----

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

insert into dbo.MSmerge_current_partition_mappings with (rowlock) (publication_number, tablenick, rowguid, partition_id)
            select distinct 1, mc.tablenick, mc.rowguid, v.partition_id
            from dbo.MSmerge_contents mc with (rowlock) 
            JOIN dbo.[MSmerge_JEMProjectME_PromotionResource_PARTITION_VIEW] v with (rowlock) 
            ON mc.tablenick = 286358001
            and mc.rowguid = v.[rowguid]
            and mc.marker = @child_marker 
            and v.partition_id in (select partition_id from dbo.MSmerge_current_partition_mappings cpm with (rowlock) JOIN
                dbo.MSmerge_contents mc2 with (rowlock)
                ON cpm.rowguid = mc2.rowguid
                and mc2.marker = @marker)
            where not exists (select * from MSmerge_current_partition_mappings with (readcommitted, rowlock, readpast) where 
                publication_number = 1 and 
                tablenick = 286358001 and
                rowguid = v.[rowguid] and
                partition_id = v.partition_id)

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


sql sql-server-2008 merge-replication
person Gavin Mannion    schedule 22.10.2012    source источник
comment
1. Какие уровни изоляции используются вставками и синхронизациями? Обычно вставка не должна быть проблемой. 2. Сколько у вас индексов? 3. Есть ли у вас неинкрементные индексы, из-за которых записи вставляются в середину древовидной структуры, а не в конец? 4. У вас есть кластеризованный индекс?   -  person Farfarak    schedule 23.10.2012
comment
1. Не уверен насчет уровней изоляции... это просто стандартный оператор вставки - 2. Мы вставляем в таблицу А, которая имеет 1 стандартный индекс... однако триггеры репликации слиянием вставляют ее во многие другие таблицы - 3. Не в таблицу, которую мы вставляются в - 4. Нет   -  person Gavin Mannion    schedule 23.10.2012
comment
1. У вас есть запросы, которые вызываются во время репликации, возможно, процесс репликации блокирует всю таблицу в вашей куче (у вас нет кластеризованного индекса) 2. Есть ли уникальный ключ в таблице?   -  person Farfarak    schedule 23.10.2012
comment
Да, процессы и другие запросы на репликацию поступают все время. У нас есть около 800 мобильных клиентов, которые реплицируются около 10 раз в день каждый, поэтому они часто перекрывают друг друга, и задания... .. только что перепроверили. На столе нет уникального ключа   -  person Gavin Mannion    schedule 23.10.2012
comment
Я бы порекомендовал иметь кластеризованный индекс для таблицы, который является последовательным, и убедиться, что репликация выбирает на его основе, а репликация и вставка не должны выполняться на уровне изоляции транзакций SERIALIZABLE, таким образом, у вас не будет RangeS-S или RangeI- N блокировок, которые, скорее всего, являются причинами взаимоблокировок, если у вас есть граф взаимоблокировок.   -  person Farfarak    schedule 23.10.2012
comment
Я начал отслеживать индексы и профилировщики и нашел что-то странное, что я включил в вопрос.   -  person Gavin Mannion    schedule 23.10.2012

Ответы (2)


arrow_upward
8
arrow_downward

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

Решено 30 минут назад, добавив два индекса:

CREATE NONCLUSTERED INDEX [IX_MSmerge_current_partition_mappings_PERF1] ON [dbo].[MSmerge_current_partition_mappings] 
(
    [partition_id] ASC
)
INCLUDE ( [rowguid]) 


CREATE NONCLUSTERED INDEX [IX_msmerge_contents_PERF1] ON [dbo].[MSmerge_contents] 
(
    [marker] ASC
)
INCLUDE ( [rowguid])

Я надеюсь, что это может помочь вам, это помогло нам сократить время запроса с 5 минут до 10 секунд.

-- Через пару часов...

Мой коллега нашел еще один индекс, который еще больше увеличил производительность еще на 75%:

CREATE NONCLUSTERED INDEX [IX_MSmerge_current_partition_mappings_PERF2] ON [dbo].[MSmerge_current_partition_mappings] 
(
    [rowguid] ASC,
    [partition_id] ASC
)

Определить отсутствующий индекс. Вы можете использовать следующий сценарий для определения отсутствующих индексов, отсортированных так, чтобы тот, который, как ожидается, повысит производительность, окажется вверху (существует множество таких сценариев, этот был заимствован из http://www.sherbaz.com/category/sqlserver/)

SELECT  sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
,  'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') + CASE WHEN mid.inequality_columns IS NULL 
                THEN ''  
    ELSE CASE WHEN mid.equality_columns IS NULL 
                    THEN ''  
        ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL 
                THEN ''  
    ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns 
    FROM sys.dm_db_missing_index_group_stats AS migs 
            INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle 
            INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle AND mid.database_id = DB_ID() 
            INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID 
    WHERE     (migs.group_handle IN 
        ( 
        SELECT     TOP (500) group_handle 
            FROM          sys.dm_db_missing_index_group_stats WITH (nolock) 
            ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC))  
        AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable')=1 
    ORDER BY 2 DESC , 3 DESC
person David Abrahamsson    schedule 31.10.2012

arrow_upward
0
arrow_downward

В конце концов, индексы пока только помогли, похоже, что репликация слиянием не очень хорошо настроена в этой системе.

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

В качестве примечания мы должны были сделать базовое обновление

Обновить набор таблиц Column1 = Column1

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

person Gavin Mannion    schedule 24.06.2013