Sql server запросы как найти - Исправление недочетов и поиск решений вместе с Examum.ru

Query history can be viewed using the system views:

sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_exec_query_plan

For example, using the following query:

select  top(100)
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time/1000 as CPU,
        convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
        qs.total_elapsed_time/1000 as TotDuration,
        convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
        total_logical_reads as [Reads],
        total_logical_writes as [Writes],
        total_logical_reads+total_logical_writes as [AggIO],
        convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
        [sql_handle],
        plan_handle,
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        total_physical_reads,
        convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
        convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
        convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
        query_hash,
        query_plan_hash,
        total_rows,
        convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
        total_dop,
        convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
        total_grant_kb,
        convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
        total_used_grant_kb,
        convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
        total_ideal_grant_kb,
        convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
        total_reserved_threads,
        convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
        total_used_threads,
        convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.statement_start_offset)/2  ))
        end as query_text,
        db_name(st.dbid) as database_name,
        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
        sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%query%'

Current running queries can be seen using the following script:

select ES.[session_id]
      ,ER.[blocking_session_id]
      ,ER.[request_id]
      ,ER.[start_time]
      ,DateDiff(second, ER.[start_time], GetDate()) as [date_diffSec]
      , COALESCE(
                    CAST(NULLIF(ER.[total_elapsed_time] / 1000, 0) as BIGINT)
                   ,CASE WHEN (ES.[status] <> 'running' and isnull(ER.[status], '')  <> 'running') 
                            THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
                    END
                ) as [total_time, sec]
      , CAST(NULLIF((CAST(ER.[total_elapsed_time] as BIGINT) - CAST(ER.[wait_time] AS BIGINT)) / 1000, 0 ) as bigint) as [work_time, sec]
      , CASE WHEN (ER.[status] <> 'running' AND ISNULL(ER.[status],'') <> 'running') 
                THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
        END as [sleep_time, sec] --Время сна в сек
      , NULLIF( CAST((ER.[logical_reads] + ER.[writes]) * 8 / 1024 as numeric(38,2)), 0) as [IO, MB]
      , CASE  ER.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommited'
        WHEN 2 THEN 'ReadCommited'
        WHEN 3 THEN 'Repetable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
        END as [transaction_isolation_level_desc]
      ,ER.[status]
      ,ES.[status] as [status_session]
      ,ER.[command]
      ,ER.[percent_complete]
      ,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
      , SUBSTRING(
                    (select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle]))
                  , ER.[statement_start_offset]/2+1
                  , (
                        CASE WHEN ((ER.[statement_start_offset]<0) OR (ER.[statement_end_offset]<0))
                                THEN DATALENGTH ((select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])))
                             ELSE ER.[statement_end_offset]
                        END
                        - ER.[statement_start_offset]
                    )/2 +1
                 ) as [CURRENT_REQUEST]
      ,(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
      ,(select top(1) [objectid] from sys.dm_exec_sql_text(ER.[sql_handle])) as [objectid]
      ,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
      ,NULL as [event_info]--(select top(1) [event_info] from sys.dm_exec_input_buffer(ES.[session_id], ER.[request_id])) as [event_info]
      ,ER.[wait_type]
      ,ES.[login_time]
      ,ES.[host_name]
      ,ES.[program_name]
      ,cast(ER.[wait_time]/1000 as decimal(18,3)) as [wait_timeSec]
      ,ER.[wait_time]
      ,ER.[last_wait_type]
      ,ER.[wait_resource]
      ,ER.[open_transaction_count]
      ,ER.[open_resultset_count]
      ,ER.[transaction_id]
      ,ER.[context_info]
      ,ER.[estimated_completion_time]
      ,ER.[cpu_time]
      ,ER.[total_elapsed_time]
      ,ER.[scheduler_id]
      ,ER.[task_address]
      ,ER.[reads]
      ,ER.[writes]
      ,ER.[logical_reads]
      ,ER.[text_size]
      ,ER.[language]
      ,ER.[date_format]
      ,ER.[date_first]
      ,ER.[quoted_identifier]
      ,ER.[arithabort]
      ,ER.[ansi_null_dflt_on]
      ,ER.[ansi_defaults]
      ,ER.[ansi_warnings]
      ,ER.[ansi_padding]
      ,ER.[ansi_nulls]
      ,ER.[concat_null_yields_null]
      ,ER.[transaction_isolation_level]
      ,ER.[lock_timeout]
      ,ER.[deadlock_priority]
      ,ER.[row_count]
      ,ER.[prev_error]
      ,ER.[nest_level]
      ,ER.[granted_query_memory]
      ,ER.[executing_managed_code]
      ,ER.[group_id]
      ,ER.[query_hash]
      ,ER.[query_plan_hash]
      ,EC.[most_recent_session_id]
      ,EC.[connect_time]
      ,EC.[net_transport]
      ,EC.[protocol_type]
      ,EC.[protocol_version]
      ,EC.[endpoint_id]
      ,EC.[encrypt_option]
      ,EC.[auth_scheme]
      ,EC.[node_affinity]
      ,EC.[num_reads]
      ,EC.[num_writes]
      ,EC.[last_read]
      ,EC.[last_write]
      ,EC.[net_packet_size]
      ,EC.[client_net_address]
      ,EC.[client_tcp_port]
      ,EC.[local_net_address]
      ,EC.[local_tcp_port]
      ,EC.[parent_connection_id]
      ,EC.[most_recent_sql_handle]
      ,ES.[host_process_id]
      ,ES.[client_version]
      ,ES.[client_interface_name]
      ,ES.[security_id]
      ,ES.[login_name]
      ,ES.[nt_domain]
      ,ES.[nt_user_name]
      ,ES.[memory_usage]
      ,ES.[total_scheduled_time]
      ,ES.[last_request_start_time]
      ,ES.[last_request_end_time]
      ,ES.[is_user_process]
      ,ES.[original_security_id]
      ,ES.[original_login_name]
      ,ES.[last_successful_logon]
      ,ES.[last_unsuccessful_logon]
      ,ES.[unsuccessful_logons]
      ,ES.[authenticating_database_id]
      ,ER.[sql_handle]
      ,ER.[statement_start_offset]
      ,ER.[statement_end_offset]
      ,ER.[plan_handle]
      ,NULL as [dop]--ER.[dop]
      ,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
      ,ER.[user_id]
      ,ER.[connection_id]
from sys.dm_exec_requests ER with(readuncommitted)
right join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id 
left join sys.dm_exec_connections EC  with(readuncommitted)
on EC.session_id = ES.session_id
where ER.[status] in ('suspended', 'running', 'runnable')
or exists (select top(1) 1 from sys.dm_exec_requests as ER0 where ER0.[blocking_session_id]=ES.[session_id])

This request displays all active requests and all those requests that explicitly block active requests.

All these and other useful scripts are implemented as representations in the SRV database, which is distributed freely.
For example, the first script came from the view [inf].[vBigQuery], and the second came from view [inf].[vRequests].

There are also various third-party solutions for query history.
I use Query Manager from Dbeaver:

and Query Execution History from SQL Tools, which is embedded in SSMS:

Источник

I am new to SQL Server Management Studio and am wondering: is there is a way to see which queries have been run against a database?

In the Activity monitor, there is a «Recent Expensive Queries» report but I’m guessing that isn’t all of the queries since I’m not seeing the ones I have run.

I am running SQL Server Express 2008 v 10.0.1600.22.

asked Mar 15, 2010 at 18:52

Use SQL Profiler and use a filter on it to get the most expensive queries.

answered Mar 15, 2010 at 18:56

Benjamin OrtuzarBenjamin Ortuzar

7,7616 gold badges41 silver badges46 bronze badges

Use the Activity Monitor. It’s the last toolbar in the top bar. It will show you a list of «Recent Expensive Queries». You can double-click them to see the execution plan, etc.

Loathing

5,0543 gold badges23 silver badges34 bronze badges

answered Mar 15, 2010 at 19:02

John SaundersJohn Saunders

160k26 gold badges247 silver badges396 bronze badges

If you want to see queries that are already executed there is no supported default way to do this. There are some workarounds you can try but don’t expect to find all.

You won’t be able to see SELECT statements for sure but there is a way to see other DML and DDL commands by reading transaction log (assuming database is in full recovery mode).

You can do this using DBCC LOG or fn_dblog commands or third party log reader like ApexSQL Log (note that tool comes with a price)

Now, if you plan on auditing statements that are going to be executed in the future then you can use SQL Profiler to catch everything.

answered Jun 11, 2013 at 9:46

DwoolkDwoolk

1,49113 silver badges8 bronze badges

You need a SQL profiler, which actually runs outside SQL Management Studio. If you have a paid version of SQL Server (like the developer edition), it should be included in that as another utility.

If you’re using a free edition (SQL Express), they have freeware profiles that you can download. I’ve used AnjLab’s profiler (available at http://sites.google.com/site/sqlprofiler), and it seemed to work well.

answered Mar 15, 2010 at 18:58

Mike MooneyMike Mooney

11.7k3 gold badges36 silver badges42 bronze badges

Run the following query from Management Studio on a running process:

DBCC inputbuffer( spid# )

This will return the SQL currently being run against the database for the SPID provided. Note that you need appropriate permissions to run this command.

This is better than running a trace since it targets a specific SPID. You can see if it’s long running based on its CPUTime and DiskIO.

Example to get details of SPID 64:

DBCC inputbuffer(64)

answered Sep 5, 2011 at 15:57

     SELECT *  FROM sys.dm_exec_sessions es
  INNER JOIN sys.dm_exec_connections ec
      ON es.session_id = ec.session_id
  CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) where es.session_id=65 under see text contain...

answered Apr 25, 2013 at 6:29

VisheVishe

3,3331 gold badge24 silver badges23 bronze badges

If you want SSMS to maintain a query history, use the SSMS Tool Pack add on.

If you want to monitor the SQL Server for currently running queries, use SQL PRofiler as other have already suggested.

answered Mar 15, 2010 at 19:17

Remus RusanuRemus Rusanu

287k40 gold badges437 silver badges567 bronze badges

More clear query, targeting Studio sql queries is :

SELECT text  FROM sys.dm_exec_sessions es
  INNER JOIN sys.dm_exec_connections ec
      ON es.session_id = ec.session_id
  CROSS APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) 
  where program_name like '%Query'

answered Jun 4, 2015 at 12:41

zhristzhrist

9818 silver badges27 bronze badges

Источник

В ходе расследования проблем возникает необходимость получить информацию о текущих исполняемых запросах в СУБД. Примером когда необходима подобная информация может служить разбор превышения времени ожидания на блокировке СУБД. В этой статье я создам подобную ситуацию и покажу какую информацию можно получить.

Подготовка

Суть примера заключается в том чтобы получить ожидание на блокировке СУБД. Для этого нам потребуется тестовая база данных, а также информация об имени таблицы в базе для составления SQL-запроса. С помощью обработки выполним запись в регистр и встанем на ожидании, не завершив транзакцию. В Management Studio выполним запрос чтения данных из регистра. Поскольку в предлагаемом примере уровень изоляции Read Committed, запрос на чтение будет ожидать освобождения ресурса, который заблокирован транзакцией записи.

Создание базы данных

Создадим базу данных, в которой установим режим управления блокировкой «Управляемый», основной режим запуска «Обычное приложение», режим использования модальных окон в «Использовать», режим совместимости «8.2.13». Добавим в базу регистр сведений «ТекущиеИсполняемыеЗапросы» (непериодический, независимый). В регистре добавим измерение: «Измерение1» (тип Число) и ресурс: «Ресурс1» (тип Число). Также создадим обработку «ЗаписьВРегистрВТранзакции» со следующим кодом:

НачатьТранзакцию();

НаборЗаписей = РегистрыСведений.ТекущиеИсполняемыеЗапросы.СоздатьНаборЗаписей();

НаборЗаписей.Отбор.Измерение1.Установить(1);

НоваяЗапись = НаборЗаписей.Добавить();

НоваяЗапись.Измерение1 = 1;

НоваяЗапись.Ресурс1 = 1;

НаборЗаписей.Записать(Истина);

Предупреждение(«Ожидание»);

ЗафиксироватьТранзакцию();

SQL-запрос

Для составления SQL-запроса нам потребуется имя таблицы базы данных, соответствующее регистру сведений. Для этого воспользуемся обработкой из статьи «Получение информации о структуре хранения базы данных в терминах 1С:Предприятие и СУБД». В моей базе данных имя этой таблицы: «_InfoRg243», напишем следующий запрос выборки всех данных из таблицы:

SELECT

FROM

dbo._InfoRg243

Эксперимент

Эксперимент довольно прост:

Открываем в 1С обработку «ЗаписьВРегистрВТранзакции» и встаем на модальном окне предупреждения
В Management Studio выполняем SQL-запрос, он должен начать выполняться и «повиснуть» на выполнении

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

Получение информации о текущих запросах

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

Представление	Описание
dm_exec_requests	Возвращает сведения о каждом из запросов, выполняющихся в SQL Server
dm_exec_sql_text	Возвращает текст пакета SQL, который определен указанным параметром
dm_exec_query_plan	Возвращает события инструкции Showplan в XML-формате для пакета, указанного в дескрипторе плана

Текст запроса приведен ниже, а также доступен во вложении к статье:

SELECT

DB_NAME(er.database_id) AS DB_Name,

er.start_time,

er.session_id,

er.status,

er.command,

— performance:

er.reads+er.writes AS IO,

er.logical_reads,

er.cpu_time,

er.total_elapsed_time,

— waits:

er.blocking_session_id,

er.wait_type,

er.wait_time,

er.wait_resource,

— query:

qt.text,

qp.query_plan

FROM

sys.dm_exec_requests as er

CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp

WHERE

er.database_id = DB_ID(‘MyBase’)

В условии отбора «MyBase» — имя моей базы данных в СУБД. Результат запроса представлен на картинке ниже:

Текущие исполняемые запросы

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

Ниже приведу описания колонок результата запроса:

Имя колонки	Описание
DB_Name	Имя базы данных к которой выполняется запрос
start_time	Отметка времени поступления запроса
session_id	Идентификатор сеанса, к которому относится данный запрос
status	Состояние запроса
command	Тип выполняемой команды
IO	Число операций чтения и записи, выполненных данным запросом
logical_reads	Число логических операций чтения, выполненных данным запросом
cpu_time	Время ЦП (в миллисекундах), затраченное на выполнение запроса
total_elapsed_time	Общее время, истекшее с момента поступления запроса (в миллисекундах)
blocking_session_id	Идентификатор сеанса, блокирующего запрос
wait_type	Если запрос в настоящий момент блокирован, в столбце содержится тип ожидания
wait_time	Если запрос в настоящий момент блокирован, в столбце содержится продолжительность текущего ожидания (в миллисекундах)
wait_resource	Если запрос в настоящий момент блокирован, в столбце указан ресурс, освобождения которого ожидает запрос
text	Текст поступившего запроса
query_plan	Предполагаемый план выполнения поступившего запроса

Анализ результата запроса

Интерпретируем результат запроса:

02.08.2016 в 12:50:18 сеансом с идентификатором «70» к базе данных «MyBase» был отправлен запрос типа SELECT. Текст запроса, а так же предполагаемый план выполнения запроса указаны в полях text и query_plan соответственно. Запрос был приостановлен (suspend) по причине ожидания получения разделяемой (S) блокировки на ресурсе «KEY: …». Данный ресурс захвачен блокирующим сеансом с идентификатором 69. Время ожидания, а так же общее время истекшее с момента получения запроса указаны в полях wait_time и total_elapsed_time соответственно.

Источник

Query history can be viewed using the system views:

sys.dm_exec_query_stats
sys.dm_exec_sql_text
sys.dm_exec_query_plan

For example, using the following query:

select  top(100)
        creation_time,
        last_execution_time,
        execution_count,
        total_worker_time/1000 as CPU,
        convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
        qs.total_elapsed_time/1000 as TotDuration,
        convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
        total_logical_reads as [Reads],
        total_logical_writes as [Writes],
        total_logical_reads+total_logical_writes as [AggIO],
        convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0)) as [AvgIO],
        [sql_handle],
        plan_handle,
        statement_start_offset,
        statement_end_offset,
        plan_generation_num,
        total_physical_reads,
        convert(money, total_physical_reads/(execution_count + 0.0)) as [AvgIOPhysicalReads],
        convert(money, total_logical_reads/(execution_count + 0.0)) as [AvgIOLogicalReads],
        convert(money, total_logical_writes/(execution_count + 0.0)) as [AvgIOLogicalWrites],
        query_hash,
        query_plan_hash,
        total_rows,
        convert(money, total_rows/(execution_count + 0.0)) as [AvgRows],
        total_dop,
        convert(money, total_dop/(execution_count + 0.0)) as [AvgDop],
        total_grant_kb,
        convert(money, total_grant_kb/(execution_count + 0.0)) as [AvgGrantKb],
        total_used_grant_kb,
        convert(money, total_used_grant_kb/(execution_count + 0.0)) as [AvgUsedGrantKb],
        total_ideal_grant_kb,
        convert(money, total_ideal_grant_kb/(execution_count + 0.0)) as [AvgIdealGrantKb],
        total_reserved_threads,
        convert(money, total_reserved_threads/(execution_count + 0.0)) as [AvgReservedThreads],
        total_used_threads,
        convert(money, total_used_threads/(execution_count + 0.0)) as [AvgUsedThreads],
        case 
            when sql_handle IS NULL then ' '
            else(substring(st.text,(qs.statement_start_offset+2)/2,(
                case
                    when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
                    else qs.statement_end_offset    
                end - qs.statement_start_offset)/2  ))
        end as query_text,
        db_name(st.dbid) as database_name,
        object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
        sp.[query_plan]
