Почему я должен заставить порядок с этими запросами иерархии /

Question

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

select  c.CategoryID, courses.MarketID, count(distinct courses.CourseID) NumberOfCourses
from    Category c
        join CategoryHierarchy tch on tch.HierarchyKey like '%~' + cast(c.CategoryID as varchar) + '~%'
        join vLiveEvents courses on tch.CategoryID = courses.CategoryID
where   courses.MarketID is not null
group by c.CategoryHumanID, courses.MarketID

Когда я запускаю это как есть, это может занять почти две минуты, однако, если я добавлю подсказку Option (Force Order) тогда это займет всего несколько секунд. Итак, мой вопрос: я делаю что-то не так, что приводит к тому, что SQL создает плохой план, или движок SQL на самом деле просто не хорош для оптимизации иерархических соединений, как это?

Я попытался включить план sql, но он слишком длинный, и ТАК не позволяет мне иметь столько символов. Я рад поделиться этим, хотя, если кто-нибудь может сказать мне, как это сделать.

РЕДАКТИРОВАТЬ: Я думаю, вероятно, не все знают, как работают эти виды иерархий. Ключ иерархии будет выглядеть примерно так: ~1234~5678~9123~, где 1234 является родителем 5678, который является родителем 9123. Путем сравнения идентификаторов CategoryID я могу включить все дочерние категории в результаты.

Answer 1

Начиная с SQL Server 2016+, была добавлена ​​функция хранилища запросов для мониторинга производительности. Это дает представление о выборе плана запроса и производительности.

Это также предоставляет возможность форсировать план.

Это не полная замена трассировки или расширенных событий, но по мере развития от версии к версии мы могли бы получить полнофункциональное хранилище запросов в будущих выпусках SQL Server. Основной поток Query Store

1. Существующие компоненты SQL Server взаимодействуют с хранилищем запросов с помощью диспетчера хранилища запросов.
2. Диспетчер хранилища запросов определяет, какое хранилище следует использовать, а затем передает выполнение этому хранилищу (статистика плана или времени выполнения или статистика ожидания запроса)
   • Хранилище планов - сохранение информации о плане выполнения
   • Runtime Stats Store - Сохранение информации статистики выполнения
   • Query Wait Stats Store - Сохраняющаяся информация статистики ожидания.
3. Хранилище Plan, Runtime Stats и Wait использует хранилище запросов в качестве расширения SQL Server.

1. Включение хранилища запросов: хранилище запросов работает на уровне базы данных на сервере.

   • По умолчанию хранилище запросов неактивно для новых баз данных.
   • Вы не можете включить хранилище запросов для мастера или tempdb база данных.
   • Доступен DMV

     sys.database_query_store_options (Transact-SQL)

2. Сбор информации в хранилище запросов. Мы собираем всю доступную информацию из трех хранилищ, используя Query Store DMV (представления управления данными).

   • Хранилище плана запросов. Сохраняет информацию о плане выполнения и отвечает за сбор всей информации, связанной с компиляцией запроса.

     sys.query_store_query (Transact-SQL) sys.query_store_plan (Transact-SQL) sys.query_store_query_text (Transact-SQL)

   • Хранилище статистики времени выполнения: Сохраняет информацию статистики выполнения, и, вероятно, это наиболее часто обновляемое хранилище. Эта статистика представляет данные выполнения запроса.

     sys.query_store_runtime_stats (Transact-SQL)

   • Query Wait Stats Store: сохранение и сбор статистических данных ожидания.

     sys.query_store_wait_stats (Transact-SQL)

ПРИМЕЧАНИЕ. Хранилище статистики ожидания запросов доступно только в SQL Server 2017+