继续这篇文章 - 我还有一些问题我希望有人可以帮助我:
-
有没有办法选择特定的数据库和/或表来获取查询或将其添加为列?
-
在我的查询中,有一些变量显示为@P1或@GUID。有没有办法获取在那里插入的数据?
我只使用 Express,也无法访问 SQL Profiler。
Continuing from this post - I have some more questions I hope someone can help me with:
-
Is there a way to select a specific database and/or table to get the queries from or add this as a column?
-
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.
发布评论
评论(2)
sys.dm_exec_sql_text
有一个dbid
列,因此您可以对其进行过滤。例如,我从另一个答案中获取查询,并添加了一个 where 子句来过滤针对master
的查询:请注意,并非所有查询都具有正确的数据库上下文(或根本没有数据库上下文)。例如,如果您有一个查询连接不同数据库中的两个表,您将只会看到一个 dbid - 它将是执行上下文,并且可能是也可能不是查询中引用的数据库之一。因此,应用过滤器实际上可能会隐藏您感兴趣的查询。
您可以通过深入研究其他 DMO 中的 XML 来获取参数,例如
sys.dm_exec_cached_plans
和sys.dm_exec_query_plan
。如果您已经为捕获的查询制定了执行计划,那么使用 SQL Sentry Plan Explorer 而不是自己费力地浏览大量 XML。免责声明:我在 SQL Sentry 工作,他们向社区提供免费工具。
sys.dm_exec_sql_text
has adbid
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 againstmaster
: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
andsys.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.
仅供参考,您知道,尽管 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.