Maximum Row Size and Query Hints

Автор: Craig Freedman Maximum Row Size and Query Hints

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

CREATE TABLE T (A INT, B CHAR(8000), C CHAR(8000))

Msg 1701, Level 16, State 1, Line 1

Creating or altering table 'T' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.

Какое же отношение ограничение размера записи имеет к подсказкам оптимизатору?

Подразумеваемые (Implied) предикаты

Автор: Craig Freedman Implied Predicates and Query Hints

В этой статье будут рассмотрены некоторые странности с предикатами в планах запросов. Рассмотрим следующую тривиальную схему и запрос:

CREATE TABLE T1 (A INT, B INT)

CREATE TABLE T2 (A INT, B INT)

 

SELECT *

FROM T1 INNER JOIN T2 ON T1.A = T2.A

WHERE T1.B = 0

OPTION (HASH JOIN)

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

  |--Hash Match(Inner Join, HASH:([T1].[A])=([T2].[A]), RESIDUAL:([T2].[A]=[T1].[A]))

       |--Table Scan(OBJECT:([T1]), WHERE:([T1].[B]=(0)))

       |--Table Scan(OBJECT:([T2]))

На самом деле, этот запрос получит такой план с подсказкой или без нее. Теперь давайте внесем небольшое изменение в предложение WHERE и посмотрим, что произойдет:

SELECT *

FROM T1 INNER JOIN T2 ON T1.A = T2.A

WHERE T1.A = 0

OPTION (HASH JOIN)

Теперь этот запрос выдает сообщение об ошибке:

Msg 8622, Level 16, State 1, Line 1

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

OPTIMIZED Nested Loops Joins

Автор: Craig Freedman OPTIMIZED Nested Loops Joins

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

Optimizing I/O Performance by Sorting – Part 2

Автор: Craig Freedman Optimizing I/O Performance by Sorting – Part 2

В предыдущей части мы рассмотрели, как SQL Server использует сортировку для преобразования случайных операций ввода-вывода в последовательные. В этой части давайте наглядно продемонстрируем, как такая сортировка может повлиять на производительность. Для проверки я буду использовать ту же базу данных размером 3 ГБ, которая была создана в первой части.

Новое в SQL Server 2022: Improved RCSI Ghost Cleanup

Автор: Paul White https://www.sql.kiwi/2024/12/improved-ghosts-2022/

Уровень изоляции моментального снимка с фиксированным чтением (Read Committed Snapshot Isolation, далее: RCSI) даёт много преимуществ. Главное из них в том, что читатели не будут блокировать писателей (и наоборот). Каждый оператор видит снимок данных на определенный момент времени (за исключением некоторых случаев, таких как использование non-inlined функций, которые оптимизатор не может развернуть внутри запроса). С другой стороны, появляются затраты на поддержание версий строк, необходимых для реализации RCSI.

Речь идет не только о том, чтобы убедиться, что база данных tempdb (или пользовательская база данных (если используется ADR - ACCELERATED_DATABASE_RECOVERY) достаточно велика и может справиться с дополнительной параллельной активностью:

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

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

Optimizing I/O Performance by Sorting – Part 1

Автор: Craig Freedman Optimizing I/O Performance by Sorting – Part 1

В одной из предыдущих статей мы обсуждали почему случайные операции ввода-вывода медленнее последовательных (особенно для старых шпиндельных жестких дисков). По этой причине 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