Is there a good way to time SQL queries when using Linq to SQL? I really like logging feature, but it would be great if you could somehow also time that query. Any ideas?
在使用Linq to SQL时,有一种很好的方法来计算SQL查询吗?我真的很喜欢日志记录功能,但如果你能以某种方式计算查询时间,那将会很棒。有任何想法吗?
6 个解决方案
#1
5
SQL Profiler to get the query and the time, and also Execution Path in Query analyzer to see where the bottlenecks are.
SQL Profiler用于获取查询和时间,以及查询分析器中的执行路径以查看瓶颈所在。
#2
9
As two people already said, SQL Profiler is the out-of-the-box tool to use for that. I don't want to be an echo but I wanted to elaborate a bit more in detail: Not only does it provide the actual timings from SQL Server (as opposed to timing from the app-side where network i/o, connection and connection pool timings are added to the cake) but it also gives you the [often more important] I/O statistics figures, locking info (as needed) etc.
正如两个人已经说过的那样,SQL Profiler是用于此的开箱即用工具。我不想成为一个回声,但我想详细说明一下:它不仅提供了SQL Server的实际时序(而不是来自应用程序端的网络i / o,连接和连接的时序)池时间被添加到蛋糕中)但它也为您提供[通常更重要的] I / O统计数据,锁定信息(根据需要)等。
The reason I/O statistics are important is that a very expensive query may run fast while consuming excessive amounts of server resources. If for example a query that is executed often hits large tables and there are no matching indexes resulting table scans, the affected tables will be cached in memory by SQL Server (if it can). This can sometimes cause the same query to execute blazingly fast while in effect it is harming the rest of the system/app/db by eating up server resources.
I / O统计信息很重要的原因是,在消耗过多的服务器资源时,非常昂贵的查询可能会快速运行。例如,如果执行的查询经常遇到大型表,并且没有匹配的索引导致表扫描,则受影响的表将由SQL Server缓存在内存中(如果可以)。这有时会导致相同的查询执行速度极快,而实际上它会通过占用服务器资源来损害系统/ app / db的其余部分。
Locking info is almost as important -> tiny queries doing PK lookups for a single record can have bad timings due to locking and blocking. I read somewhere that this very site was plagued by deadlocks in its' early beta days. SQL Profiler is your friend for identifying and resolving problems caused by locking too.
锁定信息几乎同样重要 - >对于单个记录执行PK查找的微小查询可能由于锁定和阻塞而导致错误的计时。我在某个地方看到这个网站在其早期测试版的日子里一直受到僵局的困扰。 SQL Profiler是您的朋友,用于识别和解决由锁定引起的问题。
To summarize it; whether you use L2S, EF, plain ADO - if you want to make sure your app "behaves nice" towards the database always have SQL Profiler ready during development and testing. It pays off!
总结一下;您是否使用L2S,EF,普通ADO - 如果您想确保您的应用程序对数据库“表现良好”,请始终在开发和测试期间准备好SQL Profiler。它得到了回报!
Edit: Since I wrote the answer above I have developed a new runtime profiling tool for L2S that bring the best of both worlds together; I/O stats and server-side timings from SQL Server, SQL Server execution plan, SQL Server's "missing index" alerts, combined with the managed call stack to make it easy to find what code generated a certain query, and some advanced filter options to log only queries that fulfill certain criteria. Additionally, the logging component can be distributed with apps to make runtime query profiling in live customer environments easier. The tool can be downloaded from:
编辑:自从我写完上面的答案后,我为L2S开发了一个新的运行时分析工具,它将两个世界的优点结合在一起;来自SQL Server,SQL Server执行计划,SQL Server的“缺失索引”警报的I / O统计信息和服务器端计时,结合托管调用堆栈,以便于查找生成某个查询的代码,以及一些高级过滤器选项仅记录满足特定条件的查询。此外,日志记录组件可与应用程序一起分发,以便在实时客户环境中更轻松地进行运行时查询分析。该工具可以从以下位置下载:
http://www.huagati.com/L2SProfiler/ where you can also get a free 45-day trial license.
http://www.huagati.com/L2SProfiler/您还可以获得免费的45天试用许可证。
A longer background description and intro to the tool is also posted here:
http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
此处还发布了更长的背景说明和工具介绍:http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
...and a sample/walkthrough of using some of the more advanced filter options is available here:
http://huagati.blogspot.com/2009/08/walkthrough-of-newest-filters-and.html
...此处提供了使用一些更高级过滤器选项的示例/演练:http://huagati.blogspot.com/2009/08/walkthrough-of-newest-filters-and.html
#3
2
You could use a System.Diagnostics.Stopwatch it will allow you to track the time the query executes. Just remember that Linq->SQL queries aren't executed until you enumerate over them. Also note that if you are logging to Console.Out there will be a significant performance hit.
您可以使用System.Diagnostics.Stopwatch它可以跟踪查询执行的时间。请记住,在您枚举Linq-> SQL查询之前,不会执行它们。另请注意,如果您要登录到Console.Out,将会有重大的性能影响。
#4
1
What you could do is add a custom TextWriter implementation to the DataContext.Log which will write the generated sql to a file or memory. Then loop through those queries, executing them with raw ADO.NET code, surrounding each with a stopwatch.
你可以做的是将自定义TextWriter实现添加到DataContext.Log,它将生成的sql写入文件或内存。然后遍历这些查询,使用原始ADO.NET代码执行它们,用秒表围绕每个查询。
I've used a similar technique before because it seemed whenever I was developing some code I never had Profiler open, and it was really easy to output those results to a HTML page. Sure your executing them twice per website request, but its helpful to see execution times asap instead of waiting until you catch something in Profiler.
之前我曾经使用过类似的技术,因为每当我开发一些我从未打开过Profiler的代码时,我很容易将这些结果输出到HTML页面。当然,每次网站请求都要执行两次,但是有助于尽快查看执行时间,而不是等到你在Profiler中捕获一些东西。
Also if your going to the SQL Tool route I would recommend googling "slowest query DMV" and getting a stored procedure that can give you stats on the slowest queries in your db. Its not always easy to scroll through profiler results to find the bad queries. Also with the right queries over sql 2005's dmv you can also do ordering by lets say cpu vs. time and so forth.
此外,如果您转到SQL工具路线,我建议使用Google搜索“最慢查询DMV”并获取一个存储过程,该过程可以为您提供有关数据库中最慢查询的统计信息。滚动浏览器结果并不总是很容易找到错误的查询。对sql 2005的dmv进行正确的查询,你也可以通过让我们说cpu与时间等来进行排序。
#5
0
We use SQL Profiler to test our queries with LLBLGen Pro.
我们使用SQL Profiler来测试LLBLGen Pro的查询。
#6
0
The best is to log the queries to a file and them use SQL Profiler to time them and tweak the indexes for the queries.
最好的方法是将查询记录到文件中,然后使用SQL事件探查器为它们计时并调整查询的索引。
#1
5
SQL Profiler to get the query and the time, and also Execution Path in Query analyzer to see where the bottlenecks are.
SQL Profiler用于获取查询和时间,以及查询分析器中的执行路径以查看瓶颈所在。
#2
9
As two people already said, SQL Profiler is the out-of-the-box tool to use for that. I don't want to be an echo but I wanted to elaborate a bit more in detail: Not only does it provide the actual timings from SQL Server (as opposed to timing from the app-side where network i/o, connection and connection pool timings are added to the cake) but it also gives you the [often more important] I/O statistics figures, locking info (as needed) etc.
正如两个人已经说过的那样,SQL Profiler是用于此的开箱即用工具。我不想成为一个回声,但我想详细说明一下:它不仅提供了SQL Server的实际时序(而不是来自应用程序端的网络i / o,连接和连接的时序)池时间被添加到蛋糕中)但它也为您提供[通常更重要的] I / O统计数据,锁定信息(根据需要)等。
The reason I/O statistics are important is that a very expensive query may run fast while consuming excessive amounts of server resources. If for example a query that is executed often hits large tables and there are no matching indexes resulting table scans, the affected tables will be cached in memory by SQL Server (if it can). This can sometimes cause the same query to execute blazingly fast while in effect it is harming the rest of the system/app/db by eating up server resources.
I / O统计信息很重要的原因是,在消耗过多的服务器资源时,非常昂贵的查询可能会快速运行。例如,如果执行的查询经常遇到大型表,并且没有匹配的索引导致表扫描,则受影响的表将由SQL Server缓存在内存中(如果可以)。这有时会导致相同的查询执行速度极快,而实际上它会通过占用服务器资源来损害系统/ app / db的其余部分。
Locking info is almost as important -> tiny queries doing PK lookups for a single record can have bad timings due to locking and blocking. I read somewhere that this very site was plagued by deadlocks in its' early beta days. SQL Profiler is your friend for identifying and resolving problems caused by locking too.
锁定信息几乎同样重要 - >对于单个记录执行PK查找的微小查询可能由于锁定和阻塞而导致错误的计时。我在某个地方看到这个网站在其早期测试版的日子里一直受到僵局的困扰。 SQL Profiler是您的朋友,用于识别和解决由锁定引起的问题。
To summarize it; whether you use L2S, EF, plain ADO - if you want to make sure your app "behaves nice" towards the database always have SQL Profiler ready during development and testing. It pays off!
总结一下;您是否使用L2S,EF,普通ADO - 如果您想确保您的应用程序对数据库“表现良好”,请始终在开发和测试期间准备好SQL Profiler。它得到了回报!
Edit: Since I wrote the answer above I have developed a new runtime profiling tool for L2S that bring the best of both worlds together; I/O stats and server-side timings from SQL Server, SQL Server execution plan, SQL Server's "missing index" alerts, combined with the managed call stack to make it easy to find what code generated a certain query, and some advanced filter options to log only queries that fulfill certain criteria. Additionally, the logging component can be distributed with apps to make runtime query profiling in live customer environments easier. The tool can be downloaded from:
编辑:自从我写完上面的答案后,我为L2S开发了一个新的运行时分析工具,它将两个世界的优点结合在一起;来自SQL Server,SQL Server执行计划,SQL Server的“缺失索引”警报的I / O统计信息和服务器端计时,结合托管调用堆栈,以便于查找生成某个查询的代码,以及一些高级过滤器选项仅记录满足特定条件的查询。此外,日志记录组件可与应用程序一起分发,以便在实时客户环境中更轻松地进行运行时查询分析。该工具可以从以下位置下载:
http://www.huagati.com/L2SProfiler/ where you can also get a free 45-day trial license.
http://www.huagati.com/L2SProfiler/您还可以获得免费的45天试用许可证。
A longer background description and intro to the tool is also posted here:
http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
此处还发布了更长的背景说明和工具介绍:http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
...and a sample/walkthrough of using some of the more advanced filter options is available here:
http://huagati.blogspot.com/2009/08/walkthrough-of-newest-filters-and.html
...此处提供了使用一些更高级过滤器选项的示例/演练:http://huagati.blogspot.com/2009/08/walkthrough-of-newest-filters-and.html
#3
2
You could use a System.Diagnostics.Stopwatch it will allow you to track the time the query executes. Just remember that Linq->SQL queries aren't executed until you enumerate over them. Also note that if you are logging to Console.Out there will be a significant performance hit.
您可以使用System.Diagnostics.Stopwatch它可以跟踪查询执行的时间。请记住,在您枚举Linq-> SQL查询之前,不会执行它们。另请注意,如果您要登录到Console.Out,将会有重大的性能影响。
#4
1
What you could do is add a custom TextWriter implementation to the DataContext.Log which will write the generated sql to a file or memory. Then loop through those queries, executing them with raw ADO.NET code, surrounding each with a stopwatch.
你可以做的是将自定义TextWriter实现添加到DataContext.Log,它将生成的sql写入文件或内存。然后遍历这些查询,使用原始ADO.NET代码执行它们,用秒表围绕每个查询。
I've used a similar technique before because it seemed whenever I was developing some code I never had Profiler open, and it was really easy to output those results to a HTML page. Sure your executing them twice per website request, but its helpful to see execution times asap instead of waiting until you catch something in Profiler.
之前我曾经使用过类似的技术,因为每当我开发一些我从未打开过Profiler的代码时,我很容易将这些结果输出到HTML页面。当然,每次网站请求都要执行两次,但是有助于尽快查看执行时间,而不是等到你在Profiler中捕获一些东西。
Also if your going to the SQL Tool route I would recommend googling "slowest query DMV" and getting a stored procedure that can give you stats on the slowest queries in your db. Its not always easy to scroll through profiler results to find the bad queries. Also with the right queries over sql 2005's dmv you can also do ordering by lets say cpu vs. time and so forth.
此外,如果您转到SQL工具路线,我建议使用Google搜索“最慢查询DMV”并获取一个存储过程,该过程可以为您提供有关数据库中最慢查询的统计信息。滚动浏览器结果并不总是很容易找到错误的查询。对sql 2005的dmv进行正确的查询,你也可以通过让我们说cpu与时间等来进行排序。
#5
0
We use SQL Profiler to test our queries with LLBLGen Pro.
我们使用SQL Profiler来测试LLBLGen Pro的查询。
#6
0
The best is to log the queries to a file and them use SQL Profiler to time them and tweak the indexes for the queries.
最好的方法是将查询记录到文件中,然后使用SQL事件探查器为它们计时并调整查询的索引。