Как узнать стоимость запроса sql

Что такое стоимость запроса в SQL?

Что такое стоимость запроса и как ее узнать в SQLiteStudio (3.2.1) ? Есть много сайтов, где про нее что-то написано Здесь описывается что все неправильно понимают, что такое стоимость. И чем стоимость полезна. Здесь говорится, как уменьшить стоимость. А в учебнике Грофф Дж. Р., Вайнберг П.Н., Оппель Э. Дж. — SQL. Полное руководство — 2015 на 900 с лишним страниц такое понятие вообще ни разе не упоминается.

Отслеживать
задан 1 ноя 2021 в 11:40
Cowboyserg Cowboyserg
781 6 6 серебряных знаков 21 21 бронзовый знак
Что такое стоимость запроса Попытка оценить затраты ресурсов на выполнение запроса «в попугаях».
1 ноя 2021 в 11:47

Открой план запроса, там ты увидишь все операции которые произошли при выполнении этого запроса. В каждой операции указано в % отношении сколько ресурсов она потребила (100% это выполненный запрос) . Это и есть стоимость.

1 ноя 2021 в 11:52

@Akina Да, видел, что в 3000 попугаев и более. Только вот вопрос, эти попугаи, это тики системы или что?

sys.dm_exec_requests (Transact-SQL)

Возвращает сведения о каждом запросе, который выполняется в SQL Server. Дополнительные сведения о запросах см. в руководстве по архитектуре потоков и задач.

Чтобы вызвать это из выделенного пула SQL в Azure Synapse Analytics или Analytics Platform System (PDW), ознакомьтесь со статьей sys.dm_pdw_exec_requests (Transact-SQL). Для бессерверного пула SQL или Microsoft Fabric используется sys.dm_exec_requests .

Имя столбца	Тип данных	Описание:
session_id	smallint	Идентификатор сеанса, к которому относится данный запрос. Не допускает значения NULL.
request_id	int	Идентификатор запроса. Уникален в контексте сеанса. Не допускает значения NULL.
start_time	datetime	Метка времени поступления запроса. Не допускает значения NULL.
состояние	nvarchar(30)	Состояние запроса. Может использоваться одно из следующих значений:

background
откат
выполняется
готово к запуску
sleeping
suspended (приостановлено)

ВЫБРАТЬ
ВСТАВИТЬ
ОБНОВИТЬ
DELETE
BACKUP LOG
BACKUP DATABASE
DBCC
FOR

Текст запроса можно получить с sys.dm_exec_sql_text соответствующим sql_handle запроса. Внутренние системные процессы устанавливают команду в соответствии с выполняемой задачей. Например:

-2 = Блокирующий ресурс принадлежит потерянной распределенной транзакции.

-3 = Блокирующий ресурс принадлежит отложенной транзакции восстановления.

-4 = Идентификатор сеанса владельца кратковременной блокировки сейчас не удалось определить из-за переходов между внутренними состояниями кратковременной блокировки.

-5 = идентификатор сеанса владельца блокировки не удалось определить, так как он не отслеживается для этого типа блокировки (например, для блокировки SH).

Сам по себе идентификатор blocking_session_id -5 не указывает на проблему с производительностью. Идентификатор -5 указывает, что сеанс ожидает выполнения асинхронного действия. Перед тем, как был представлен идентификатор -5, в том же сеансе был показан идентификатор 0, даже если он все еще находился в состоянии ожидания.

ALTER INDEX REORGANIZE
параметра AUTO_SHRINK с ALTER DATABASE
BACKUP DATABASE
DBCC CHECKDB
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC INDEXDEFRAG
DBCC SHRINKDATABASE
DBCC SHRINKFILE
Восстановление…
RESTORE DATABASE
ROLLBACK
TDE ENCRYPTION

В противном случае возвращается 0.

Дескриптор SQL для отдельного запроса.

Необязательный внешний ключ sys.query_context_settings .

Замечания

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

