Optimizing I/O Performance by Sorting – Part 1

Автор: Craig Freedman Seek Predicates

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

Предикаты поиска

Автор: Craig Freedman Seek Predicates

Перед тем, как SQL Server приступит к поиску по индексу, он должен определить, являются ли ключи индекса подходящими для оценки предиката запроса.

Примеры полезности индексов

Автор: Craig Freedman Index Examples and Tradeoffs

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

Различия между sys.dm_db_index_usage_stats и sys.dm_db_index_operational_stats

Автор: Craig Freedman What is the difference between sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats?

В SQL Server есть два административных представления (DMV) - sys.dm_db_index_usage_stats и sys.dm_db_index_operational_stats - которые очень полезны для мониторинга использования индексов. Оба DMV выдают схожие статистические данные о количестве просмотров, поисков и изменений в индексах. Однако важно понимать разницу между ними.

Random Prefetching

Автор: Craig Freedman Random Prefetching

В предыдущей статье мы рассмотрели важность для повышения производительности асинхронного ввода-вывода и последовательного упреждающего чтения (sequential read ahead). В этой статье мы рассмотрим, как SQL Server использует случайную упреждающую (random prefetching) выборку. Давайте начнем с простого примера плана запроса, в котором много случайных операций ввода-вывода. Как и в предыдущей статье, все примеры используют базу данных TPC-H с коэффициентом масштабирования 1ГБ. Следующий запрос возвращает количество позиций, связанных с заказами, размещенным 15 марта 1998г.

Ещё одна «засада» на уровне изоляции Read Uncommitted

Автор: Craig Freedman Query Failure with Read Uncommitted

В предыдущих статьях были рассмотрены практически все уровни изоляции, за исключением Read Uncommitted или NOLOCK. Эта статья завершает серию обсуждением того, что может приключиться, если читать данные ещё не зафиксированных транзакций. О вреде NOLOCK написано уже немало. Например, вы могли об этом почитать у Любора Коллара (Lubor Kollar) из «SQL Server Development Customer Advisory Team» и в (ныне уже недоступном) блоге Тони Роджерсона (Tony Rogerson).

Read Committed and Large Objects

Автор: Craig Freedman Read Committed and Large Objects

В моей последней статье было показано, что SQL Server при исполнении оператора UPDATE откладывает фиксацию блокировки до конца чтения (вместо снятия блокировок со строк сразу после их освобождения). Как там отмечалось, это делается для того, чтобы между просмотром или поиском строк для предстоящего изменения не допустить другие изменения. В этой статье будет рассмотрен аналогичный алгоритм при работе с большими объектами.

Read Committed and Updates

Автор: Craig Freedman Read Committed and Updates

Проведём эксперимент. Начнем с создания следующей простой схемы:

create table t1 (a int, b int)
create clustered index t1a on t1(a)
insert t1 values (1, 1)
insert t1 values (2, 2)
insert t1 values (3, 3)
create table t2 (a int)
insert t2 values (9)

В сеансе 1 заблокируем третью строку таблицы t1:

begin tran
update t1 set b = b where a = 3

Далее в сеансе 2 посмотрим spid сессии (он позже понадобится), и выполним представленное ниже изменение на уровне изоляции по умолчанию read committed:

select @@spid
update t1 set t1.b = t1.b
where exists (select * from t2 where t2.a = t1.b)

Для это изменения оптимизатор выберет следующий план запроса:

|–Clustered Index Update(OBJECT:([t1].[t1a]), SET:([t1].[b] = [t1].[b]))
……|–Top(ROWCOUNT est 0)
…………|–Nested Loops(Left Semi Join, WHERE:([t2].[a]=[t1].[b]))
………………|–Clustered Index Scan(OBJECT:([t1].[t1a]))
………………|–Table Scan(OBJECT:([t2]))

Чтобы узнать, нужно ли изменять строку, в этом плане выполняется просмотр таблицы t1 и каждой строки в таблице t2. Просмотр получает U блокировку на каждую строку t1. Если строка изменяется, уровень блокирования поднимается до X-блокировки. Если строка не изменяется, просмотр снимает со строки блокировку, поскольку у этой сессии используется режим изоляции read committed.

План запроса с уровнем изоляции «Read Committed»

Автор: Craig Freedman Query Plans and Read Committed Isolation Level

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

Read Committed and Bookmark Lookup

Автор: Craig Freedman Read Committed and Bookmark Lookup

В предыдущих двух статьях мы обсуждали сценарии, при которых SQL Server продолжает удерживать блокировки Read Committed до конца исполнения оператора. Он это делает вместо того, чтобы снимать блокировку сразу после завершения работы со строкой. Один сценарий возможен при обновлении, а второй при работе с большими объектами. В этой статье (последней из цикла статей по блокировкам с Read Committed) будет рассмотрен сценарий использования в плане запроса оператора Bookmark Lookup, когда SQL Server также удерживает блокировки Read Committed дольше чем этого можно было бы ожидать.

Bookmark Lookup

Автор: Craig Freedman Bookmark Lookup

Перевод Ирины Наумовой

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

Sequential Read Ahead

Автор: Craig Freedman Sequential Read Ahead

