SQL Server代理作业运行缓慢 [英] SQL Server Agent Job Running Slow

问题描述

我正在使用SQL Server 2005中的SQL Server代理作业执行存储过程.

I am executing a stored procedure using SQL Server Agent Job in SQL Server 2005.

这项工作一直进行到昨天.从昨天开始，这项工作耗时超过1小时，而不是2分钟.

This job was running fast until yesterday. Since yesterday this job is taking more than 1 hour instead of 2 mins.

我在SSMS中执行了存储过程，只花了不到1分钟的时间.

I executed the stored procedure in SSMS, it just took less than 1 minute to execute.

我不知道为什么将它作为SQL Server代理作业执行需要花费超过1个小时的时间?

I could not figure out why it is taking more than 1 hour when executed as a SQL Server Agent job?

推荐答案

经过一段时间的注释，并假设SP在SSMS中执行时，其在输入参数和数据相同的情况下表现良好，我最后认为我可以给出最后一个提示:

After some time commenting and assuming that the SP performs with the same input parameters and data well when executed in SSMS, I finnaly think I can give a last tip:

根据在SP中执行的操作(例如，在循环或游标中插入/更新/删除大量数据)，您应该在代码开头将nocount设置为on.

Depending on what actions are performed within the SP (e.g. inserting/updating/deleting a lot of data within a loop or cursor), you should set nocount on at the beginning of your code.

set nocount on

如果不是这种情况或没有帮助，请添加注释中已经提到的更多信息(例如Job的所有设置和每个Jobstep，已记录的内容，Jobhistory中的内容，检查SQLerrorlogs，eventlogs ，..). 还可以查看"SQL Server日志"，也许您可​​以在此处收集一些信息.同样，查看数据库服务器的应用程序/系统事件始终是一个好主意. 要获得基本概述，可以在SSMS中使用Activitymonitor，方法是选择Databaseserver，然后从contextmenu菜单中选择"Activity monitor"，然后搜索sql代理.

If this is not the case or does not help, please add more information, already mentioned in the comments (e.g. all settings of the Job and each Jobstep, what has been logged, what is in the Jobhistory, check SQLerrorlogs, eventlogs,....). Also take a look at the "SQL Server Logs" maybe you can gather some info here. Also a look into the Application/System eventlo of the Databaseserver is always a good idea. To get a basic overview you can use the Activitymonitor in SSMS, by selecting the Databaseserver and selecting "Activity monitor" from contextmenu and search for the sql agent.

我最后的尝试是尝试为代理运行sql跟踪.在这种情况下，您将开始跟踪和过滤，例如由运行SQLAgent服务的用户决定.您可以为跟踪设置很多选项，因此我建议使用google搜索，在MSDN上搜索或在此处对stackoverflow提出另一个问题.

My last try would be to try to run a sql trace for the agent. In this case you would start a trace and filter e.g. by the user that the SQLAgent Service runs. There are so many options you can set for traces, so I would recommend to google for it, search on MSDN or ask another question here on stackoverflow.

这篇关于SQL Server代理作业运行缓慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！