查看 SQL Server Management Studio 中特定执行的查询?

发布于 2025-01-08 05:47:12 字数 321 浏览 0 评论 0 原文

继续这篇文章 - 我还有一些问题我希望有人可以帮助我:

  1. 有没有办法选择特定的数据库和/或表来获取查询或将其添加为列?

  2. 在我的查询中,有一些变量显示为@P1或@GUID。有没有办法获取在那里插入的数据?

我只使用 Express,也无法访问 SQL Profiler。

Continuing from this post - I have some more questions I hope someone can help me with:

  1. Is there a way to select a specific database and/or table to get the queries from or add this as a column?

  2. In my queries there are some variables shown as @P1 or @GUID. Is there a way to get the data that was inserted there?

I am only using Express to I also have no access to SQL Profiler.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

黯然#的苍凉 2025-01-15 05:47:12

sys.dm_exec_sql_text 有一个 dbid 列,因此您可以对其进行过滤。例如,我从另一个答案中获取查询,并添加了一个 where 子句来过滤针对 master 的查询:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('master')
ORDER BY deqs.last_execution_time DESC

请注意,并非所有查询都具有正确的数据库上下文(或根本没有数据库上下文)。例如,如果您有一个查询连接不同数据库中的两个表,您将只会看到一个 dbid - 它将是执行上下文,并且可能是也可能不是查询中引用的数据库之一。因此,应用过滤器实际上可能会隐藏您感兴趣的查询。

您可以通过深入研究其他 DMO 中的 XML 来获取参数,例如 sys.dm_exec_cached_planssys.dm_exec_query_plan。如果您已经为捕获的查询制定了执行计划,那么使用 SQL Sentry Plan Explorer 而不是自己费力地浏览大量 XML。

免责声明:我在 SQL Sentry 工作,他们向社区提供免费工具。

sys.dm_exec_sql_text has a dbid column, so you can filter on that. For example I took the query from the other answer and added a where clause filtering on queries against master:

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.dbid = DB_ID('master')
ORDER BY deqs.last_execution_time DESC

Note that not all queries have the right database context (or a database context at all). For example, if you have a query that joins two tables that are in different databases, you will only see one dbid - it will either be the executing context and may or may not be one of the databases referenced in the query. So applying the filter might actually hide queries you are interested in.

You may be able to obtain parameters by digging into the XML from other DMOs such as sys.dm_exec_cached_plans and sys.dm_exec_query_plan. If you have an execution plan already for a query that you've captured, it is going to be much easier to use a tool like SQL Sentry Plan Explorer than to wade through gobs of XML yourself.

Disclaimer: I work for SQL Sentry, who provides the free tool to the community.

北音执念 2025-01-15 05:47:12

仅供参考,您知道,尽管 SQL Express 不包含配置文件,但如果您有权访问它,则可以使用它。

Just an FYI, you know that even though SQL Express doesn't include profile, if you have access to it you can use.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文