是否可以自动化 SQL Server 2008 分析器？
有一个帖子关于有用的 SQL 技巧。 在这里我要提到 SQL Server Profiler 工具，因为它帮助我减少了 SQL 的编写。 我会编写 SQL 来询问、理解或再次猜测数据库业务逻辑。
Profiler 非常有用，尤其是在应用程序代码嵌入了 SQL 并且您希望在尽可能短的时间内弄清楚它在做什么的情况下。 （此外，您可能不知道应用程序环境中使用的是哪个源代码版本，或者更糟糕的是，没有可用的源代码！）。
我想知道探查器是否有一个可以连接的 API？
当我们想要通过设置环境变量/标志（PROFILER_ON）快速检查系统内调用了什么 SQL 时，这将非常有用= TRUE，例如）。 然后系统可以启动 SQL 分析器，设置各种跟踪属性，然后写入日志或表 - 支持团队可以查看。
我想编写一个组件来打开探查器并监视生产环境（在安静的时候），因此无法真正更改代码库（应用程序代码和 SQL 存储过程）。
There was a post regarding useful SQL tricks. Here I was going to mention the SQL Server Profiler tool, as it has helped me write less SQL. I would write SQL that would interrogate, understand or second guess the databases business logic.
Profiler is very useful, especially where application code has embedded SQL and you want to work out what it's doing, in the shortest time possible. (Also you may not know which source code version is used in the application's enviroment, or even worse, where there is no source code available!).
I was wondering if the profiler has an API I could hook into?
This would be very useful when we want to quickly check what SQL is called, within the system, by setting an environment variable/flag (PROFILER_ON=TRUE, for example). Then the system can kick off SQL profiler, setting various trace properties then writing out to a log or table - which could be viewed by the support team.
I want to write a component to switch profiler on and monitor the production environment (at quiet times) so can't really alter the codebase (both app code and SQL stored procs).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以利用 Microsoft.SqlServer.Management.Trace 命名空间对象； 它们提供与 SQL Profiler 提供的相同功能的 API。 这是针对数据库编写自己的 sql 脚本的替代方法。 然而，就像 SQL Profiler 一样，这些对象在幕后所做的事情是执行 SQL Trace 存储过程（或其变体）来创建、运行和管理跟踪。
如果您使用 1 个探查器实例来探查另一个实例（或者使用探查器通过从跟踪中删除“不像‘SQL探查器...’”过滤条件来探查自身（我认为这是一个应用程序名称过滤器），那么您会看到探查器正在执行的操作与您在 SQL 中执行的操作或 SMO 对象将通过 API 执行的操作相同。
You can utilize the Microsoft.SqlServer.Management.Trace namespace objects; they provide an API against the same functionality that SQL Profiler provides. This is an alternative against scripting your own sql against the database. However, just like SQL Profiler, what these objects do under the covers is to execute the SQL Trace stored procs (or variations thereof) to create, run and manage the traces.
If you use 1 profiler instance to profile another (or use a profiler to profile itself by removing the "NOT LIKE 'SQL Profiler...'" filter criteria from the trace (I think it's an Application Name filter) then you'll see exactly what it is that profiler is doing is the same as what you would do in SQL or what the SMO objects will do from an API.
您可以做的就是设置“完美”跟踪并将其编写为 SQL 脚本。
然后执行它来运行跟踪并将输出保存到文件或表中。
现在，您可以包装 SQL 以在另一个存储过程中创建跟踪，该存储过程可以通过 SQL 代理/空闲阈值运行，但使用 fn_trace_getinfo 来查看是否已在运行。
您不能在SSMS中自动化它或使用环境变量：代码、作业、警报等都必须存在于服务器上。
您可以将脚本化跟踪放入批处理文件中并通过 osql/sqlcmd 运行它，但必须手动调用它。
没有探查器 API：它所做的只是运行存储过程，就像任何解决方案都必须做的那样。
为什么不能添加独立于应用程序代码的监控 SQL 代码？
您也无法使用 SMO ...仅读取跟踪
编辑：跟踪始终需要针对服务器运行 SQL
What you can do is set up your "perfect" trace and script it out as SQL.
Then execute it to run the trace and save output to a file or table.
Now, you could wrap the SQL to create the trace in another stored proc that can be run via SQL Agent/idle threshold, but use fn_trace_getinfo to see if one if running already.
You can not automate it in SSMS or use an environment variable: the code, the job, the alerts etc all must exist on the server.
You can put the scripted trace into a batch file and run it via osql/sqlcmd but it has to be invoked manually.
There is no profiler API: all it does is run stored procs, just like any solution will have to.
Why can't you add monitoring SQL code hat is independant of the app code?
You can't use SMO either... only to read traces
Edit: a trace always requires SQL to be run against the server
您可以使用系统存储过程来自动化分析器。 查看执行相同操作的详细信息 http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
You can use System stored procedure to automate profiler. Check out the details of doing the same at http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
GBN 就在这里； SQL Profiler 所做的就是在 SQL Server 上执行存储过程：这些过程启动、配置和停止跟踪。 这意味着您不必尝试自动化分析器，而只需执行相同的跟踪过程，并且您将获得相同的行为。
要自动启动和停止跟踪，可以使用 SQL Profiler 创建脚本； 该脚本可以从批处理文件执行。 所以你会做这样的事情；
.sql文件此
.sql文件包含您可以运行的代码； 如果你打开它，你会在顶部看到这样的代码；这是创建跟踪并将事件附加到跟踪。
现在，要开始跟踪，您可以安排该文件的执行。 批处理文件看起来像这样；
GBN's got it right here; All SQL Profiler does is execute stored procedures on your SQL Server: these procedures start, configure, and stop traces. What that means is that instead of trying to automate the profiler, you just execute the same tracing procedures, and you'll get the same behavior.
To start and stop traces automatically, you can use SQL Profiler to create a script; that script can be executed from a batch file. So you'd do something like this;
.sqlfileThis
.sqlfile contains code you can run; if you open it, you'll see this kind of code at the top;This is creating a trace and attaching events to the trace.
So now, to start your trace, you can schedule the execution of this file. The batch file will look something like;