При выполнении параллельных запросов в построчном режиме SQL Server назначает рабочий поток для координации рабочих потоков, ответственных за выполнение назначенных им задач. В этом динамическом административном представлении для запроса отображается только координирующий поток. Столбцы reads , writes , logical_reads и row_count не обновляются для координирующего потока. Столбцы wait_type , wait_time , last_wait_type , wait_resource и granted_query_memory обновляются только для координирующего потока. Дополнительные сведения см. в статье Руководство по архитектуре потоков и задач.

Разрешения

Если у пользователя есть разрешение VIEW SERVER STATE на сервере, он увидит все выполняющиеся сеансы на экземпляре SQL Server. В противном случае пользователь увидит только текущий сеанс. Разрешение VIEW SERVER STATE нельзя предоставить в Базе данных SQL Azure, поэтому представление sys.dm_exec_requests всегда ограничено текущим подключением.

В сценариях группы доступности, если для вторичной реплики задано только намерение чтения, подключение к вторичному объекту должно указать намерение приложения в параметрах строки подключения путем добавления applicationintent=readonly . В противном случае проверка доступа для sys.dm_exec_requests не будет пройдена для баз данных в группе доступности, даже если есть разрешение VIEW SERVER STATE .

Для SQL Server 2022 (16.x) и более поздних sys.dm_exec_requests версий требуется разрешение VIEW SERVER PERFORMANCE STATE на сервере.

Примеры

О. Поиск текста запроса для выполняемого пакета

В следующем примере выполняется запрос sys.dm_exec_requests для поиска необходимого запроса и из его результата копируется sql_handle .

SELECT * FROM sys.dm_exec_requests; GO 

Затем для получения текста инструкции используйте скопированный sql_handle с помощью системной функции sys.dm_exec_sql_text(sql_handle) .

SELECT * FROM sys.dm_exec_sql_text(< copied sql_handle >); GO 

B. Поиск всех блокировок, в которых выполняется пакет

В следующем примере выполняется запрос sys.dm_exec_requests для поиска необходимого пакета и из результата копируется transaction_id .

SELECT * FROM sys.dm_exec_requests; GO 

Затем для получения сведений о блокировке используйте скопированный transaction_id с помощью системной функции sys.dm_tran_locks .

SELECT * FROM sys.dm_tran_locks WHERE request_owner_type = N'TRANSACTION' AND request_owner_id = < copied transaction_id >; GO 

C. Поиск всех заблокированных в настоящее время запросов

В следующем примере выполняется запрос sys.dm_exec_requests для поиска сведений о заблокированных запросах.

SELECT session_id, status, blocking_session_id, wait_type, wait_time, wait_resource, transaction_id FROM sys.dm_exec_requests WHERE status = N'suspended'; GO 

D. Заказ существующих запросов по ЦП

SELECT [req].[session_id], [req].[start_time], [req].[cpu_time] AS [cpu_time_ms], OBJECT_NAME([ST].[objectid], [ST].[dbid]) AS [ObjectName], SUBSTRING( REPLACE( REPLACE( SUBSTRING( [ST].[text], ([req].[statement_start_offset] / 2) + 1, ((CASE [req].[statement_end_offset] WHEN -1 THEN DATALENGTH([ST].[text]) ELSE [req].[statement_end_offset] END - [req].[statement_start_offset] ) / 2 ) + 1 ), CHAR(10), ' ' ), CHAR(13), ' ' ), 1, 512 ) AS [statement_text] FROM [sys].[dm_exec_requests] AS [req] CROSS APPLY [sys].dm_exec_sql_text([req].[sql_handle]) AS [ST] ORDER BY [req].[cpu_time] DESC; GO 

См. также

• Системные динамические административные представления
• Выполнение связанных динамических административных представлений и функций
• sys.dm_os_memory_clerks
• sys.dm_os_sys_info
• sys.dm_exec_query_memory_grants
• sys.dm_exec_query_plan
• sys.dm_exec_sql_text
• SQL Server, объект статистики SQL
• Руководство по архитектуре обработки запросов
• Руководство по архитектуре потоков и задач

Далее

SQL-Ex blog

Не пора ли убрать стоимость из плана запроса?

