Одна таблица 400 ГБ, один запрос - нужны идеи настройки (SQL2005)

Почему вы сгруппировали по первичному ключу?
Какие столбцы могут иметь значение NULL?
Какова длина VARCHAR?
Что дает план запроса сейчас?

Вы мешаете нам давать бессмысленные названия столбцов.

Даже если кластеризованный индекс правильный, более избирательное поле должно быть первым.

Я мог бы давать рекомендации на основе недостаточной информации, но некоторая помощь была бы лучше.

Помогите мне больше узнать о таблице. если ваш PK - k1, k2, вам не нужно выбирать какой-либо другой столбец, чтобы получить полностью уникальную запись.

Вы хотите сказать, что с k1 по 7 это ПК? Если это так, объявите его как таковой, и это будет кластерный индекс. Производительность запросов должна резко повыситься.

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

Обновлено: у меня такое чувство, что я не одинок в своем подозрении, что лучшее место для начала оптимизации - это дизайн вашей физической таблицы. Вы контролируете это? Не зная, что хранит каждый столбец, я не могу предложить очень конкретных идей, но следует очень общий подход: поместите K1,3,4,5 & 6 (k2, похоже, напрямую связано со значениями в вашей таблице) в отдельной таблице с единственным уникальным int в качестве PK. Затем создайте отношение FK обратно к этой таблице. Вы PK в основной таблице затем включите это поле, k2 и k7. Теперь оптимизатор вашего запроса выполнит довольно недорогой поиск в вашей новой таблице, вернет одну запись, а затем выполнит поиск индекса в вашей основной таблице только с помощью PK.

Используйте профилировщик SQL, чтобы определить, какие индексы создавать, он предназначен для обработки этой информации за вас и предлагает улучшенные профили выполнения.

У вас есть внешние ключи на k3, k4?

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

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

В:

SELECT TOP(g) d1 
FROM table WITH(NOLOCK)  
WHERE k1 = a  WHERE k2 = b  WHERE k3 = c  WHERE k4 = d  WHERE k5 = e  WHERE k6 = f  
ORDER BY k7

Что, я полагаю, должно быть

SELECT TOP(g) d1 
FROM table WITH(NOLOCK)  
WHERE k1 = a AND k2 = b  AND k3 = c AND k4 = d AND k5 = e AND k6 = f 
ORDER BY k7

Вероятно, существует какой-то набор данных, который немедленно сокращает набор записей, скажем, с 10 миллионов строк до 10 000.

например

SELECT TOP(g) d1 
FROM (SELECT * 
      FROM table k1=a AND k2=a WITH(NOLOCK)) 
WHERE AND k3 = c AND k4 = d AND k5 = e AND k6 = f 
ORDER BY k7

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

У администраторов баз данных, вероятно, есть другие, лучшие решения!

Вам необходимо создать индекс, который как можно быстрее уменьшит количество возможных возвращаемых строк.

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

k7 также следует проиндексировать, так как это значительно увеличит скорость предложения orderby.

Вам также может потребоваться поэкспериментировать (я знаю, я знаю, вы сказали, что не экспериментируйте, но это может помочь ...) с созданием индекса с несколькими столбцами в следующем порядке: k4, k1, k2, k3. Это, опять же, сделано для того, чтобы как можно быстрее сократить количество возможных возвращаемых строк.

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

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

Похоже, вам нужны только самые ранние записи "g"? Может быть, только самые свежие записи "g"?

В основном вы хотите, чтобы ваш запрос читал только самые последние / самые старые записи. Вы же не хотите запрашивать все 400 ГБ, не так ли? В этом случае вы можете рассмотреть возможность архивирования большей части 400 ГБ или сохранения последних вставленных записей в «текущей» таблице, которую вы можете запросить. Вы можете поддерживать текущие записи в текущей таблице с помощью двойной вставки или с помощью триггера в таблице (дрожь). Но основная предпосылка состоит в том, что вы запускаете свой запрос к как можно меньшей таблице. По сути, это разбиение таблицы бедняками.

Прежде всего, проведите день с SQL Profiler, работающим в фоновом режиме. В конце дня сохраните данные трассировки в файл, чтобы мастер оптимизации обработал их и оценил ваш текущий индекс. Это должно сказать вам, может ли изменение проиндексированных полей, порядка сортировки и т. д. Дать вам значительный выигрыш. Не позволяйте мастеру вносить изменения. Если процентное увеличение производительности выглядит значительным (> 30% ИМХО), продолжайте вносить изменения самостоятельно.

Ваш индекс должен стать большим. Вы можете запланировать работу (на ночь, пару раз в неделю), чтобы сделать следующее:

