捕获变量中的SQL统计时间

I would like to do some automated SQL Profiling of some advanced queries through a large range of variable options. What I'm hoping to do is something similar to the following

我想通过大量的变量选项对一些高级查询进行一些自动SQL分析。我希望做的是类似于以下内容

set statistics io on
set statistics time on

select *
from MyTable

set statistics time off
set statistics io off

--Somehow return the statistics for this query back to the calling C# code
-- so that I can log it for displaying in a report among other queries

I have tried using the following however I'm unsure of how to find the Query handle for my ran query to extract out the desired execution times

我已经尝试使用以下但是我不确定如何为我的运行查询找到查询句柄以提取所需的执行时间

select * from sys.dm_exec_query_stats

So really my question has 2 solutions. 1) Is it posible to simply capture the statistcs return message? 2) Or is it possible to find out my sql query handle so that I can query the dm_exec_query_stats table?

所以我的问题确实有2个解决方案。 1)简单地捕获statistcs返回消息是否可行? 2)或者是否可以找到我的sql查询句柄,以便我可以查询dm_exec_query_stats表?

I should also mention I have considered doing the following but I'm hoping for a more complete solution.

我还应该提到我考虑过以下但我希望有一个更完整的解决方案。

declare @StartTime datetime = getdate();
Select * from myTable;
declare @Dur datetime = getdate() - @StartTime;

1 个解决方案

#1

Of course, as soon as I turn to * I figure it out myself!

当然,只要我转向*,我就自己弄清楚了!

In case anyone in the future is looking for this as well...Here is a solution where you simply enter your ran query in the where clause and all of the performance metrics are returned. Please keep in mind that the times are in microseconds not milliseconds

如果将来有人也在寻找这个...这是一个解决方案,您只需在where子句中输入您的运行查询,并返回所有性能指标。请记住,时间以微秒为单位,而不是毫秒

SELECT *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
where st.text = 'Your Query Here'