Постановка таблицы дизайна для производительности
В моем хранилище данных SQL Azure есть типичный шаблон звездочки. Данные сначала выгружаются в промежуточные таблицы через фабрику данных, затем она вызывает основную процедуру, которая вызывает другие процедуры для преобразования данных в соответствующий формат, а затем очищает промежуточные таблицы для этого фрагмента данных.
Должны ли эти промежуточные таблицы иметь индексы? Должны ли они иметь статистику? Я недавно обновился до Gen 2, но у меня не включена функция автоматического создания статистики. Я беспокоюсь о том, что статистика будет создаваться, но не обновляться, и в конечном итоге это приведет к замедлению работы больше, чем что-либо еще.
Для большего контекста существует процедура перестроения индексов и обновления статистики, которая запускается всю ночь, один раз в день. Процесс загрузки данных выполняется ежечасно.
1 ответ
Учитывая, что это промежуточные таблицы, наибольшее влияние будет иметь следующее.
Там, где это возможно, используйте распределение хэшей. Это даст наилучшую производительность при обработке таблицы на последующих шагах. В то время как документация иногда предлагает распределение round_robin, и это немного быстрее для приема, следующий запрос к таблице будет медленнее.
Всегда используйте статистику. Я предлагаю создавать их вручную, исходя из ожидаемого использования, для большей предсказуемости производительности ELT. Если вы не создадите и не обновите статистику, в будущем вы получите ужасную производительность. Если вы не хотите вручную управлять статистикой, обязательно включите автоматическую статистику.
Рассмотрим использование структур таблиц HEAP vs CLUSTERED COLUMNSTORE для промежуточных таблиц. Как правило, промежуточные таблицы обрабатываются по всей строке, и вы можете обнаружить, что ваша производительность лучше на промежуточном уровне, если вы используете HEAP. Это необходимо проверить на ваших данных, поскольку кэширование Gen2, которое дает гораздо большую производительность, не относится к таблицам кучи.
Определенно создайте таблицы фактов и измерений в виде кластеризованных индексов columnstore. Хеш распространяет ваши факты и реплицирует ваши измерения (если у вас нет миллиардов измерений строк, в этом случае распределение хешей может быть более подходящим).
Если вы используете алгоритмы CTAS, ваша потребность в некластеризованных индексах должна быть очень низкой. Обычно я добавляю индексы только тогда, когда вижу проблему с производительностью запроса, которую я не могу решить никаким другим способом.
Наконец, убедитесь, что вы используете разумный DWU и Resource Class. Общее практическое правило заключается в том, что вы не должны использовать ELT ниже DWU500 и LARGERC. Если вы этого не сделаете, вы обнаружите, что у вас плохие кластеризованные индексы columnstore, что приведет к будущим проблемам с производительностью.
Некоторая информация с моей стороны - Ваша таблица фактов должна быть разделена. на самом деле у вас должна быть работа, которая автоматически создает разделы. насколько велика таблица фактов? если ваша таблица фактов становится слишком большой, то, исходя из ваших требований, вы можете подумать о введении архивации старой таблицы, если она не требуется в таблице фактов.