Добавил Sergey Moiseenko on Среда, 17 марта. 2021

Приглашенная звезда

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

• Стоимость плана — это как долго выполняется запрос.
• Стоимость оператора — это процент времени, которое занимает выполнение оператора в пределах плана.

Оптимизатор не знает, что у вас отличное хранилище. Предполагается, что это не так. Вы когда-нибудь видели, сколько стоит случайный ввод-вывод?

И неважно, сколько у вас памяти, или как много ваших данных уже находится в памяти; оптимизатор исходит из предположения, что данных в памяти нет (непрогретый кэш).

Стоимость может особенно ввести в заблуждение в предварительных/кэшированных планах, если виной тому прослушивание параметра.

Чем полезна стоимость?

Что касается меня, я, главным образом, использую стоимость для демонстрации того, почему SQL Server предпочитает один план другому. Как только вы осознаете, что оптимизатор выбирает планы на основе стоимости, легко сделать логический вывод, что другой вариант был оценен как более дорогой.

Другой момент состоит в том, что хотя многие метрики имеют «оценочные (предварительные)» и «фактические» компоненты, когда вы собираете фактический план выполнения.

только оценки

. ни одна из этих оценочных метрик стоимости не имеет фактических компонентов, которые появляются в фактических планах, и они не обновляются после выполнения запроса, чтобы отразить произошедшее, когда он выполнялся.

Если бы это было сделано, они оказались бы бесполезны для иллюстрации того единственного вывода, который они могут обоснованно сделать: почему был выбран этот план.

Лучшие метрики

В последних версиях SQL Server и SSMS вы получаете время оператора.

Наряду с временем оператора мы получаем информацию о вводе/выводе, распределении строк/потоков в параллельных планах и набор других полезных метрик.

Я бы предпочел увидеть либо последнее время выполнения операторов, либо среднее время выполнения операторов в плане. Пока вы не назвали меня сумасшедшим, упомяну, что SQL Server 2019 имеет новое DMV — sys.dm_exec_query_plan_stats — которое отслеживает последний известный фактический план выполнения для запроса.

В долгосрочной перспективе имеет смысл заменить стоимость на время работы оператора. Это значительно упростит поиск худших частей планов запросов.

Обратные ссылки

Нет обратных ссылок

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Как узнать стоимость запроса sql

EXPLAIN — показать план выполнения оператора

Синтаксис

EXPLAIN [ ( параметр [, . ] ) ] оператор EXPLAIN [ ANALYZE ] [ VERBOSE ] оператор Здесь допускается параметр: ANALYZE [ boolean ] VERBOSE [ boolean ] COSTS [ boolean ] BUFFERS [ boolean ] TIMING [ boolean ] FORMAT

Описание

Эта команда выводит план выполнения, генерируемый планировщиком Postgres Pro для заданного оператора. План выполнения показывает, как будут сканироваться таблицы, затрагиваемые оператором — просто последовательно, по индексу и т. д. — а если запрос связывает несколько таблиц, какой алгоритм соединения будет выбран для объединения считанных из них строк.

Наибольший интерес в выводимой информации представляет ожидаемая стоимость выполнения оператора, которая показывает, сколько, по мнению планировщика, будет выполняться этот оператор (это значение измеряется в единицах стоимости, которые не имеют точного определения, но обычно это обращение к странице на диске). Фактически выводятся два числа: стоимость запуска до выдачи первой строки и общая стоимость выдачи всех строк. Для большинства запросов важна общая стоимость, но в таких контекстах, как подзапрос в EXISTS , планировщик будет минимизировать стоимость запуска, а не общую стоимость (так как исполнение запроса всё равно завершится сразу после получения одной строки). Кроме того, если количество возвращаемых строк ограничивается предложением LIMIT , планировщик интерполирует стоимость между двумя этими числами, выбирая наиболее выгодный план.

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

Важно