• Перенести данные старше определенного возраста в таблицу истории
• дефрагментировать индекс
• пересчитать метрики

Это позволит ускорить процесс после настройки индексов.

Добавьте один индекс со столбцами k1-k6 в нем; это должно быть лучшим.

Кроме того, если вы можете запускать sp_updatestats перед каждым запросом.

Трудно дать вам содержательный ответ. Вы смотрели стоимость дискового ввода-вывода? Где вы храните файлы базы данных - возможно, это тормозит ввод-вывод? Здесь так много переменных, которые могут повлиять на производительность. Возможно, это время, затрачиваемое вашим пользовательским интерфейсом, или что нужно для отображения данных, а может, это время, затрачиваемое Сетью?

Возможно, самый простой способ - где вы увидите наибольший выигрыш - это разбить таблицу на части - если вы используете Enterprise Edition SQL Server 2005.

Опять же, без доступа к реальным планам запросов, статистика perfmon очень сложно сказать вам, в чем именно проблема. Ваш вопрос просто не дает нам достаточно, чтобы продолжить - и все это лишь предположение.

Покажите результат плана запроса - любое приключение по настройке, которое не начинается, является злоключением.

Я бы использовал мастер настройки индекса, чтобы получить лучший ответ.

Однако, если бы это был я, я бы попробовал индексировать на K3, K4 (в том порядке, в котором вы чаще всего запрашиваете) (у вас уже есть индексированные K1 и K2) и отдельный индекс на K7. Я не верю, что добавление логических полей улучшило бы производительность индекса.

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

Вот что бы я сделал:

• Не создавайте отдельные индексы для каждого столбца. Вы будете тратить место зря, и они вам не сильно помогут (если вообще)
• Оставьте свой первичный ключ в покое, но создайте кластерный индекс в столбце даты, поскольку это то, что вы используете в ORDER BY. Таким образом, ядро ​​базы данных начнет сканировать кластерный ключ, сравнивать столбцы с предоставленными вами значениями и выводить строки, удовлетворяющие условиям.
• Для этого не нужны никакие другие индексы. Я считаю, что даже 100 значений из 100 миллионов для k4 будут считаться оптимизатором плохой селективностью (хотя вы можете попробовать это по крайней мере).
• если вы выберете на основе некоторых диапазонов дат, например Только данные за последний месяц, неделю, год и т. д. вы можете захотеть разбить вашу большую таблицу на «меньшие» на основе столбца даты. Эти столбцы с 10 значениями также будут хорошими кандидатами для ключей раздела.

Кстати, вы указываете весь ПК в запросе - предполагая, что AND в WHERE - это выберет ровно 1 строку.

Я бы сказал, что 8 ГБ ОЗУ для стола на 400 ГБ недостаточно. У сервера нет шансов сохранить соответствующие данные в памяти, если только один индекс занимает 5-8 ГБ. Таким образом, существует множество операций чтения с жесткого диска, которые замедляют выполнение запроса.

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

Обновлено: Чтобы убедиться, в чем ваше реальное узкое место, запустите Windows Монитор производительности.

Спасибо всем за вашу помощь.

Я сделал 3 исправления ошибок в исходном сообщении.

1) ГДЕ должно было быть И.

2) k4 должно было быть ДЕНЬГИ, а не ВАРЧАР. Кроме того, k1 имеет длину 3.

3) k2 не должно быть в предложении WHERE. Как правильно указывает Дофледорфер, нет смысла иметь какие-либо другие инструкции WHERE, кроме полного первичного ключа.

Вот ответы на ваши вопросы:

Why have you clustered on the primary key?

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

Which columns can be NULL?

Никто.

What are the VARCHAR lengths?

Я ошибся с типами столбцов. Единственный оставшийся VARCHAR имеет длину 3.

What does the query plan give you now?

Размещено в следующем посте.

Help me understand more about the table. if your PK is k1,k2, you shouldn't have to select by any other column to get a completely unique record. This was a mistake. The k2 part of the PK is not in the WHERE clause.

Knowing why you need around a million records returned might help me provide a better solution.

База данных содержит ежедневные записи (столбец d1 TEXT) или данные. Людям необходим доступ к большим объемам этих данных для создания собственных отчетов. Им необходимо отфильтровать его по количеству значений и отсортировать по времени.

It looks like you only want the earliest "g" records? Maybe only the most recent "g" records?

Да, самый последний. Но у меня их определенное количество. Я не знаю заранее дату начала.

Do you have foreign keys on k3, k4? No. This is the only table int the DB.

Комментарии:

Even if the clustered index is proper, the more selective field should come first.

