在 SQL Server Express 版本中创建作业? [英] Create jobs in SQL Server Express edition?

问题描述

我想在 sql server express edition 中安排一些作业.挖掘了一下之后，我得到了一些帖子:

I want to schedule some jobs in sql server express edition. After digging a bit i got few post like:

如何运行SQL Server Express Edition 中每天一个存储过程?

如何在 SQL Server Express 版中创建作业

如何运行SQL Server Express Edition 中每天一个存储过程?

而且我知道需要 Sql Agent 来安排 sql server 中的作业，但由于 SQL Server express 没有附带 SQL Agent，所以我必须使用其他一些替代方法.

and i understand that Sql Agent is require to schedule a job in sql server but since SQL Server express does not come with SQL Agent so i have to go with some other alternative.

我得到了 this，这表明了良好且简单的替代方案.在@Thomas Bratt 的回答中，他使用了无限循环 和 等待 (Transact-SQL)

And i got this, which suggest good and easy alternative. Here in @Thomas Bratt's answer he used infinite loop and waitfor (Transact-SQL)

来自@Thomas Bratt 答案的代码

我想知道这段代码对性能的影响(如果有的话).

I want to know the performance impact (if any) of this code.

...
while 1 = 1
begin
    waitfor time @timeToRun
    begin
        execute [MyDatabaseName].[dbo].[MyDatabaseStoredProcedure];
    end
end 
...

和同一帖子 @Raj More 建议另一种替代方案，即 Windows 调度程序，因此使用 Windows 调度程序 或 waitfor (Transact-SQL) 无限循环 ??

and in the same post @Raj More suggest another alternative i.e Windows Scheduler, so which one is better to use Windows Scheduler or waitfor (Transact-SQL) with infinite loop ??

推荐答案

是的，sp_procoption"是为了自动执行程序.但在我看来，这些过程会有一些逻辑来对所有数据库执行一次性设置更新和/或配置更改.如果您的 SQL 作业也适用于类似的任务，那么您应该考虑使用此选项，并记住这些 proc 不能有任何参数.同样从 while 循环的性能影响来看，您将始终将其视为 SQL 服务器的后台运行作业，但大部分时间都在睡眠/等待时间发生.实际的性能影响取决于它运行时正在执行的任务，而不是等待时.

yes, "sp_procoption" is meant for the the automatic execution of procedure. but in my opinion those procedures would have some logic to perform one time settings updates and/or configuration changes on all databases. if your SQL jobs are also meant for similar taks then you should consider this option with limitation in mind that these procs can not have any parameters. Also from the performance impact of the while loop, you will always see this as a background running job for your SQL server but most of the time sleeping/waiting to time occur. actual performance hit is depends upon what tasks it is doing when it runs and not when it is waiting.

底线:如果我计划或期待在不久的将来在此 SQ 作业中进行更多改进，我不会将sp_procoption"功能用作作业调度程序而使其过载.相反，我会考虑其他用户建议的更灵活的选项.

bottom line: if I am planing to or expecting more enhancements in this SQ jobs in near future I wouldn't overload the "sp_procoption" feature by using it as a job scheduler. instead i would consider more flexible options as other users have suggested.

这篇关于在 SQL Server Express 版本中创建作业?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！