获取 SQL Server 2000 上的存储过程使用数据
从 SQL Server 2000 中获取特定数据库上的存储过程使用数据的最佳方法是什么？
我需要的数据是：
- X 时间内所有存储过程调用的总计
- X 时间内每个特定存储过程调用的总计。
- X 时间内处理所有存储过程所花费的总时间。
- X 时间内处理特定存储过程所花费的总时间。
我的第一个预感是使用一组过滤器来设置 SQL Profiler 来收集这些数据。 我不喜欢这个解决方案的是，数据必须写入某个文件或表中，并且我必须进行数字运算才能找出我需要的结果。 我还希望在应用更改以了解更改如何影响数据库的过程中获得这些结果。
我无法直接访问服务器来运行 SQL Profiler，因此我需要创建跟踪模板文件并将其提交给我的 DBA，让他们运行 X 次，然后将结果返回给我。
有没有更好的解决方案来获取我需要的数据？ 如果可能的话，我希望获得更多数据，但上述数据足以满足我当前的需求，我没有太多时间花在这上面。
编辑：也许有一些推荐的工具可以处理配置文件创建的跟踪文件，为我提供我想要的统计信息？
What is the best way to get stored procedure useage data on a specific database out of SQL Server 2000?
The data I need is:
- Total of all stored procedure calls over X time
- Total of each specific stored procedure call over X time.
- Total time spent processing all stored procedures over X time.
- Total time spent processing specific stored procedures over X time.
My first hunch was to setup SQL Profiler wiht a bunch of filters to gather this data. What I don't like about this solution is that the data will have to be written to a file or table somewhere and I will have to do the number crunching to figure out the results I need. I would also like get these results ober the course of many days as I apply changes to see how the changes are impacting the database.
I do not have direct access to the server to run SQL Profiler so I would need to create the trace template file and submit it to my DBA and have them run it over X time and get back to me with the results.
Are there any better solutions to get the data I need? I would like to get even more data if possible but the above data is sufficient for my current needs and I don't have a lot of time to spend on this.
Edit: Maybe there are some recommended tools out there that can work on the trace file that profile creates to give me the stats I want?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我看到两个选项：
重新编写脚本并重新编译存储过程以调用日志记录存储过程。 该存储过程将被所有想要进行性能跟踪的存储过程调用。 将其写入包含存储过程名称、当前日期时间以及您想要的任何其他内容的表中。
专业版：轻松可逆，因为您在脚本中拥有存储过程的副本，可以轻松撤消。 方便查询！
缺点：您尝试测量的存储过程的每次运行都会影响性能。
使用将在每个存储过程调用开始和结束时写入日志文本文件的代码重新编译数据访问层。 您是否从单个类继承 DAL，您可以在一个位置插入此日志记录代码？ 优点：没有数据库混乱，当您想要停止此性能测量时，您可以切换程序集。 甚至可以在 app.config 中通过开/关进行调整。 缺点：磁盘 I/O。
使用
Two options I see:
Re-script and recompile your sprocs to call a logging sproc. That sproc would be called by all your sprocs that want to have perf tracking. Write it to a table with the sproc name, current datetime, and anything else you'd like.
Pro: easily reversible, as you'd have a copy of your sprocs in a script that you could easily back out. Easily queryable!
Con: performance hit on each run of the sprocs that you are trying to gauge.
Recompile your data access layer with code that will write to a log text file at the start and end of each sproc call. Are you inheriting your DAL from a single class where you can insert this logging code in one place? Pro: No DB messiness, and you can switch in and out over an assembly when you want to stop this perf measurement. Could even be tweaked with on/off in app.config. Con: disk I/O.
也许在 SQL Profiler 之外创建 SQL Server 跟踪可能会有所帮助。
http://support.microsoft.com/kb/283790
此解决方案涉及创建一个文本文件以及您所有的追踪选项。 输出被放入文本文件中。 也许可以修改它以转储到日志表中。
监视跟踪：http://support.microsoft.com/kb/283786/EN -美国/
Perhaps creating a SQL Server Trace outside of SQL Profiler might help.
http://support.microsoft.com/kb/283790
This solution involves creating a text file with all your tracing options. The output is put into a text file. Perhaps it could be modified to dump into a log table.
Monitoring the traces: http://support.microsoft.com/kb/283786/EN-US/