Имейте в виду, что с указанием ANALYZE оператор действительно выполняется. Хотя EXPLAIN отбрасывает результат, который вернул бы SELECT , в остальном все действия выполняются как обычно. Если вы хотите выполнить EXPLAIN ANALYZE с командой INSERT , UPDATE , DELETE , CREATE TABLE AS или EXECUTE , не допуская изменения данных этой командой, воспользуйтесь таким приёмом:

BEGIN; EXPLAIN ANALYZE . ; ROLLBACK;

Без скобок для этого оператора можно указать только параметры ANALYZE и VERBOSE и только в таком порядке. В PostgreSQL до версии 9.0 поддерживался только синтаксис без скобок, однако в дальнейшем ожидается, что все новые параметры будут восприниматься только в скобках.

Параметры

Выполнить команду и вывести фактическое время выполнения и другую статистику. По умолчанию этот параметр равен FALSE . VERBOSE

Вывести дополнительную информацию о плане запроса. В частности, включить список столбцов результата для каждого узла в дереве плана, дополнить схемой имена таблиц и функций, всегда указывать для переменных в выражениях псевдоним их таблицы, а также выводить имена всех триггеров, для которых выдаётся статистика. По умолчанию этот параметр равен FALSE . COSTS

Вывести рассчитанную стоимость запуска и общую стоимость каждого узла плана, а также рассчитанное число строк и ширину каждой строки. Этот параметр по умолчанию равен TRUE . BUFFERS

Включить информацию об использовании буфера. В частности, вывести число попаданий, блоков прочитанных, загрязненных и записанных в разделяемом и локальном буфере, а также число прочитанных и записанных временных блоков. Попаданием считается ситуация, когда требуемый блок уже находится в кеше и чтения с диска удаётся избежать. Блоки в общем буфере содержат данные обычных таблиц и индексов, в локальном — данные временных таблиц и индексов, а временные блоки предназначены для краткосрочного использования при выполнении сортировки, хеширования, материализации и подобных узлов плана. Число загрязнённых блоков показывает, сколько ранее не модифицированных блоков изменила данная операция; тогда как число записанных блоков показывает, сколько ранее загрязнённых блоков данный серверный процесс вынес из кеша при обработке запроса. Значения, указываемые для узла верхнего уровня, включают значения всех его дочерних узлов. В текстовом формате выводятся только ненулевые значения. Этот параметр действует только в режиме ANALYZE . По умолчанию его значение равно FALSE . TIMING

Включить в вывод фактическое время запуска и время, затраченное на каждый узел. Постоянное чтение системных часов может значительно замедлить запрос, так что если достаточно знать фактическое число строк, имеет смысл сделать этот параметр равным FALSE . Время выполнения всего оператора замеряется всегда, даже когда этот параметр выключен и на уровне узлов время не подсчитывается. Этот параметр действует только в режиме ANALYZE . По умолчанию его значение равно TRUE . FORMAT

Установить один из следующих форматов вывода: TEXT, XML, JSON или YAML. Последние три формата содержат ту же информацию, что и текстовый, но больше подходят для программного разбора. По умолчанию выбирается формат TEXT . boolean

Включает или отключает заданный параметр. Для включения параметра можно написать TRUE , ON или 1 , а для отключения — FALSE , OFF или 0 . Значение boolean можно опустить, в этом случае подразумевается TRUE . оператор

Любой оператор SELECT , INSERT , UPDATE , DELETE , VALUES , EXECUTE , DECLARE , CREATE TABLE AS и CREATE MATERIALIZED VIEW AS , план выполнения которого вас интересует.

Выводимая информация

Результатом команды будет текстовое описание плана, выбранного для оператора , возможно, дополненное статистикой выполнения. Представленная информация описана в Разделе 14.1.

Замечания

Чтобы планировщик запросов Postgres Pro был достаточно информирован для эффективной оптимизации запросов, данные в pg_statistic должны быть актуальными для всех таблиц, задействованных в запросе. Обычно об этом автоматически заботится демон автоочистки. Но если в таблице недавно произошли значительные изменения, может потребоваться вручную выполнить ANALYZE , не дожидаясь, пока автоочистка обработает эти изменения.