from sys.dm_exec_query_stats as qs with(readuncommitted)
cross apply sys.dm_exec_sql_text(qs.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(qs.[plan_handle]) as sp
WHERE st.[text] LIKE '%query%'

Current running queries can be seen using the following script:

select ES.[session_id]
      ,ER.[blocking_session_id]
      ,ER.[request_id]
      ,ER.[start_time]
      ,DateDiff(second, ER.[start_time], GetDate()) as [date_diffSec]
      , COALESCE(
                    CAST(NULLIF(ER.[total_elapsed_time] / 1000, 0) as BIGINT)
                   ,CASE WHEN (ES.[status] <> 'running' and isnull(ER.[status], '')  <> 'running') 
                            THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
                    END
                ) as [total_time, sec]
      , CAST(NULLIF((CAST(ER.[total_elapsed_time] as BIGINT) - CAST(ER.[wait_time] AS BIGINT)) / 1000, 0 ) as bigint) as [work_time, sec]
      , CASE WHEN (ER.[status] <> 'running' AND ISNULL(ER.[status],'') <> 'running') 
                THEN  DATEDIFF(ss,0,getdate() - nullif(ES.[last_request_end_time], '1900-01-01T00:00:00.000'))
        END as [sleep_time, sec] --Время сна в сек
      , NULLIF( CAST((ER.[logical_reads] + ER.[writes]) * 8 / 1024 as numeric(38,2)), 0) as [IO, MB]
      , CASE  ER.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommited'
        WHEN 2 THEN 'ReadCommited'
        WHEN 3 THEN 'Repetable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
        END as [transaction_isolation_level_desc]
      ,ER.[status]
      ,ES.[status] as [status_session]
      ,ER.[command]
      ,ER.[percent_complete]
      ,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
      , SUBSTRING(
                    (select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle]))
                  , ER.[statement_start_offset]/2+1
                  , (
                        CASE WHEN ((ER.[statement_start_offset]<0) OR (ER.[statement_end_offset]<0))
                                THEN DATALENGTH ((select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])))
                             ELSE ER.[statement_end_offset]
                        END
                        - ER.[statement_start_offset]
                    )/2 +1
                 ) as [CURRENT_REQUEST]
      ,(select top(1) [text] from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
      ,(select top(1) [objectid] from sys.dm_exec_sql_text(ER.[sql_handle])) as [objectid]
      ,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
      ,NULL as [event_info]--(select top(1) [event_info] from sys.dm_exec_input_buffer(ES.[session_id], ER.[request_id])) as [event_info]
      ,ER.[wait_type]
      ,ES.[login_time]
      ,ES.[host_name]
      ,ES.[program_name]
      ,cast(ER.[wait_time]/1000 as decimal(18,3)) as [wait_timeSec]
      ,ER.[wait_time]
      ,ER.[last_wait_type]
      ,ER.[wait_resource]
      ,ER.[open_transaction_count]
      ,ER.[open_resultset_count]
      ,ER.[transaction_id]
      ,ER.[context_info]
      ,ER.[estimated_completion_time]
      ,ER.[cpu_time]
      ,ER.[total_elapsed_time]
      ,ER.[scheduler_id]
      ,ER.[task_address]
      ,ER.[reads]
      ,ER.[writes]
      ,ER.[logical_reads]
      ,ER.[text_size]
      ,ER.[language]
      ,ER.[date_format]
      ,ER.[date_first]
      ,ER.[quoted_identifier]
      ,ER.[arithabort]
      ,ER.[ansi_null_dflt_on]
      ,ER.[ansi_defaults]
      ,ER.[ansi_warnings]
      ,ER.[ansi_padding]
      ,ER.[ansi_nulls]
      ,ER.[concat_null_yields_null]
      ,ER.[transaction_isolation_level]
      ,ER.[lock_timeout]
      ,ER.[deadlock_priority]
      ,ER.[row_count]
      ,ER.[prev_error]
      ,ER.[nest_level]
      ,ER.[granted_query_memory]
      ,ER.[executing_managed_code]
      ,ER.[group_id]
      ,ER.[query_hash]
      ,ER.[query_plan_hash]
      ,EC.[most_recent_session_id]
      ,EC.[connect_time]
      ,EC.[net_transport]
      ,EC.[protocol_type]
      ,EC.[protocol_version]
      ,EC.[endpoint_id]
      ,EC.[encrypt_option]
      ,EC.[auth_scheme]
      ,EC.[node_affinity]
      ,EC.[num_reads]
      ,EC.[num_writes]
      ,EC.[last_read]
      ,EC.[last_write]
      ,EC.[net_packet_size]
      ,EC.[client_net_address]
      ,EC.[client_tcp_port]
      ,EC.[local_net_address]
      ,EC.[local_tcp_port]
      ,EC.[parent_connection_id]
      ,EC.[most_recent_sql_handle]
      ,ES.[host_process_id]
      ,ES.[client_version]
      ,ES.[client_interface_name]
      ,ES.[security_id]
      ,ES.[login_name]
      ,ES.[nt_domain]
      ,ES.[nt_user_name]
      ,ES.[memory_usage]
      ,ES.[total_scheduled_time]
      ,ES.[last_request_start_time]
      ,ES.[last_request_end_time]
      ,ES.[is_user_process]
      ,ES.[original_security_id]
      ,ES.[original_login_name]
      ,ES.[last_successful_logon]
      ,ES.[last_unsuccessful_logon]
      ,ES.[unsuccessful_logons]
      ,ES.[authenticating_database_id]
      ,ER.[sql_handle]
      ,ER.[statement_start_offset]
      ,ER.[statement_end_offset]
      ,ER.[plan_handle]
      ,NULL as [dop]--ER.[dop]
      ,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
      ,ER.[user_id]
      ,ER.[connection_id]
from sys.dm_exec_requests ER with(readuncommitted)
right join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id 
left join sys.dm_exec_connections EC  with(readuncommitted)
on EC.session_id = ES.session_id
where ER.[status] in ('suspended', 'running', 'runnable')
or exists (select top(1) 1 from sys.dm_exec_requests as ER0 where ER0.[blocking_session_id]=ES.[session_id])

This request displays all active requests and all those requests that explicitly block active requests.

There are also various third-party solutions for query history.
I use Query Manager from Dbeaver:

and Query Execution History from SQL Tools, which is embedded in SSMS:

Источник

Remove From My Forums

Question
Is there a way to get history of queries run (say DML queries 1 month ago) , in SQL Server/SQL Azure ?

OTHER THAN —

SELECT SQLTEXT.text, STATS.last_execution_time
FROM sys.dm_exec_query_stats STATS
CROSS APPLY sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE STATS.last_execution_time > GETDATE()-1
ORDER BY STATS.last_execution_time DESC

————————————————————————

Answers

The right thing to do from security POV is to enable the auditing feature. it exists on both SQL Server and Azure SQL DB. it will give you long term view of the good old who did what when question.

Thanks, Tomer (MSFT)
- Marked as answer by
  
  Monday, September 5, 2016 5:52 AM

Источник

Подготовка

Создание базы данных

SQL-запрос

Эксперимент

Получение информации о текущих запросах

Анализ результата запроса

Question

Answers