Запрос статистики SQL без параметра SET

Question

Я пытаюсь захватить некоторые статистические параметры для целей регистрации. "SET параметры" не являются опцией (то есть установить время статистики).

Поэтому я попытался запросить некоторые DMV:

select '3AAAAAAAAAAA';
--no GO-statement here

select 
    total_worker_time/execution_count AS [Avg CPU Time],
    total_elapsed_time as [Elapsed Time],
    total_rows as [Total rows],
    st.text,
    (select cast(text as varchar(4000)) from ::fn_get_sql((select sql_handle from sys.sysprocesses where spid = @@spid)))
from sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
--where  ???
order by creation_time desc

Информация, полученная здесь, - почти то, что мне нужно - но:

Запрос отображается только в результате DMV, когда он выполняется в последнем выполненном GO-блоке (но не в реальном). Это не то, что мне нужно. Мне нужно что-то вроде @@error или @@rowcount, которое доступно в том же GO-блоке и содержит истекшее время и время процессора. Есть идеи, как запросить эту информацию из последнего отчета?

Если это можно решить: я бы хотел запросить "последнее" выполнение оператора в сеансе (@@spid), не записывая оператор дважды.

---

Обновление по вопросу:

Этот запрос работает "на сеанс" и будет перечислять запрашиваемые значения (хотя тривиальные запросы отсутствуют). Top 1 всегда возвращает значения последнего оператора (не соответствует действительности, если он запускается через exec @SQL, что создает другой сеанс):

print 'hello';

select top 10 'my personal identifier: 1', * FROM sys.messages;

select top 20 'my personal identifier: 2', * FROM sys.messages;

print 'hello';
select 'hello';

select top 30 'my personal identifier: 3', * FROM sys.tables;

select top 1
    total_worker_time/execution_count AS [Avg CPU Time],
    total_elapsed_time as [Elapsed Time],
    total_rows as [Total rows],
    substring(st.text, (qs.statement_start_offset / 2) + 1, (case when qs.statement_end_offset = -1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset ) / 2 + 5) as [executing statement] 
from sys.dm_exec_query_stats AS qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) AS st
where st.text = (select cast(text as varchar(4000)) from ::fn_get_sql((select sql_handle from sys.sysprocesses where spid = @@spid)))
order by qs.statement_start_offset desc;

Фильтр (условие where) кажется грубым и не очень надежным. Есть ли способ улучшить это?

Answer 1

Я пытаюсь ответить сам (Йероен Мостерт - большое спасибо за вашу помощь!) - вопрос остается без ответа (см. Ниже):

Следующая функция должна давать вам процессор, время выполнения, количество операций ввода-вывода, число или строки последнего оператора, который был выполнен в реальном сеансе, если оператор достаточно сложен, чтобы вызвать генерацию плана SQL. То есть после простых команд печати набор результатов будет enpty. Даже после выполнения хранимых процедур, если они открывают новый сеанс (т.е. после exec sp_executesql, набор результатов будет пустым).

Для "среднего" SQL-оператора следующий запрос должен привести к набору строк, содержащему информацию, которую вы в противном случае получили бы через установленное время статистики и включить статистику io.

drop function if exists dbo.ufn_lastsql_resources ;
go

CREATE FUNCTION dbo.ufn_lastsql_resources (@session_id int)
RETURNS TABLE  
AS  
return
select 
    top 1

    convert(char(10), getdate(), 121) + ' ' + substring(convert(char(40), getdate(), 121), 12,12) + ',' as [Time stamp], 
    cast(cast((last_worker_time / execution_count / 1000. ) as numeric(9,2)) as varchar(100)) + ','     as [Avg CPU Time in ms],
    cast(cast((last_elapsed_time / 1000. ) as numeric(9,2)) as varchar(100)) + ','                      as [Elapsed Time in ms],
    cast(last_rows as varchar(100)) + ','                                                               as [Total rows],
    cast(substring(st.text, (statement_start_offset / 2) + 1, (case when statement_end_offset = -1 then datalength(st.text) else statement_end_offset end - statement_start_offset ) / 2 + 2) as varchar(4000)) + ',' 
                                                                                                        as [executing statement],

    last_physical_reads + last_logical_reads                                                            as [Reads],
    last_logical_writes                                                                                 as [Writes],

    --last_grant_kb,
    --last_used_grant_kb,
    --last_ideal_grant_kb,

    --last_reserved_threads,
    --last_used_threads

    @session_id                                                                                         as spid

from 
    (
    select qs.*
    from sys.dm_exec_query_stats as qs
    inner join sys.dm_exec_requests as eq 
    on  qs.sql_handle  = eq.sql_handle
    and qs.plan_handle = eq.plan_handle 
    and eq.session_id  = @session_id
    ) a
cross apply sys.dm_exec_sql_text(a.sql_handle) AS st
where 
    substring(st.text, (statement_start_offset / 2) + 1, (case when statement_end_offset = -1 then datalength(st.text) else statement_end_offset end - statement_start_offset ) / 2 + 2) not like '%ufn_lastsql_resources%'
order by 
    last_execution_time desc, statement_start_offset desc
go

Скорее всего, есть более элегантные способы сделать это. Возможно, можно написать что-то, что будет работать должным образом даже с операторами, использующими опцию (перекомпиляция) или с exec (@sql). В любом случае, мне кажется, что я работаю на SQL Server 2016 и 2012. Вам необходимо разрешение VIEW SERVER STATE на сервере, Чтобы вызвать функцию, попробуйте:

drop table if exists #t1
select top 10 'statement 1' a, * into #t1 from sys.messages
select 1, * from dbo.ufn_lastsql_resources(@@spid) option (recompile)


drop table if exists #t2
select top 20 'statement 2' a, * into #t2 from sys.messages 
--select 2, * from dbo.ufn_lastsql_resources(@@spid)

select top 3 'statement 3' a, * from sys.messages 
select 3, * from dbo.ufn_lastsql_resources(@@spid) option (recompile)

Вопрос напоминает без ответа, так как путь не работает должным образом. Нет уверенности в том, что нужно извлечь правильный оператор из пакета (первые 1 в сеансе, упорядоченные по времени last_execution и последнему в пакете. Кажется, это неправильный порядок. Поскольку планы используются повторно, это единственный способ, я выяснил, работать.)