检测数据库访问
Jeff 在其中一个播客中提到，他经常做的一件事就是为数据库调用插入仪器，以便他可以判断哪些查询导致速度缓慢等。这是我过去使用 SQL Profiler 测量过的东西，但是我感兴趣的是其他人使用什么策略将其包含在应用程序中。
这只是在每个数据库调用中包含一个计时器并记录结果的情况，还是有一种“更简洁”的方法？ 也许已经有一个框架可以为您执行此操作，或者是否有一个我可以在例如 Linq-to-SQL 中启用的标志来提供类似的功能。
我主要使用 C#，但也有兴趣查看不同语言的方法，并且我对通过 SQL Profiler 等数据库平台方法执行此操作的“代码”方式更感兴趣。
Jeff mentioned in one of the podcasts that one of the things he always does is put in instrumentation for database calls, so that he can tell what queries are causing slowness etc. This is something I've measured in the past using SQL Profiler, but I'm interested in what strategies other people have used to include this as part of the application.
Is it simply a case of including a timer across each database call and logging the result, or is there a 'neater' way of doing it? Maybe there's a framework that does this for you already, or is there a flag I could enable in e.g. Linq-to-SQL that would provide similar functionality.
I mainly use c# but would also be interested in seeing methods from different languages, and I'd be more interested in a 'code' way of doing this over a db platform method like SQL Profiler.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
我确实看到了在应用程序运行时使用 SQL Profiler 的价值，并且 EXPLAIN 或 SET STATISTICS 将为您提供有关各个查询的信息，但是是否有人会定期将测量点放入其代码中以收集有关正在进行的数据库查询的信息 - 这会例如，对表的查询最初执行良好，但随着行数的增加，变得越来越慢。
如果您使用 MySQL 或 Postgre，有各种工具可以实时查看查询活动，但我还没有找到像 SQL Profiler 一样好的工具来衡量一段时间内的查询性能。
我想知道是否存在（或应该存在？）类似于 ELMAH 的内容它只是插入并为您提供信息而不需要太多额外的努力吗？
I definitely see the value in using SQL Profiler while you're app is running, and EXPLAIN or SET STATISTICS will give you information about individual queries, but does anyone routinely put measurement points into their code to gather information about database queries ongoing - that would pick up on for example, a query on a table that performs fine initially, but as the number of rows grows, becomes slower and slower.
If you're using MySQL or Postgre there's various tools for seeing query activity in real time, but I haven't found a tool as good as the SQL Profiler for measuring query performance over time.
I'm wondering if there is (or should be?) something similar to ELMAH in the way it just plugs in and gives you information without much additional effort?
如果您喜欢 Firebird，您可能想观看 sinatica.com。
我们很快就会为 Firebird DBA 推出实时监控工具。
< /无耻插件>
If you're into Firebird you may want to watch sinatica.com.
We'll soon launch a real-time monitoring tool for Firebird DBAs.
< /shameless plug>
如果您使用 Hibernate（我使用 Java 版本，我想 NHibernate 有类似的东西），您可以让 Hibernate 收集有关许多不同事物的统计信息。 例如，请参见：
http://www.javalobby.org/java/forums/t19807 .html
If you use Hibernate (I use the Java version, I'd imagine NHibernate has something similar), you can have Hibernate collect statistics about lots of different things. See, for example:
http://www.javalobby.org/java/forums/t19807.html
我本以为这里要问的重要问题是“您使用什么数据库平台？”
例如，在 Sybase 中，安装 MDA 表 可能会解决您的问题，它们提供了一大堆从过程调用使用情况到平均逻辑 I/O、CPU 时间和索引覆盖率的统计信息。 它可以像您想要的那样聪明。
I would have thought that the important thing to ask here is "what database platform are you using?"
For example, in Sybase, installing MDA tables might solve your problem, they provide a whole bunch of statistics from procedure call usage to average logical I/O, CPU time and index coverage. It can be as clever as you want it to be.
如果您在 SQL Management Studio 中编写查询，您可以输入：
SET STATISTICS TIME ON，SQl Server 将告诉您查询的各个部分解析、编译和执行所需的时间。您也许可以通过处理 SqlConnection 类的 InfoMessage 事件来记录此信息（但我认为使用 SQL Profiler 更容易。）
If you're writing queries in SQL Management Studio you can enter:
SET STATISTICS TIME ONand SQl Server will tell you how long the individual parts of a query took to parse, compile and execute.You might be able to log this information by handling the InfoMessage event of the SqlConnection class (but I think using the SQL Profiler is much easier.)
记录数据库调用、总时间和应用程序中返回的记录数（字节）很有用，但它不会为您提供所需的所有信息。
它可能会向您显示您意想不到的使用模式。 它可能会显示您在哪里使用“逐行”访问而不是“基于集合”操作。
最好使用的工具是 SQL Profiler，它可以分析“读取”次数与 CPU 和持续时间的关系。 您希望避免高 CPU 查询、高读取和长持续时间（废话！）。
“按读取分组”是一个有用的功能，可以将最讨厌的查询带到顶部。
Recording the database calls, the gross timing and the number of records (bytes) returned in the application is useful, but it's not going to give you all the information you need.
It might show you usage patterns you were not expecting. It might show where your using "row-by-row" access instead of "set based" operations.
The best tool to use is SQL Profiler and analyse the number of "Reads" vs the CPU and duration. You want to avoid high CPU queries, high Read's and long durations (duh!).
The "group by reads" is a useful feature to bring to the top the nastiest queries.
如果查询不仅仅是单个表上的简单 SELECT，如果我在 MySQL 或 PostgreSQL 上，我总是通过 EXPLAIN 运行它。 如果您使用的是 SQL Server，则 Management Studio 有一个显示估计执行计划，其本质上是相同的。 了解引擎如何访问每个表以及它将使用哪些索引非常有用。 有时它会让你感到惊讶。
If a query is more then just a simple SELECT on a single table I always run it through EXPLAIN if I am on MySQL or PostgreSQL. If you are using SQL Server then Management Studio has a Display Estimated Execution Plan which is essentially the same. It is useful to see how the engine will access each table and what indexes it will use. Sometimes it will surprise you.