Здравствуйте, это первое сообщение из серии тем, связанных с SQL Server for Business Intelligence in 2022, (если вы эксперт по SQL, я уверен, что вы узнаете как минимум что-то новое из этого), поскольку почти каждая система в мире управляется какой-то базой данных, поэтому эта серия сообщений поможет тем из вас, кто заинтересован в получении SQL Server for Business Intelligence in 2022.
Система, которая существует во всем мире, управляется некоторой базой данных, поэтому эта серия постов поможет тем, кто заинтересован в том, чтобы попасть в этот мир.
Для этого я использую базу данных, предоставленную Microsoft
AdventureWorks2019.
Рассмотрим таблицу FackProductInventory, она имеет два первичных ключа (ProductKey, DateKey), который также является кластеризованным индексом, и в зависимости от того, как вы определите этот индекс, информация будет храниться на диске.
Как я уже упоминал в заголовке статьи, SQL не хранит строки или столбцы, он хранит только 8 KB страниц, упорядоченных в соответствии с кластеризованным индексом, созданным по первичному ключу.
Теоретически в каждой из этих страниц размером 8 кб сохраняются все столбцы таблицы, однако иногда у нас есть поля с типом данных nvarchar(max), содержимое которых не может быть сохранено в 8 кб странице, поэтому SQL сохраняет указатель на это поле и сохраняет его в другой странице размером 8 кб.
Итак, база данных — это не более чем группировка этих 8kb страниц, поэтому SQL Server не ищет «строку» или «столбец», и когда он хочет найти, вставить, обновить что-то конкретное, SQL Server должен «обнаружить» в какой 8kb странице
хранятся данные, помещает их в память, вносит в запись требуемое изменение и возвращает ее обратно на диск.
В идеальном мире SQL Server точно знает, где хранятся данные, и читает только эту единственную страницу. Но на самом деле все обстоит иначе, и SQL приходится сканировать всю таблицу, чтобы выполнить всю работу.
- Обычно наши запросы нелегко понять (иногда в них не используются условия where, и они получают все данные).
- Мы перепутали информацию на разных страницах.
- В SQL приходится иметь дело с различными операциями, такими как группировка, соединения, сортировка и так далее.
КОНКРЕТНЫЙ ПРИМЕР
Мы выполняем следующую команду, чтобы активировать, сколько байт запрос читает в памяти и на диске.
SETSTATISTICS IO ON;
Мы видим, что запрос читает 3860 логических чтений, которые он выполняет в памяти, и 574 физических чтений, которые он выполняет на диске, и все это составляет около 35,3 МБ данных, которые он читает для выполнения этого запроса.
Как правило, чем больше данных должен прочитать ваш запрос, тем медленнее он выполняет запросы, в данном случае, поскольку нет фильтра SQL, ему приходится читать каждую строку и выкрикивать (SELECT) поле DateKey, в данном случае это было 776 286 записей.
Теперь мы добавим простой фильтр к запросу и снова посмотрим план выполнения:
Секундочку… это тот же план выполнения, что и в предыдущем запросе?
То, что запрос возвращает МЕНЬШЕ данных, не означает, что SQL делает меньше работы. Перед выполнением запроса SQL должен «предсказать», сколько усилий потребуется, и для этого он использует один из флагов «Estimated Subtree Cost», основанный на IO и CPU; это то же самое, что и в предыдущем запросе.
на IO и CPU; для этого запроса SQL оценивает, что он будет стоить 3,7 querySoles.
Давайте добавим упорядочивание по полю MovementDate, а теперь остановимся на мгновение, чтобы подумать о следующем изображении и спросить себя, как человек, как бы вы выполнили следующий запрос?
В английском языке, как человек, мы бы прочитали каждую страницу и записали на другой странице найденные DateKey и MovementDate, а после выполнения этой задачи мы бы начали сортировать их по дате. В плане выполнения каждый оператор является как бы микросервисом, который, как правило, только выполняет
микросервис, который обычно выполняет только одну задачу.
В данном случае мы видим, что при добавлении сортировки общая стоимость была умножена на 6! Это преступно. Это все потому, что SQL требуется больше 8 КБ страниц для записи результатов сортировки, и в идеальном мире он делает все это в памяти, но
в несовершенном мире памяти будет недостаточно, и он делает это на диске, поэтому важно, чтобы наши запросы были как можно более понятными для человека, чтобы SQL мог предсказать, сколько памяти он выделит для вашего запроса. Поскольку после того, как SQL выделит
памяти в начале выполнения, это число является неизменным.
Иногда, когда SQL не оценивает, сколько памяти ему потребуется, в плане выполнения появляются предупреждения, в которых SQL переливает данные в tempdb, чтобы упорядочить или выполнить остальные операции запроса.
В качестве последней задачи, не глядя на план выполнения, скажите, займет ли следующий запрос больше времени на чтение данных? или запись данных? или сортировку данных? или отображение результатов?
Поскольку мы теперь получаем все поля из запроса, это означает, что мы будем читать больше страниц размером 8 КБ, и, в свою очередь, нам придется записывать больше страниц размером 8 КБ, а поскольку в памяти будет больше данных, сортировка также станет громоздкой.
И если честно, лично для меня не имеет значения, используете ли вы SELECT *, если вы не используете ORDER BY, потому что в SQL это второе по дороговизне место для упорядочивания данных.
Теперь представьте, что сотни пользователей выполняют один и тот же запрос снова и снова в одно и то же время, это создаст перегрузку в оперативной памяти сервера, SQL не сможет кэшировать все, и все взорвется (более или менее).
КОРОЧЕ ГОВОРЯ, МЫ УЗНАЛИ
Чтобы увидеть, сколько страниц размером 8 КБ использует наш запрос, мы должны активировать SET STATISTICS IO ON; (Активация не повредит).
Чтобы отфильтровать поле без индексации, SQL всегда будет выполнять сканирование таблицы.
Оператор ORDER BY без поддержки индексов в конечном итоге будет потреблять больше оперативной памяти, чем предполагалось.
SQL не помещает в память результаты запроса, но он помещает в память страницы размером 8 КБ.
В СЛЕДУЮЩЕМ ПОСТЕ
Мы увидим, как решить проблему стоимости с некластеризованными индексами и разницу между операторами index seek & scans table.