Более избирательный индекс не используется в предложении WHERE (редактирование записи!). Итак, я так понимаю, в таком случае это не должно быть первым?

You may want to Move data over a certain age to a history table

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

You may want to defrag the index

В настоящее время у меня нет. Будем разбираться в этом, если эта ветка окажется плодотворной.

Add a single index with columns k1-k6 in it; that should be the best.

Может ли кто-нибудь еще прокомментировать это предложение? Liggett78 заметил, что это удвоит размер БД, не сильно помогая из-за сортировки по столбцу даты. Обратите внимание, что столбец DATE отсутствует в предложении WHERE, он используется только для упорядочивания данных.

Try turning k1, k2 into ints and making them foreign keys, it'll use a lot less storage for one, I'd have thought and I think it should be quicker (though I may be wrong there, I guess SQL Server caches these values).

k2 - bigint (ошибка в исходном посте). Таким образом, изменение k1 на int (из VARCHAR (3)) - это вариант. Неужели мы действительно думаем, что это будет иметь большое значение? И действительно ли люди думают, что разделение таблицы на k1, k2, d1 и k1, k2, k3, k4, k5, k7 и использование внешних ключей улучшит ситуацию?

One good tip to improve query speeds is to put in a sub-query that cuts down your recordset size to a more manageable one. There is likely to be some set of data that immediately cuts the recordset down from, say 10 million rows, to 10,000.

e.g. SELECT TOP(g) d1 FROM (SELECT * FROM table WHERE k1=a WITH(NOLOCK)) WHERE AND k3 = c AND k4 = d AND k5 = e AND k6 = f ORDER BY k7

Очень интересно. Это действительно поможет? Похоже, что SQL Server был бы очень глуп, если бы он сам не вырезал данные аналогичным образом.

Perhaps it is the time taken by your UI or whether to display the data, perhaps it is the time taken by the Network ?

Нет UI. Конечно, есть сетевые проблемы с перемещением данных, но меня беспокоит только время, необходимое для того, чтобы запрос начал возвращать результаты (я использую считыватель данных ADO.NET) на данный момент - по одному за раз :)

.. [to] see the most gains ... partition the table

Будет ли кластерный индекс иметь такой же эффект?

Leave your primary key alone, but create a clustered index on your date column, since this is what you use in ORDER BY. That way the database engine would begin to scan the clustered key, compare columns with your supplied values and output rows that satisfy the conditions.

Похоже на разумный план! Есть другие спонсоры?

Подводя итог предложениям:

1) Создайте отдельные индексы для всех ключей: большинство людей голосует против?

2) Создайте отдельные индексы для ключей с наиболее разными значениями.

3) Создайте индекс с несколькими столбцами для некоторый столбцов, сначала столбцы с наиболее различными значениями.

4) Закинуть на него оперативку.

Разделение и распараллеливание - проверьте план запроса, если он не показывает, что запрос распараллелен, узнайте, почему это не так. Возможно, вам потребуется разбить запрос на несколько этапов, а затем свести результаты воедино.

Если это так, разделите данные на несколько физических дисков, добавьте больше ядер. У него много работы, после того как вы его чертовски проиндексировали, физическая сила - это все, что вам осталось.

Не думайте, что SQL Server просто использует все ваши ядра. Как правило, вы должны правильно разработать свой запрос, чтобы можно было использовать несколько ядер. Проверьте свойства первого узла в плане запроса, чтобы увидеть DOP (степень параллелизма). Если его 1, вы зря тратите ядра ...

Как я намекал в комментарии, я сделал это с одной таблицей Oracle размером около 8 ТБ, состоящей из более чем двух миллиардов строк, растущих со скоростью сорок миллионов строк в день. Однако в моем случае пользователями было два миллиона (и это число постоянно растущих) клиентов, обращающихся к этим данным через Интернет, круглосуточно, и буквально ЛЮБАЯ из строк могла быть доступна. Да, и новые строки нужно было добавить в течение двух минут в реальном времени.

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

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

Вы говорите, что не можете разделить данные, потому что используются все данные: НЕВОЗМОЖНО! Ваши пользователи не могут пролистывать миллион строк в день или сто миллионов строк в сумме. Итак, узнайте, как ваши пользователи ДЕЙСТВИТЕЛЬНО используют данные - посмотрите на каждый запрос в этом случае.

