Нужны рекомендации по выходу за рамки с SqlBulkCopy на SQL Server

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

CREATE TABLE MainTable
(
   _id bigint IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    field1, field2, ...
)

Я опущу, как я собираюсь выполнять запросы, поскольку это не имеет отношения к моему вопросу.

Я написал прототип, который вставляет данные в эту таблицу с помощью SqlBulkCopy. Похоже, в лаборатории это сработало очень хорошо. Мне удалось вставить десятки миллионов записей со скоростью ~ 3К записей / сек (полная запись сама по себе довольно большая, ~ 4К). Поскольку единственный индекс в этой таблице - это автоинкремент bigint, я не заметил замедления даже после того, как было отправлено значительное количество строк.

Учитывая, что лабораторный SQL-сервер представлял собой виртуальную машину с относительно слабой конфигурацией (4 Гб ОЗУ, совместно используемая с дисковой системой других виртуальных машин), я ожидал получить значительно лучшую пропускную способность на физической машине, но этого не произошло, или, скажем так, прирост производительности был незначительным. Я мог бы, возможно, получить на 25% более быстрые вставки на физической машине. Даже после того, как я настроил RAID0 с 3 дисками, который работал в 3 раза быстрее, чем один диск (измерено с помощью программного обеспечения для тестирования), я не получил никаких улучшений. По сути: более быстрая подсистема накопителей, выделенный физический процессор и двойная оперативная память почти не привели к увеличению производительности.

Затем я повторил тест, используя самый большой экземпляр в Azure (8 ядер, 16 ГБ), и получил тот же результат. Таким образом, добавление дополнительных ядер не повлияло на скорость вставки.

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

• Изменение параметра SqlBulkInsert.BatchSize
• Одновременная вставка из нескольких потоков и настройка количества потоков
• Использование параметра блокировки таблицы в SqlBulkInsert
• Устранение задержки в сети за счет вставки из локального процесса с использованием драйвера общей памяти

Я пытаюсь увеличить производительность как минимум в 2-3 раза, и моя первоначальная идея заключалась в том, что добавление большего количества оборудования может помочь, но пока этого не происходит.

Итак, может ли кто-нибудь порекомендовать меня:

• Какой ресурс можно было заподозрить здесь узким местом? Как подтвердить?
• Есть ли методика, по которой я мог бы попытаться получить надежно масштабируемое улучшение массовой вставки, учитывая, что существует единственная система SQL-сервера?

ОБНОВЛЕНИЕ. Я уверен, что загрузить приложение не проблема. Он создает запись во временной очереди в отдельном потоке, поэтому, когда есть вставка, она выглядит следующим образом (упрощенно):

===>start logging time
int batchCount = (queue.Count - 1) / targetBatchSize + 1;
Enumerable.Range(0, batchCount).AsParallel().
    WithDegreeOfParallelism(MAX_DEGREE_OF_PARALLELISM).ForAll(i =>
{
    var batch = queue.Skip(i * targetBatchSize).Take(targetBatchSize);
    var data = MYRECORDTYPE.MakeDataTable(batch);
    var bcp = GetBulkCopy();
    bcp.WriteToServer(data);
});
====> end loging time

тайминги регистрируются, и часть, которая создает очередь, никогда не занимает значительного фрагмента

ОБНОВЛЕНИЕ 2. Я реализовал сбор, сколько времени занимает каждая операция в этом цикле, и его макет выглядит следующим образом:

• queue.Skip().Take() - незначительно
• MakeDataTable(batch) - 10%
• GetBulkCopy() - незначительно
• WriteToServer(data) - 90%

UPDATE3 Я разрабатываю для стандартной версии SQL, поэтому я не могу полагаться на разделение, поскольку оно доступно только в версии Enterprise. Но я попробовал вариант схемы разметки:

• создано 16 файловых групп (от G0 до G15),
• сделал 16 таблиц только для вставки (от T0 до T15), каждая из которых привязана к своей отдельной группе. Таблицы вообще не имеют индексов, даже не имеют кластеризованного идентификатора int.
• потоки, которые вставляют данные, будут циклически перебирать все 16 таблиц каждый. Это делает почти гарантией того, что каждая операция массовой вставки использует свою собственную таблицу.

Это позволило улучшить объемную вставку примерно на 20%. Ядра ЦП, интерфейс LAN, ввод-вывод накопителя не были максимизированы и использовались примерно на 25% от максимальной емкости.

ОБНОВЛЕНИЕ4. Думаю, сейчас он настолько хорош, насколько это возможно. Мне удалось довести пластинки до разумной скорости, используя следующие методы:

• Каждая массовая вставка помещается в отдельную таблицу, а затем результаты объединяются в основную.
• Таблицы воссоздаются свежими для каждой объемной вставки, используются замки таблиц.
• Использовал реализацию IDataReader отсюда вместо DataTable.
• Массовые вставки, сделанные от нескольких клиентов
• Каждый клиент обращается к SQL через индивидуальную гигабитную VLAN.
• Побочные процессы, обращающиеся к основной таблице, используют параметр NOLOCK
• Я проверил sys.dm_os_wait_stats и sys.dm_os_latch_stats, чтобы исключить разногласия

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

ОБНОВЛЕНИЕ 5: следующий элемент можно оптимизировать:

• Использовал реализацию IDataReader отсюда вместо DataTable.

Если вы не запустите свою программу на машине с большим количеством ядер ЦП, она может потребовать некоторого повторного факторинга. Поскольку для генерации методов get / set используется отражение, это становится основной нагрузкой на ЦП. Если производительность является ключевым фактором, она увеличивает производительность, когда вы кодируете IDataReader вручную, так что он компилируется вместо использования отражения