SQL Server 探查器
我听说 SQL Profiler 在运行时会对 MSDB 进行更改。这是真的吗？如果是的话，它会带来什么变化？
更多信息
我问的原因是，我们有一位 DBA，他希望我们在实时服务器上运行探查器时确定更改请求的范围。她的论点是，它对数据库进行了更改，而这些更改应该受到更改控制。
I have been told that SQL Profiler makes changes to the MSDB when it is run. Is this true and if so what changes does it make?
MORE INFO
The reason I ask is that we have a DBA who wants us to range a change request when we run the profiler on a live server. Her argument is that it makes changes to the DB's which should be change controlled.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
启动跟踪会在
msdb.sys.traces，停止跟踪会删除该行。但是，msdb.sys.traces 是内部表值函数的视图，不受任何物理存储的支持。为了证明这一点，请将msdb设置为read_only，启动跟踪，观察msdb.sys.traces中的新行，停止跟踪，记住将msdb转回read_write。由于当msdb为只读时，可以在探查器事件中启动跟踪，因此很明显，通常不会发生对 msdb 的写入。现在，在您向 dba 微笑之前，她实际上是对的。探查器跟踪可能会对实时系统造成巨大压力，因为跟踪事件必须阻塞，直到它们可以生成跟踪记录。实时、繁忙的系统可能会遇到
SQLTRACE_BUFFER_FLUSH、SQLTRACE_LOCK、TRACEWRITE等类型的资源阻塞。实时跟踪（探查器）通常更差，文件跟踪（sp_trace_create）更好，但仍然可能导致问题。因此，开始新的跟踪绝对应该是 DBa 应该了解并仔细考虑的事情。Starting a trace adds a row into
msdb.sys.traces, stopping the trace removes the row. Howevermsdb.sys.tracesis a view over an internal table valued function and is not backed by any physical storage. To prove this, setmsdbtoread_only, start a trace, observer the new row inmsdb.sys.traces, stop the trace, remember to turnmsdbbackread_write. Since a trace can be started in the Profiler event whenmsdbis read only it is clear that normally there is no write into msdb that can occur.Now before you go and grin to your dba, she is actually right. Profiler traces can pose a significant stress on a live system because the traced events must block until they can generate the trace record. Live, busy, systems may experience blocking on resources of type
SQLTRACE_BUFFER_FLUSH,SQLTRACE_LOCK,TRACEWRITEand other. Live traces (profiler) are usualy worse, file traces (sp_trace_create) are better, but still can cause issues. So starting new traces should definetly something that the DBa should be informed about and very carefully considered.我所知道的唯一的情况是当您安排跟踪来收集定期信息时发生的情况 - 添加了一项作业。
The only ones I know happen when you schedule a trace to gather periodic information - a job is added.
据我所知，情况并非如此（除了其他人指出的微小变化之外）。
您指的是哪些变化？
That's not the case as far as I'm aware (other than the trivial change noted by others).
What changes are you referring to?
我读过、听到过或看到过的任何内容都没有表明 SQL Profiler 或其所做或使用的任何内容对 MSDB 数据库有任何影响。 （本质上，SQL Profiler 是一个围绕跟踪例程的 GUI。）当然可以配置特定的设置/实现来执行任何操作，也许这就是某人正在考虑的事情。
这听起来像是一种“都市传说”。我建议你挑战它——让那些声称它是真的的人提供证据。
Nothing I have ever read, heard, or seen says that SQL Profiler or anything it does or uses has any impact on the MSDB database. (SQL Profiler is, essentially, a GUI wrapped around the trace routines.) It is of course possible to configure a specific setup/implementation to do, well, anything, and perhaps that's what someone is thinking of.
This sounds like a kind of "urban legend". I recommend that you challenge it -- get the people who claim it to be true to provide proof.