Балансировка загрузки процессоров и ввода-вывода очень важна для обеспечения лучшей производительности и оптимизации обслуживания нагрузки сервера. В SQL Server реализованы два механизма асинхронного ввода-вывода: последовательное упреждающее чтение (sequential read ahead) и случайная упреждающая выборка (random prefetching). Оба они предназначены для балансировки нагрузки в многопроцессорных системах.

Scans vs Seeks

Автор: Craig Freedman Scans vs. Seeks

Scan и Seek — это итераторы, которые SQL Server использует для чтения данных из таблиц и индексов. Эти итераторы являются одними из самых фундаментальных, которые можно встретить почти в каждом в плане запроса. Так в чем разница между Scan (просмотром) и Seek (поиском)?

CASE Subqueries in BETWEEN and CASE Statements

Автор: Craig Freedman Subqueries in BETWEEN and CASE Statements

Рассмотрим следующий запрос:

CREATE TABLE T1 (A INT, B1 INT, B2 INT)

CREATE TABLE T2 (A INT, B INT)

SELECT *

FROM T1

WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2

Новое в SQL Server 2022: улучшения в sys.dm_exec_query_statistics_xml

Автор: Vivek Janakiraman Unleashing SQL Server 2022: Enhancements to sys.dm_exec_query_statistics_xml

Одним из улучшений в SQL Server 2022 является дальнейшее совершенствование динамического административного представления (DMV)  sys.dm_exec_query_statistics_xml. Этот DMV предоставляет подробную статистику выполнения запросов, что очень полезно для повышения их производительности и для оптимизации.

Новое в SQL Server 2022: tempdb Contention Enhancements with Page Latch Concurrency

https://www.mssqltips.com/sqlservertip/8025/sql-server-2022-tempdb-contention-enhancements/

Автор Simon Liew

Tempdb используется в качестве промежуточного хранилища и репозитория метаданных, и все эти объекты в SQL Server могут быть источником высокой нагрузки, а также приводить к конфликтам и конкуренции в критически-важных для работы сервера местах. Проявление этих негативных факторов возможно при использовании временных таблиц, триггеров, промежуточной материализации данных для спулов, курсоров, сортировок, хэш-соединений и больших объектов (LOB), и это лишь наиболее очевидные источники проблем. Ещё одной распространённой проблемой является конкуренция за страницы системных объектов Global Allocation Map (GAM) и Shared Global Allocation Map (SGAM), это особенно актуально для некоторых видов нагрузки с высоким уровнем параллелизма.

SQL Server 2022 привнёс множество впечатляющих новых функций, некоторые из которых более широко известны, чем другие. Одно из менее известных новшеств — улучшение параллелизма блокировки системных страниц.

В SQL Server 2022 страницы GAM практически лишены кратких блокировок (latch-free), и это меняет правила игры для тех типов рабочей нагрузки, где это порождало проблемы и с которыми было нелегко справиться. Однако эта функция недоступна в более ранних версиях SQL Server, поэтому для получения выгоды от этого улучшения необходимо выполнить обновление версии.

Conversion and Arithmetic Errors

Craig Freedman's SQL Server Blog

https://learn.microsoft.com/en-us/archive/blogs/craigfr/conversion-and-arithmetic-errors

Давайте посмотрим на простой запрос:

CREATE TABLE T1 (A INT, B CHAR(8))
INSERT T1 VALUES (0, '0')
INSERT T1 VALUES (1, '1')
INSERT T1 VALUES (99, 'Error')
SELECT T1.A, CONVERT(INT, T1.B) AS B_INT FROM T1

Невозможно преобразовать строку «Error» в целое число, поэтому неудивительно, что этот запрос завершается ошибкой преобразования:

A           B_INT
----------- -----------
1           1

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Error   ' to data type int.

Hash Join

Автор оригинала: Craig Freedman

По материалам статьи Craig Freedman: Hash Join

Когда Вы встречаете случай использования оператора Hash Join (хэш-соединение), это говорит о наличии тяжелого запроса. В отличие то соединения Nested Loops Join, которое хорошо для относительно маленьких наборов данных, и от соединения Merge Join, которое помогает при умеренных размерах наборов данных, хэш-соединение превосходит другие типы соединений при необходимости соединения огромных наборов данных. Хэш-соединения распараллеливается и масштабируется лучше любого другого соединения и сильно выигрывает при большой производительности информационных хранилищ (я вернусь к обсуждению параллельного выполнения запросов в следующей серии статей).

Функции ранжирования: RANK, DENSE_RANK, and NTILE

Автор оригинала: Craig Freedman

В предыдущей статье обсуждалась функция ROW_NUMBER. Сейчас же мы рассмотрим другие функции ранжирования: RANK, DENSE_RANK и NTILE. Начнем с RANK и DENSE_RANK. Эти функции по функциональности и реализации аналогичны ROW_NUMBER. Разница заключается в том, что ROW_NUMBER присваивает уникальные возрастающие значения каждой строке, не обращая внимания на повторение значений выражения сортировки, тогда как RANK и DENSE_RANK присваивают одинаковые значения строкам с одинаковым значением выражения сортировки. Разница между функциями RANK и DENSE_RANK заключается в том, как значения присваиваются строкам.