使用过滤器减少 SQL 跟踪的开销
我们有一个 SQL 2000 服务器，它有各种各样的作业，这些作业在一天的不同时间甚至一个月的不同日期运行。 通常，我们只使用 SQL 探查器在很短的时间内运行跟踪以进行性能故障排除，但在这种情况下，这确实无法让我全面了解在数据库上运行的查询类型。一天、一周或一个月的过程。
如何最大限度地减少长时间运行的 SQL 跟踪的性能开销？ 我已经知道：
- 执行跟踪服务器端 (sp_create_trace)，而不是使用 SQL Profiler UI。
- 跟踪到文件，而不是数据库表（这会增加数据库服务器的额外开销）。
我的问题实际上是关于过滤器的。 如果我添加一个过滤器以仅记录运行超过一定持续时间或读取的查询，它仍然需要检查服务器上的所有活动来决定是否需要记录它，对吗？ 因此，即使使用该过滤器，跟踪是否也会为已经处于不可接受性能边缘的服务器带来不可接受的开销？
We have a SQL 2000 server that has widely varied jobs that run at different times of day, or even different days of the month. Normally, we only use the SQL profiler to run traces for very short periods of time for performance troubleshooting, but in this case, that really wouldn't give me a good overall picture of the kinds of queries that are run against the database over the course of a day or week or month.
How can I minimize the performance overhead of a long-running SQL trace? I already know to:
- Execute the trace server-side (sp_ create_trace), instead of using the SQL Profiler UI.
- Trace to a file, and not to a database table (which would add extra overhead to the DB server).
My question really is about filters. If I add a filter to only log queries that run more than a certain duration or reads, it still has to examine all activity on the server to decide if it needs to log it, right? So even with that filter, is the trace going to create an unacceptable level of overhead for a server that is already on the edge of unacceptable performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
添加过滤器确实可以最大限度地减少事件收集的开销，并且还可以防止服务器记录不需要的事务条目。
至于跟踪是否会产生不可接受的开销水平，您只需对其进行测试并在出现其他投诉时停止它。 不过，利用生产跟踪文件获取 DB Tuning Advisor 的提示可以提高每个人明天的性能。
Adding Filters does minimize the overhead of event collection and also prevents the server from logging transaction entries you don't need.
As for whether the trace is going to create an unacceptable level of overhead, you'll just have to test it out and stop it if there are additional complaints. Taking the hints of the DB Tuning Advisor with that production trace file could improve performance for everyone tomorrow though.
实际上，您不应该让服务器处理跟踪，因为这可能会导致问题：“当服务器处理跟踪时，不会删除任何事件 - 即使这意味着牺牲服务器性能来捕获所有事件。而如果 Profiler 正在处理跟踪，如果服务器太忙，它将跳过事件。” （来自 SQL 70-431 考试书籍最佳实践。）
You actually should not have the server process the trace as that can cause problems: "When the server processes the trace, no event are dropped - even if it means sacrificing server performace to capture all the events. Whereas if Profiler is processing the trace, it will skip events if the server gets too busy." (From SQL 70-431 exam book best practices.)
我发现一篇文章实际上测量了 SQL 探查器会话与服务器端跟踪的性能影响：
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
这确实是我的根本问题，如何确保在跟踪过程中我的生产服务器不会陷入困境。 看起来，如果你做得正确，开销就会很小。
I found an article that actually measures the performance impact of a SQL profiler session vs a server-side trace:
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
This really was my underlying question, how to make sure that I don't bog down my production server during a trace. It appears that if you do it correctly, there is minimal overhead.
实际上，您可以收集比从 Profiler 收集的更详细的测量结果，并在整个实例上 24x7 执行此操作，而不会产生任何开销。 这避免了提前弄清楚需要过滤的内容的必要性……这可能很棘手。
全面披露：我为提供此类工具的供应商之一工作……但无论您使用我们的还是其他人的……这可能会让您绕过这里的核心问题。
有关我们工具的更多信息，请点击此处 http://bit.ly/aZKerz
It’s actually possible to collect more detailed measurements than you can collect from Profiler – and do it 24x7 across an entire instance -- without incurring any overhead. This avoids the necessity of figuring out ahead of time what you need to filter… which can be tricky.
Full disclosure: I work for one of the vendors who provide such tools… but whether you use ours or someone else’s… this may get you around the core issue here.
More info on our tool here http://bit.ly/aZKerz