Как отменить sql запрос
Интересная задача. Неуверен в решении, но можете попробовать вот такие варианты:
Для MySQL : Вы можете использовать команду «KILL» , чтобы прервать выполнение запроса. Например, если вы хотите прервать выполнение запроса с идентификатором 12345 , вы можете выполнить следующую команду:
Для PostgreSQL : Вы можете использовать команду «pg_cancel_backend» , чтобы прервать выполнение запроса. Например, если вы хотите прервать выполнение запроса с идентификатором 12345 , вы можете выполнить следующую команду:
Важно помнить, что при прерывании запроса могут быть частично изменены данные, которые были уже затронуты этим запросом.
Устранение неполадок с запросами, которые, кажется, никогда не заканчиваются в SQL Server
В этой статье описаны действия по устранению неполадок, из-за которых запрос, который, кажется, никогда не завершается, или его завершение может занять много часов или дней.
Что такое бесконечный запрос?
В этом документе основное внимание уделяется запросам, которые продолжают выполняться или компилироваться, то есть их ЦП продолжает увеличиваться. Он не применяется к запросам, которые заблокированы или ожидают на каком-либо ресурсе, который никогда не освобождается (ЦП остается постоянным или изменяется очень мало).
Если запросу осталось завершить выполнение, он в конечном итоге завершится. Это может занять всего несколько секунд, или может занять несколько дней.
Термин «бесконечный» используется для описания восприятия того, что запрос не завершается, когда на самом деле запрос в конечном итоге завершается.
Определение бесконечного запроса
Чтобы определить, выполняется ли запрос постоянно или зависает на узком месте, выполните следующие действия.
-
Выполните следующий запрос:
DECLARE @cntr int = 0 WHILE (@cntr < 3) BEGIN SELECT TOP 10 s.session_id, r.status, r.wait_time, r.wait_type, r.wait_resource, r.cpu_time, r.logical_reads, r.reads, r.writes, r.total_elapsed_time / (1000 * 60) 'Elaps M', SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1, ((CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(st.TEXT) ELSE r.statement_end_offset END - r.statement_start_offset) / 2) + 1) AS statement_text, COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text, r.command, s.login_name, s.host_name, s.program_name, s.last_request_end_time, s.login_time, r.open_transaction_count, atrn.name as transaction_name, atrn.transaction_id, atrn.transaction_state FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st LEFT JOIN (sys.dm_tran_session_transactions AS stran JOIN sys.dm_tran_active_transactions AS atrn ON stran.transaction_id = atrn.transaction_id) ON stran.session_id =s.session_id WHERE r.session_id != @@SPID ORDER BY r.cpu_time DESC SET @cntr = @cntr + 1 WAITFOR DELAY '00:00:05' END -
Если вы видите выходные данные, аналогичные приведенному ниже, когда ЦП увеличивается пропорционально по сравнению с затраченным временем, а ожидания не возникают, применяются действия по устранению неполадок, описанные в этой статье.
| session_id | status | cpu_time | logical_reads | wait_time | wait_type |
|---|---|---|---|---|---|
| 56 | Запущена | 7038 | 101000 | 0 | NULL |
| 56 | готово к запуску | 12040 | 301000 | 0 | NULL |
| 56 | Запущена | 17020 | 523000 | 0 | NULL |
| session_id | status | cpu_time | logical_reads | wait_time | wait_type |
|---|---|---|---|---|---|
| 56 | suspended | 0 | 3 | 8312 | LCK_M_U |
| 56 | suspended | 0 | 3 | 13318 | LCK_M_U |
| 56 | suspended | 0 | 5 | 18331 | LCK_M_U |
Дополнительные сведения см. в разделе Диагностика ожиданий или узких мест.
Длительное время компиляции
В редких случаях вы можете заметить, что ЦП постоянно увеличивается с течением времени, но это не обусловлено выполнением запроса. Вместо этого она может быть вызвана слишком длинной компиляцией (синтаксический анализ и компиляция запроса). В таких случаях проверка выходной столбец transaction_name и найдите значение sqlsource_transform . Это имя транзакции указывает на компиляцию.
Сбор диагностических данных
- SQL Server 2008 г. — SQL Server 2014 г. (до sp2)
- SQL Server 2014 (после пакета обновления 2 (SP2) и SQL Server 2016 (до SP1)
- SQL Server 2016 г. (после sp1) и SQL Server 2017 г.
- SQL Server 2019 и более поздних версиях
Чтобы собрать диагностические данные с помощью SQL Server Management Studio (SSMS), выполните следующие действия.
- Запишите XML-файл предполагаемого плана выполнения запроса .
- Просмотрите план запроса, чтобы узнать, есть ли очевидные признаки того, откуда может возникнуть задержка. Типичные примеры:
- Сканирование таблиц и индексов (просмотр предполагаемых строк)
- Вложенные циклы, управляемые огромным набором данных внешней таблицы
- Вложенные циклы с большой ветвью во внутренней части цикла
- Очереди таблиц
- Функции в списке SELECT, обработка каждой строки которых занимает много времени
- Если запрос выполняется быстро в любое время, можно записать "быстрые" выполнения Фактический план выполнения XML для сравнения.
Упрощенная инфраструктура профилирования запросов появилась в этих версиях SQL Server. Он позволяет собирать фактическую статистику во время выполнения медленного запроса. Эта функция устранения неполадок позволяет изучить операторы запросов в плане запроса во время выполнения и понять, где большая часть времени тратится на запрос.
Чтобы определить медленные шаги в запросе с помощью инфраструктуры профилирования статистики упрощенного выполнения запросов версии 1, выполните следующие действия.
-
Выполните следующие команды, чтобы включить query_thread_profile XEvent:
CREATE EVENT SESSION [NodePerfStats] ON SERVER ADD EVENT sqlserver.query_thread_profile( ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system, sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed, sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username, sqlserver.sql_text)) ADD TARGET package0.ring_buffer(SET max_memory=(25600)) WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF); ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START SELECT CONVERT (varchar(30), getdate(), 126) as runtime, qp.session_id, convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name, qp.row_count, qp.estimate_row_count, qp.node_id, req.cpu_time, req.total_elapsed_time, substring (REPLACE (REPLACE (SUBSTRING (SQLText.text , (req.statement_start_offset/2) + 1 , ( (CASE statement_END_offset WHEN -1 THEN DATALENGTH(SQLText.text) ELSE req.statement_END_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS active_statement_text FROM sys.dm_exec_query_profiles qp RIGHT OUTER JOIN sys.dm_exec_requests req ON qp.session_id = req.session_id LEFT OUTER JOIN sys.dm_exec_sessions sess on req.session_id = sess.session_id LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText WHERE req.session_id <> @@SPID AND sess.is_user_process = 1 ORDER BY qp.session_id asc, row_count desc --this is to prevent massive grants OPTION (max_grant_percent = 3, MAXDOP 1) SET SHOWPLAN_XML ON GO GO SET SHOWPLAN_XML OFF ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP Вы можете использовать инфраструктуру профилирования статистики упрощенного выполнения запросов версии 2 для записи динамических планов запросов с фактическими значениями для количества строк. Эта инфраструктура профилирования позволяет изучить операторы запросов в плане запроса во время выполнения и понять, где большая часть времени тратится на запрос.
Чтобы определить медленные шаги в запросе, выполните следующие действия.
-
Чтобы включить упрощенную инфраструктуру в этих версиях SQL Server, используйте один из следующих методов:
Включите флаг трассировки 7412, выполнив следующую команду:
DBCC TRACEON (7412, -1) CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER ADD EVENT sqlserver.query_plan_profile( ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system, sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed, sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username, sqlserver.sql_text)) ADD TARGET package0.ring_buffer(SET max_memory=(25600)) WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF, STARTUP_STATE=OFF); ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START SELECT t.text, session_id FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t SELECT * FROM sys.dm_exec_query_statistics_xml () ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP -- or DBCC TRACEOFF (7412, -1) Вы можете использовать инфраструктуру профилирования статистики упрощенного выполнения запросов версии 3 для записи динамических планов запросов с фактическими значениями для количества строк. Эта инфраструктура профилирования позволяет изучить операторы запросов в плане запроса во время выполнения и понять, где большая часть времени тратится на запрос. Упрощенное профилирование включено по умолчанию в SQL Server 2019 г.
Чтобы определить медленные шаги в запросе, выполните следующие действия.
- Запустите затронутый бесконечный запрос из приложения.
- Используйте команду, аналогичную приведенной ниже, чтобы определить Session_id объект выполняемого бесконечного запроса:
SELECT t.text, session_id FROM sys.dm_exec_requests req CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t SELECT * FROM sys.dm_exec_query_statistics_xml () Примечание. Если вы не получаете выходные данные из sys.dm_exec_query_statistics_xml , можно проверка, отключен ли параметр LAST_QUERY_PLAN_STATS базы данных, выполнив следующую команду:
SELECT name, value, value_for_secondary, is_value_default FROM sys.database_scoped_configurations WHERE name = 'LAST_QUERY_PLAN_STATS' Вы можете включить статистику плана последнего запроса на уровне базы данных, запустив ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON .
Метод проверки собранных планов
В этом разделе показано, как просмотреть собранные данные. Он будет использовать несколько планов ЗАПРОСОВ XML (с расширением *.sqlplan), собранных в SQL Server 2016 с пакетом обновления 1 (SP1) и более поздних сборках и версиях.
Чтобы сравнить планы выполнения, выполните следующие действия.
- Откройте ранее сохраненный файл плана выполнения запроса (.sqlplan).
- Щелкните правой кнопкой мыши пустую область плана выполнения и выберите Сравнить план выполнения.
- Выберите второй файл плана запроса, который требуется сравнить.
- Найдите толстые стрелки, указывающие на большое количество строк, передаваемых между операторами. Затем выберите оператор до или после стрелки и сравните количество фактических строк в двух планах.
- Сравните второй и третий планы, чтобы узнать, происходит ли наибольший поток строк в одних и том же операторах. Пример:
Решение
- Убедитесь, что статистика обновлена для таблиц, используемых в запросе.
- Найдите отсутствующие рекомендации по индексу в плане запроса и примените ее.
- Перепишите запрос, чтобы упростить его:
- Используйте более селективные WHERE предикаты, чтобы сократить объем обрабатываемых данных.
- Разорвать его на части.
- Выберите некоторые части во временные таблицы и соедините их позже.
- Удалите TOP , EXISTS и FAST (T-SQL) в запросах, которые выполняются в течение очень длительного времени из-за цели строки оптимизатора. Кроме того, можно использовать подсказку DISABLE_OPTIMIZER_ROWGOAL . Дополнительные сведения см. в разделе Цели строк ушли изгоев.
- Избегайте использования общих табличных выражений (CTE) в таких случаях, когда они объединяют инструкции в один большой запрос.
- Попробуйте использовать указания запросов , чтобы создать лучший план:
- HASH JOIN или MERGE JOIN подсказка
- FORCE ORDER Подсказка
- FORCESEEK Подсказка
- RECOMPILE
- ИСПОЛЬЗУЙТЕ PLAN N'' , если у вас есть быстрый план запроса, который можно принудительно применить.
- Используйте хранилище запросов (QDS) для принудительного применения хорошо известного плана, если такой план существует и если версия SQL Server поддерживает хранилище запросов.
Диагностика ожиданий или узких мест
Этот раздел включен в качестве справки на случай, если проблема не является длительным запросом на управление ЦП. Его можно использовать для устранения неполадок с запросами, которые выполняются долго из-за ожиданий.
Чтобы оптимизировать запрос, ожидающий узких мест, определите, сколько времени ожидания и где находится узкое место (тип ожидания). После подтверждения типа ожидания сократите время ожидания или полностью исключите его.
Чтобы вычислить приблизительное время ожидания, вычесть время ЦП (время рабочей роли) из затраченного времени запроса. Как правило, время ЦП — это фактическое время выполнения, а оставшаяся часть времени существования запроса ожидается.
Примеры вычисления приблизительной продолжительности ожидания:
| Затраченное время (мс) | Время ЦП (мс) | Время ожидания (мс) |
|---|---|---|
| 3200 | 3000 | 200 |
| 7080 | 1000 | 6080 |
Определение узкого места или ожидания
- Чтобы определить исторические запросы с длительным ожиданием (например, >20 % от общего затраченного времени составляет время ожидания), выполните следующий запрос. Этот запрос использует статистику производительности для кэшированных планов запросов с начала SQL Server.
SELECT t.text, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, qs.total_worker_time / qs.execution_count AS avg_cpu_time, (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, qs.total_elapsed_time AS cumulative_elapsed_time FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time > 0.2 ORDER BY qs.total_elapsed_time / qs.execution_count DESC SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms FROM sys.dm_exec_requests r JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE wait_time > 500 AND is_user_process = 1 - Выполните запрос с включением фактического плана выполнения в.
- Щелкните правой кнопкой мыши левый оператор на вкладке План выполнения .
- Выберите Свойства , а затем — Свойство WaitStats .
- Проверьте Значения WaitTimeMs и WaitType.
Ссылки для устранения или сокращения ожиданий
Причины и способы их устранения для каждого типа ожидания различаются. Не существует единого общего метода для разрешения всех типов ожидания. Ниже приведены статьи по устранению неполадок и устранению распространенных проблем с типом ожидания:
- Общие сведения и устранение проблем с блокировкой (LCK_M_*)
- Общие сведения и устранение проблем с блокировкой базы данных Azure SQL
- Устранение неполадок с низкой производительностью SQL Server, вызванной проблемами ввода-вывода (PAGEIOLATCH_*, WRITELOG, IO_COMPLETION, BACKUPIO)
- Разрешение конфликтов по вставке последней страницы PAGELATCH_EX в SQL Server
- Объяснения и решения, предоставляемые памятью (RESOURCE_SEMAPHORE)
- Устранение неполадок с медленными запросами, возникающими в результате ASYNC_NETWORK_IO типа ожидания
- Устранение неполадок с типом ожидания высокого HADR_SYNC_COMMIT в группах доступности Always On
- Как это работает: CMEMTHREAD и отладка
- Обеспечение того, чтобы параллелизм ждал действия (CXPACKET и CXCONSUMER)
- ОЖИДАНИЕ THREADPOOL
Описание многих типов ожиданий и то, что они указывают, см. в таблице Типы ожиданий.
Очень длинный SQL запрос - что делать?
Доброго времени суток и с Наступившим Новым Кодом 🙂 Есть интересная ситуация - существует очень большой (по длине) SQL запрос. Из-за этого он не обрабатывается сервером корректно и выплёвывает 502 ошибку. Если смотреть со стороны SQL-я самого (через консоль) - запрос отлично отрабатывает, но как только этот же запрос посылаю из php - получаю 502. Логично, что проблемы кроются где-то в драйвере php-mysql, но как решить эту проблему, понятия не имею. Помогите, пожалуйста. 🙂 Чтобы имели представление по поводу величины: длина запроса ниже средней - 34387 символов в UTF8. P.S. Используется mysqli.
P.P.S. В логах nginx явно не написано про mysql. Только вот это:
2015/01/06 04:19:56 [error] 29210#0: *3 recv() failed (104: Connection reset by peer) while reading response header from upstream, client: 127.0.0.1, server: tits.by, request: "POST /maps/search/ HTTP/1.1", upstream: "fastcgi://unix://var/run/php5-fpm.sock:", host: "localhost", referrer: "http://www.transinfo.by/maps/" Отслеживать
11 1 1 золотой знак 2 2 серебряных знака 8 8 бронзовых знаков
задан 6 янв 2015 в 1:03
Станислав Комар Станислав Комар
1,987 16 16 серебряных знаков 29 29 бронзовых знаков
2 ответа 2
Сортировка: Сброс на вариант по умолчанию
Вопрос решился. Была проблема не в драйвере, а в ядре старинной CMS с которой приходилось работать. Обошелся прямым вызовом execute.
Отслеживать
51.3k 87 87 золотых знаков 267 267 серебряных знаков 505 505 бронзовых знаков
Что делать, если скорость исполнения запросов в PL/SQL вас не устраивает?
Oracle SQL Developer – полностью бесплатное приложение. Поэтому скачивание его не вызовет никаких проблем у пользователя.
Данная среда разработки позволяет соединиться с любой целевой схемой базы данных Oracle при использовании стандартной аутентификации Oracle Database. После подключения можно выполнять операции с объектами в базе данных.
Приложение дает разработчикам баз данных удобный способ выполнения основных задач:
- Просмотр и управление объектами базы данных
- Выполнение операторов SQL и скриптов
- Редактирование и отладка кода PL/SQL
- Импорт и экспорт данных
- Просмотр и создание отчетов
Более того, при помощи Oracle SQL Developer можно соединиться со схемами для выбранных сторонних (не Oracle) баз данных, таких как MySQL, Microsoft SQL Server и Microsoft Access, а также просматривать метаданные и данные в этих базах данных.
Плюсы использования
Проверить быстроту выполнения запросов в данной среде разработки вы можете самостоятельно. Вспомните SQL-запрос, который в PL/SQL Developer «крутится» более 10 минут, и запустите его.
Мы с коллегами, например, выполняя рядовые выгрузки, ощутили прирост в скорости примерно в 5-7 раз! Достаточно весомый аргумент, чтобы обратить внимание на данное приложение.
Oracle SQL Developer подойдет тем людям, которые любят смотреть, что получается на выходе после малейшего изменения в запросе.
А также для исполнения запросов, в которых в where указано очень много условий: длинный список клиентов через or (Рис. 1) или, например, имеется много строк с like (Рис. 2).
Немаловажно знать, как осуществляется экспорт полученных в ходе выполнения запроса данных. Воспользуемся встроенными возможностями экспорта Oracle SQL Developer:
- Начнем с того, что после отработки запроса на экране будет представлена лишь часть полученных данных. Чтобы «прогрузить» данные до конца можно воспользоваться колесиком мышки, а можно нажать сочетание клавиш Ctrl+End.
- Чтобы сохранить данные в таблицу Excel необходимо в результирующей таблице перейти в контекстное меню и выбрать Export (Рис. 3).
3. Задаем необходимый формат файла – Format (Рис. 4 – ШАГ 1). В нашем случае это «excel 2003+ (xlsx)».
Выбираем путь, куда будет сохранен файл – Browse (Рис. 4 – ШАГ 2). Здесь же указываем имя файла.
Нажимаем последовательно на кнопки «Следующий» и «Готово» (Рис. 4 – ШАГ 3).
4. Процесс экспорта данных будет демонстрироваться в отдельном окне.
По завершении экспорта, в указанной нами директории появится Excel-файл: на листе «Export Worksheet» представлены выгруженные данные, а на листе «SQL» – скрипт, с помощью которого они были получены. Таким образом, можно в нужный момент вспомнить, откуда тот или иной файл взялся у вас на компьютере.
Еще одним немаловажным плюсом Oracle SQL Developer является его кросс-платформенность: можно запускать на Windows, Linux и macOS.
Некоторые особенности использования
Стоит сказать об умении определять границы SQL-запросов. Oracle SQL Developer требует обязательно отделять запросы точкой с запятой, а иначе он не будет понимать, где один запрос закончился, а другой начался.
Конечно, прирост в скорости исполнения запросов влечет за собой большую ресурсоемкость. Прежде всего это касается оперативной памяти компьютера . Данный факт, безусловно, является недостатком данной среды разработки.
Интерфейс Oracle SQL Developer также можно отнести к минусам, т.к. без специальной статьи тяжело сразу понять, куда нужно нажимать.
При возникновении проблем с подключением и настройкой Oracle SQL Developer, в конце статьи будет приведена пошаговая инструкция «Как начать работу с Oracle SQL Developer».
Исходя из выше изложенного оптимальный вариант использования Oracle SQL Developer – это выполнение и промежуточное тестирование действительно «тяжелых» SQL-запросов. А встроенные возможности экспорта помогут легко сохранить полученные данные.
Также не стоит забывать о том, что данный продукт является совершенно бесплатным . Возможно, для кого-то Oracle SQL Developer станет отличным инструментом для тестирования тех или иных скриптов в домашних условиях (кросс-платформенность позволит работать не только на Windows), а для тех, кто только начинает знакомство с SQL-запросами, весьма удобной «учебной» средой разработки.
Как начать работу с Oracle SQL Developer
После успешной установки и запуска Oracle SQL Developer необходимо:
- Создать новое соединение с источником данных. Это можно сделать из меню File → New → Connections, а также из окна Connections, нажав на значок «+» (Рис. 5).