SQL Server 作业（存储过程）跟踪
我需要您关于追踪问题的建议。
我们正在清晨运行数据加载作业，并将 Excel 文件中的数据加载到 SQL Server 2005 数据库中。当作业在生产服务器上运行时，很多时候需要2到3个小时才能完成任务。我们可以深入到一个作业步骤，该步骤需要花费总时间的 99% 才能完成。
虽然在暂存环境（恢复了相同的生产数据库）上运行作业步骤（存储过程）需要 9 到 10 分钟，但当它作为作业的一部分在清晨运行时，在生产服务器上运行作业步骤（存储过程）需要 9 到 10 分钟。生产服务器总是在特定的工作步骤上停滞不前。
我想在每个作业步骤上运行跟踪（在作业步骤内的 while 循环中为每个用户运行大约 10 个存储过程）并收集信息以找出问题。
SQL Server 2005 中有哪些方法可以实现同样的目的？我只想对这些 SP 运行跟踪，而不是在生产服务器上运行一段时间，因为跟踪提供了大量信息，并且对我（因为不是 DBA）来说分析这么多跟踪信息并找出答案变得非常困难的问题。所以我只想收集有关特定 SP 的信息。
让我知道你的建议。
感谢您的时间和帮助。
谢谢。
I need your suggestion on tracing the issue.
We are running data load jobs at early morning and loading the data from Excel file into SQL Server 2005 db. When job runs on production server, many times it takes 2 to 3 hours to complete the tasks. We could drill down to one job step which is taking 99% of the total time to finish.
While running the job step (stored procs) on staging environment (with the same production database restored) takes 9 to 10 minutes, the same takes hours on production server when it run at early morning as part of job. The production server always stuck up at the very job step.
I would like to run trace on the very job step (around 10 stored procs run for each user in while loop within the job step) and collect the info to figure out the issue.
What are the ways available in SQL Server 2005 to achieve the same? I want to run the trace only for these SPs and not for certain period time period on production server, as trace give lots of information and it becomes very difficult for me (as not being DBA) to analyze that much of trace information and figure out the issue. So I want to collect info about specific SPs only.
Let me know what you suggest.
Appreciate your time and help.
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
使用 SQL 探查器。它允许您跟踪大量事件，包括存储过程，甚至可以对跟踪应用过滤器。
Use SQL Profiler. It allows you to trace plenty of events, including stored procedures, and even apply filters to the trace.
当其他服务器上的速度更快但产品上的速度更快时，当时服务器上发生的其他事情很重要。也许您正在遇到日常备份或维护统计或索引的工作？
What else is happening on the server at that time is important when it is faster on other servers but not prod. Maybe you are running into the daily backup or maintenance of statistics or indexes jobs?