SQL Server - объединение больших таблиц без блокировки данных
У меня есть очень большой набор данных (~3 миллиона записей), который необходимо объединять с обновлениями и новыми записями по ежедневному расписанию. У меня есть хранимая процедура, которая на самом деле разбивает набор записей на 1000 записей и использует MERGE команда с временными таблицами в попытке избежать блокировки действующей таблицы во время обновления данных. Проблема в том, что это не совсем помогает. Таблица по-прежнему "блокируется", и наш веб-сайт, который использует данные, получает тайм-ауты при попытке доступа к данным. Я даже попытался разделить его на 100 записей и даже попытался WAITFOR DELAY '000:00:5' чтобы увидеть, поможет ли это сделать паузу между объединением кусков. Это все еще довольно вяло.
Я ищу любые предложения, лучшие практики или примеры того, как объединять большие наборы данных без блокировки таблиц.
Спасибо
3 ответа
Измените свой интерфейс, чтобы использовать NOLOCK или READ UNCOMMITTED при выполнении выбора.
Вы не можете NOLOCK MERGE,INSERT или UPDATE, так как записи должны быть заблокированы для выполнения обновления. Тем не менее, вы можете заблокировать выборы.
Обратите внимание, что вы должны использовать это с осторожностью. Если грязные чтения в порядке, тогда продолжайте. Однако, если для чтения требуются обновленные данные, вам нужно пойти другим путем и выяснить, почему объединение записей 3M вызывает проблему.
Я бы поспорил, что большую часть времени тратится на чтение данных с диска во время команды слияния и / или работы с ситуациями нехватки памяти. Вам может быть лучше, просто вставив больше оперативной памяти в сервер базы данных.
В идеале было бы достаточно оперативной памяти для загрузки всей базы данных в память по мере необходимости. Например, если у вас есть база данных 4 ГБ, убедитесь, что у вас 8 ГБ ОЗУ.. на сервере x64, конечно.
Боюсь, что у меня совершенно противоположный опыт. Мы выполняли обновления и вставки, где исходная таблица имела только долю от числа строк в качестве целевой таблицы, которая составляла миллионы.
Когда мы объединили записи исходной таблицы по всему рабочему окну, а затем выполнили MERGE только один раз, мы увидели увеличение производительности на 500%. Я объясняю это тем, что вы платите за предварительный анализ команды MERGE только один раз, а не снова и снова в тесном цикле.
Кроме того, я уверен, что объединение 1,6 миллиона строк (исходных данных) в 7 миллионов строк (целевых), в отличие от 400 строк в 7 миллионов строк за более чем 4000 различных операций (в нашем случае), значительно улучшает возможности механизма SQL-сервера. Опять же, значительный объем работы заключается в анализе двух наборов данных, и это делается только один раз.
Другой вопрос, который я должен задать: хорошо ли вы знаете, что команда MERGE работает намного лучше с индексами как исходной, так и целевой таблиц? Я хотел бы отослать вас к следующей ссылке:
http://msdn.microsoft.com/en-us/library/cc879317(v=SQL.100).aspx
Исходя из личного опыта, основная проблема с MERGE заключается в том, что, поскольку он блокирует страницу, он исключает любой параллелизм в ваших INSERT, направленных на таблицу. Поэтому, если вы пойдете по этому пути, очень важно, чтобы вы упаковывали все обновления, которые попадут в таблицу, в одном устройстве записи.
Например: у нас была таблица, для которой INSERT занимал сумасшедшие 0,2 секунды на каждую запись, большую часть этого времени, по-видимому, тратили впустую на фиксацию транзакции, поэтому мы переключили это на использование MERGE, и некоторые быстрые тесты показали, что это позволило нам вставить 256 записей за 0,4 секунды или даже 512 за 0,5 секунды мы проверили это с генераторами нагрузки, и все, казалось, было в порядке, пока оно не достигло производительности, и все блокировалось до чертиков на блокировках страницы, что привело к гораздо более низкой общей пропускной способности, чем с отдельными INSERT.
Решение состояло в том, чтобы не только пакетировать записи от одного производителя в операции MERGE, но также и пакетировать от производителей, отправляющихся в отдельную БД, в одной операции MERGE через дополнительный уровень очереди (ранее также одно соединение на БД, но используя MARS для чередования всех обращений производителей к хранимой процедуре, выполняющей фактическую транзакцию MERGE), таким образом, мы смогли без проблем обрабатывать многие тысячи INSERT в секунду.
Наличие подсказок NOLOCK на всех ваших обращениях к интерфейсу абсолютно необходимо, всегда.