Что еще более важно, мы не говорим, что вы должны УДАЛИТЬ данные, мы говорим РАЗДЕЛИТЬ данные. Клонируйте структуру таблицы в несколько таблиц с одинаковыми названиями, возможно, в зависимости от времени (возможно, один месяц на таблицу). Скопируйте данные в соответствующие таблицы и удалите исходную таблицу. Создайте представление, которое выполняет объединение новых таблиц с тем же именем, что и исходная таблица. Измените обработку вставки, чтобы настроить таргетинг на новейшую таблицу (при условии, что это уместно), и ваши запросы должны по-прежнему работать с новым представлением.

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

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

Ожидайте, что у вас не будет возможности использовать уникальные индексы: они не масштабируются за пределы одного-двух миллионов строк. Возможно, вам также придется изменить некоторые другие тактики / советы. С сотней миллионов строк и 400 ГБ вы вошли в другую область обработки.

Помимо этого, используйте другие предложения - проанализируйте фактическую производительность, используя множество инструментов, уже доступных в SQL Server и ОС. Примените множество хорошо известных техник настройки, которые легко доступны в Интернете или в книгах.

Однако НЕ экспериментируйте! При таком большом количестве данных у вас нет времени на эксперименты, и риск слишком велик. Внимательно изучите доступные методы и детали своей реальной производительности, затем выбирайте по одному шагу за раз и дайте каждому несколько часов или дней, чтобы выявить его влияние.

Ваш план запроса в основном показывает следующее:

• Первая операция - поиск по кластеризованному индексу со сравнениями по k1, handId ?, d1, k3-k6.
• Во-вторых, полное сканирование индекса на k1, handId? и k7
• Третий - это, конечно, соединение для построения окончательного набора результатов.
• Сортировка ЗАКАЗАТЬ ПО
• TOP n (фильтр)

План предлагает индекс, который должен улучшить перми на 81% - k1, k4, k5, k6, k3 + включают d1 и k7. Я не знаю, сколько времени потребуется, чтобы построить такой индекс и увидеть результаты, но, как я здесь прокомментировал, он фактически удвоит размер вашей таблицы просто потому, что почти каждый столбец присутствует в индексе. Также вставки будут медленнее.

Как предполагали многие, разделение - лучшая стратегия здесь, например сделать одну таблицу, например, иметь значения k3 от 1 до 3, другую - от 4 до 7, а третью - от 8 до 10. В SQL Server Enterprise секционирование выполняется с использованием ограничения CHECK для этого столбца, оптимизатор запросов определит, какая таблица выходит из n для сканирования / поиска в зависимости от значения параметра для столбца.

Звучит весело.

Несколько вопросов:

• Почему вы выбрали именно эти типы? varchar, деньги, bigint, int, bool? есть причина или просто хотите добавить немного веселья?
• Может быть, мы сможем взглянуть на инструкцию insert, TSQL или bulkinsert?
• Можете ли вы сказать, как часто происходит вставка (массовая или случайная?)
• Содержит ли поле DateTime дату вставки?
• Как вы к этому пришли? (Один человек в день думает или команда из 20 человек работает как сумасшедшая последние три месяца?)

Мне кажется важным несколько фактов:

• Вы вставляете миллион строк каждый день
• Вам нужен только последний миллион данных

Мне пришло в голову несколько замечаний:

• если вас интересуют только последние данные, удаление / архивирование бесполезных данных может иметь смысл (начинать с нуля каждое утро)
• если есть только один «модуль вставки» и только один «считыватель», вы можете захотеть переключиться на специализированный тип (hashmap / list / deque / stack) или что-то более сложное на языке программирования.

ХОРОШО,

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

1. K1: 10 различных значений
2. K3: 100 различных значений
3. k4: 10 разных значений
4. k5: 2 разных значения
5. k6: 2 разных значения

Если мы сделаем составной ключ из k1, k3, k4, k5 и k6, это означает, что этот ключ будет иметь только 40 000 различных комбинаций (10 * 100 * 10 * 2 * 2). Это означает, что если у нас есть 100000000 записей, деленных на 40000, статистически у нас будет подмножество из 2500 различных записей, для которых будет применяться последовательный поиск для выполнения других ограничений предложения WHERE.

Если мы экстраполируем этот результат и сравним его с текущим временем выполнения (30 минут), с ключом (k1), который статистически генерирует подмножество из 10 миллионов различных записей, мы получим:

10,000,000 записей * X секунд = 30 * 60 секунд * 2,500 записей

=> X сек = 0,45 сек

Неплохо, да? Еще лучше. Как насчет того, чтобы исключить k5 и k6 из индекса compund? Статистически у нас будет подмножество из 10 000 различных записей, по которым будет выполняться последовательный поиск. Теоретически, сколько времени это займет? Посмотрим:

10 000 000 записей * X сек = 30 * 60 * 10 000 записей

=> X сек = 1,8 сек

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

