在SQL事件探查器中查找特定LINQ查询的巧妙技巧 [英] Clever tricks to find specific LINQ queries in SQL Profiler

问题描述

由于有时可能会创建疯狂的SQL，因此对LINQ查询及其执行计划进行概要分析尤为重要.

Profiling LINQ queries and their execution plans is especially important due to the crazy SQL that can sometimes be created.

我经常发现我需要跟踪特定的查询，并且在查询分析器中很难找到.我经常在具有大量正在运行的事务的数据库(有时是生产服务器)上执行此操作-因此仅打开Profiler不好.

I often find that I need to track a specific query and have a hard time finding in query analyzer. I often do this on a database which has a lot of running transactions (sometimes production server) - so just opening Profiler is no good.

我还发现tryin无法使用DataContext进行跟踪，因为它没有给我SQL我可以实际执行.

I've also found tryin to use the DataContext to trace inadequate, since it doesnt give me SQL I can actually execute myself.

到目前为止，我最好的策略是在查询中添加一个随机"数字，并在跟踪中进行过滤.

My best strategy so far is to add in a 'random' number to my query, and filter for it in the trace.

LINQ:

where o.CompletedOrderID != "59872547981"

分析器过滤器:

'TextData' like '%59872547981'

这在一些警告的情况下仍然有效:

This works fine with a couple caveats :

• 我必须谨记要删除条件，或选择不会对查询计划造成太大影响的内容.是的，我知道将其留在麻烦中.
据我所知，即使使用这种方法，我也需要为需要跟踪的每个LINQ查询启动新的跟踪.如果转到现有文件的文件>属性"，则无法更改过滤条件.

• I have to be careful to remember to remove the criteria, or pick something that wont affect the query plan too much. Yes I know leaving it in is asking for trouble.
• As far as I can tell though, even with this approach I need to start a new trace for every LINQ query I need to track. If I go to 'File > Properties' for an existing trace I cannot change the filter criteria.

您无需费力就可以在应用程序中运行查询，并在Profiler中看到它.只是希望其他人想出一种比这更好的方法，或者至少建议使用比列查询更少的危险"令牌来进行搜索.

You cant beat running a query in your app and seeing it pop up in the Profiler without any extra effort. Was just hoping someone else had come up with a better way than this, or at least suggest a less 'dangerous' token to search for than a query on a column.

推荐答案

使用where子句也许不是最好的选择，因为它可以并且会影响查询的执行计划.

Messing with the where clause is maybe not the best thing to do since it can and will affect the execution plans for your queries.

在投影到匿名类时做一些时髦的事情-使用唯一的静态列名或不会影响执行计划的事情. (这样一来，您可以在生产代码中保留它的原样，以防日后需要对生产代码进行任何概要分析时...)

Do something funky with projection into anonymous classes instead - use a unique static column name or something that will not affect the execution plan. (That way you can leave it intact in production code in case you later need to do any profiling of production code...)

from someobject in dc.SomeTable
where someobject.xyz = 123
select new { MyObject = someobject, QueryTraceID1234132412='boo' }

这篇关于在SQL事件探查器中查找特定LINQ查询的巧妙技巧的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！