Измеряя фактическую стоимость выполнения каждого узла в плане, текущая реализация EXPLAIN ANALYZE привносит накладные расходы профилирования в выполнение запроса. В результате этого, при запуске запроса командой EXPLAIN ANALYZE он может выполняться значительно дольше, чем при обычном выполнении. Объём накладных расходов зависит от природы запроса, а также от используемой платформы. Худшая ситуация наблюдается для узлов плана, которые сами по себе выполняются очень быстро, и в операционных системах, где получение текущего времени относительно длительная операция.

Примеры

Получение плана простого запроса для таблицы, содержащей единственный столбец типа integer , с 10000 строк:

EXPLAIN SELECT * FROM foo; QUERY PLAN --------------------------------------------------------- Seq Scan on foo (cost=0.00..155.00 rows=10000 width=4) (1 row)

План того же запроса, но выведенный в формате JSON:

EXPLAIN (FORMAT JSON) SELECT * FROM foo; QUERY PLAN -------------------------------- [ + < + "Plan": < + "Node Type": "Seq Scan",+ "Relation Name": "foo", + "Alias": "foo", + "Startup Cost": 0.00, + "Total Cost": 155.00, + "Plan Rows": 10000, + "Plan Width": 4 + >+ > + ] (1 row)

Если в таблице есть индекс, а в запросе присутствует условие WHERE , для которого полезен этот индекс, EXPLAIN может показать другой план:

EXPLAIN SELECT * FROM foo WHERE i = 4; QUERY PLAN -------------------------------------------------------------- Index Scan using fi on foo (cost=0.00..5.98 rows=1 width=4) Index Cond: (i = 4) (2 rows)

План того же запроса, но в формате YAML:

EXPLAIN (FORMAT YAML) SELECT * FROM foo WHERE i='4'; QUERY PLAN ------------------------------- - Plan: + Node Type: "Index Scan" + Scan Direction: "Forward"+ Index Name: "fi" + Relation Name: "foo" + Alias: "foo" + Startup Cost: 0.00 + Total Cost: 5.98 + Plan Rows: 1 + Plan Width: 4 + Index Cond: "(i = 4)" (1 row)

Рассмотрение формата XML оставлено в качестве упражнения для читателя.

План того же запроса без вывода оценок стоимости:

EXPLAIN (COSTS FALSE) SELECT * FROM foo WHERE i = 4; QUERY PLAN ---------------------------- Index Scan using fi on foo Index Cond: (i = 4) (2 rows)

Пример плана для запроса с агрегатной функцией:

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; QUERY PLAN --------------------------------------------------------------------- Aggregate (cost=23.93..23.93 rows=1 width=4) ->Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4) Index Cond: (i < 10) (3 rows)

Пример использования EXPLAIN EXECUTE для отображения плана выполнения подготовленного запроса:

PREPARE query(int, int) AS SELECT sum(bar) FROM test WHERE id > $1 AND id < $2 GROUP BY foo; EXPLAIN ANALYZE EXECUTE query(100, 200); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ HashAggregate (cost=9.54..9.54 rows=1 width=8) (actual time=0.156..0.161 rows=11 loops=1) Group Key: foo ->Index Scan using test_pkey on test (cost=0.29..9.29 rows=50 width=8) (actual time=0.039..0.091 rows=99 loops=1) Index Cond: ((id > $1) AND (id < $2)) Planning time: 0.197 ms Execution time: 0.225 ms (6 rows)

Разумеется, конкретные числа, показанные здесь, зависят от фактического содержимого задействованных таблиц. Также учтите, что эти числа и даже выбранная стратегия выполнения запроса могут меняться от версии к версии Postgres Pro вследствие усовершенствования планировщика. Кроме того, команда ANALYZE при обработке статистических данных производит случайные выборки, так что оценки стоимости могут меняться при каждом чистом запуске ANALYZE , даже когда фактическое распределение данных в таблице не меняется.

Совместимость

Оператор EXPLAIN отсутствует в стандарте SQL.

См. также

Пред.	Наверх	След.
EXECUTE	Начало	FETCH