Надеюсь это поможет,

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

Если вы не можете воспользоваться некоторыми конкретными знаниями о ваших значениях (возможно, k5 истинно, только если k4 <0 или что-то в этом роде), вы в значительной степени смотрите на сканирование кластерного индекса. Можно также сделать это поле, по которому вы заказываете.

Глядя на небольшое количество различных значений в k3 - k6, вам, вероятно, нужно будет прочитать <1,5 миллиона строк, чтобы получить 1 миллион лучших. Вероятно, это лучшее, что вы собираетесь сделать - тем более, что для любого другого плана вам все равно потребуется заказать k7 для оценки вашего предложения TOP.

Рассматривали ли вы создание столбца суррогатной идентификации (тип bigint) и его использование в качестве кластеризованного индекса? Затем создайте свой первичный ключ как некластеризованный уникальный индекс.

С таблицей такого размера вполне возможно, что фрагментация индекса и страницы является большой проблемой для производительности. Суррогатный кластеризованный индекс гарантирует, что все вставки находятся в конце таблицы, что может почти полностью устранить фрагментацию страницы (если строки не будут удалены). Меньше фрагментации страниц == больше страниц на ввод-вывод, что очень хорошо.

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

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

После того, как вы это реализовали, подумайте (иначе, попробуйте и измерьте ;-) добавление некластеризованного индекса в столбец k7.

что такое D1, это десятичный или длинный символ, пожалуйста, не могли бы вы это пояснить. Я бы порекомендовал создать кластерный индекс как (K7, k2, k1, k4), а затем создать дополнительный индекс для (k3) (создание индекса для двух значений bool в основном бессмысленно, если только распределение значений не составляет около 30%. / 70% между значениями, или, если ваша таблица очень широкая, если d1).

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

Похоже, вы не используете свой кластерный индекс в полной мере и имеете МНОГО дублированных данных.

Кажется, что ваш кластерный индекс построен примерно так:

create clustered index IX_Clustered on Table(k1 ASC, k2 ASC)

Однако ваши другие столбцы k * представляют только 40 000 возможных перестановок.

10 (k1) * 10 (k3) * 100 (k4) * 2 (k5) * 2 (k6) = 40,000

Вы должны вывести уникальные комбинации этих 4 ключей в отдельную таблицу и присвоить каждому из них уникальный int (первичный ключ «newPK»).

Извините, пожалуйста, за псевдокод:

create table SurrogateKey(
  newPK int -- /*primary key*/
, k1, k3, k4, k5, k6
)

constraint: newPK is primary key, clustered
constraint: k1, k3, k4, k5, k6 is unique

В этой таблице будет всего 40 000 строк, и поиск по первичному ключу newPK будет очень быстрым. Затем вы можете найти одно целое число в своей большой таблице.

Ваша существующая таблица должна быть изменена, чтобы в ней были следующие столбцы:

• новыйПК
• k2 (который на самом деле не является ключом, скорее всего, это просто порядковый номер)
• d1
• k7 дата и время

Учитывая вышеизложенное, вы можете изменить свой кластерный индекс на:

create clustered index IX_Clustered on Table(newPK ASC)

И вы можете искать по этому пути. Это гарантированно будет быстрее, чем то, что делает ваш запрос сейчас (производительность эквивалентна сканированию индекса + поиску ключа).

declare @pk int
select @pk = newPK 
from SurrogateKey
where
      k1 = @k1
  and k3 = @k3
  and k4 = @k4
  and k5 = @k5
  and k6 = @k6

select top(g1) d1, k2, k7
from Table with(read uncommitted)
where newPK = @pk
order by k7

Ваш оператор вставки также должен быть изменен для запроса / вставки таблицы SurrogateKey.

Можешь попробовать:

alter table MyTable
    add constraint PK_MyTable
        primary key nonclustered (k1, k2)
create clustered index IX_MyTable
    on MyTable(k4, k1, k3, k5, k6, k7)
    --decreasing order of cardinality of the filter columns

Это гарантирует, что ваши дублирующие вставки и дальше останутся без ошибок.

Это также может указывать SQL Server на фильтрацию по (k1, k3, k4, k5, k6) и упорядочивание по (k7 asc) за один проход, позволяя SQL Server передавать результаты запроса в потоковом режиме без промежуточного этапа сортировки сначала миллиона результатов. Как только SQL Server найдет первую строку, соответствующую (k1, k3, k4, k5, k6), следующие миллион строк или около того будут соответствовать одному и тому же фильтру и уже будут отсортированы по (k7 asc). Вся фильтрация и упорядочение будут выполняться вместе на основе кластерного индекса.

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

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