SQL 顺序或并行启动存储过程

Start stored procedures sequentially or in parallel

提问by Joel Coehoorn

We have a stored procedure that runs nightly that in turn kicks off a number of other procedures. Some of those procedures could logically be run in parallel with some of the others.

我们有一个每晚运行的存储过程，它反过来启动许多其他过程。其中一些程序在逻辑上可以与其他一些程序并行运行。

• How can I indicate to SQL Server whether a procedure should be run in parallel or serial — ie: kicked off of asynchronously or blocking?
• What would be the implications of running them in parallel, keeping in mind that I've already determined that the processes won't be competing for table access or locks- just total disk io and memory. For the most part they don't even use the same tables.
• Does it matter if some of those procedures are the sameprocedure, just with different parameters?
• If I start a pair or procedures asynchronously, is there a good system in SQL Server to then wait for both of them to finish, or do I need to have each of them set a flag somewhere and check and poll the flag periodically using WAITFOR DELAY?

• 我如何向 SQL Server 指示一个过程应该并行运行还是串行运行——即：异步启动还是阻塞运行？
• 并行运行它们会有什么影响，请记住，我已经确定这些进程不会竞争表访问或锁——只是总磁盘 io 和内存。大多数情况下，他们甚至不使用相同的表。
• 如果其中一些过程是相同的过程，只是具有不同的参数，这是否重要？
• 如果我异步启动一对或过程，SQL Server 中是否有一个好的系统来等待它们两个完成，或者我是否需要让它们每个都在某处设置一个标志并使用 定期检查和轮询标志WAITFOR DELAY？

At the moment we're still on SQL Server 2000.

目前我们仍在使用 SQL Server 2000。

As a side note, this matters because the main procedure is kicked off in response to the completion of a data dump into the server from a mainframe system. The mainframe dump takes all but about 2 hours each night, and we have no control over it. As a result, we're constantly trying to find ways to reduce processing times.

附带说明一下，这很重要，因为主程序是在完成从大型机系统向服务器转储数据后启动的。大型机转储每晚只需要大约 2 小时，我们无法控制它。因此，我们一直在努力寻找减少处理时间的方法。

回答by BradC

I had to research this recently, so found this old question that was begging for a more complete answer. Just to be totally explicit: TSQL does not(by itself) have the ability to launch other TSQL operations asynchronously.

我最近不得不研究这个，所以发现这个老问题正在乞求一个更完整的答案。只是为了完全明确：TSQL确实不（本身）具有异步启动其他TSQL操作的能力。

That doesn't mean you don't still have a lot of options (some of them mentioned in other answers):

这并不意味着您仍然没有很多选择（其中一些在其他答案中提到）：

• Custom application: Write a simple custom app in the language of your choice, using asynchronous methods. Call a SQL stored proc on each application thread.
• SQL Agent jobs: Create multiple SQL jobs, and start them asynchronously from your proc using sp_start_job. You can check to see if they have finished yet using the undocumented function xp_sqlagent_enum_jobsas described in this excellent articleby Gregory A. Larsen. (Or have the jobs themselves update your own JOB_PROGRESS table as Chris suggests.) You would literally have to create separate job for each parallel process you anticipate running, even if they are running the same stored proc with different parameters.
• OLE Automation: Use sp_oacreateand sp_oamethodto launch a new process calling the other stored proc as described in this article, also by Gregory A. Larsen.
• DTS Package: Create a DTS or SSIS package with a simple branching task flow. DTS will launch tasks in individual spids.
• Service Broker: If you are on SQL2005+, look into using Service Broker
• CLR Parallel Execution: Use the CLR commands Parallel_AddSqland Parallel_Executeas described in this articleby Alan Kaplan (SQL2005+ only).
• Scheduled Windows Tasks: Listed for completeness, but I'm not a fan of this option.

• 自定义应用程序：使用异步方法以您选择的语言编写一个简单的自定义应用程序。在每个应用程序线程上调用 SQL 存储过程。
• SQL 代理作业：创建多个 SQL 作业，并使用sp_start_job. 您可以检查他们是否已完成使用Gregory A. Larsen这篇优秀文章中xp_sqlagent_enum_jobs所述的未记录功能 。（或者让作业本身按照 Chris 的建议更新您自己的 JOB_PROGRESS 表。）您实际上必须为您预期运行的每个并行进程创建单独的作业，即使它们正在使用不同的参数运行相同的存储过程。
• OLE 自动化：使用sp_oacreate和sp_oamethod启动一个新进程调用另一个存储过程，如本文所述，也是 Gregory A. Larsen。
• DTS 包：使用简单的分支任务流创建 DTS 或 SSIS 包。DTS 将在单个 spid 中启动任务。
• Service Broker：如果您使用的是 SQL2005+，请考虑使用Service Broker
• CLR并行执行：使用CLR命令Parallel_AddSql和Parallel_Execute在如所描述的本文由Alan卡普兰（SQL2005 +只）。
• 计划的 Windows 任务：为了完整性而列出，但我不喜欢此选项。

I don't have much experience with Service Broker or CLR, so I can't comment on those options. If it were me, I'd probably use multiple Jobs in simpler scenarios, and a DTS/SSIS package in more complex scenarios.

我对 Service Broker 或 CLR 没有太多经验，因此我无法评论这些选项。如果是我，我可能会在更简单的场景中使用多个作业，在更复杂的场景中使用 DTS/SSIS 包。

One final comment: SQL already attempts to parallelize individual operations whenever it can*. This means that running 2 tasks at the same time instead of after each other is no guarantee that it will finish sooner. Test carefully to see whether it actually improves anything or not.

最后一条评论：SQL 已经尝试尽可能并行化单个操作*。这意味着同时运行 2 个任务而不是依次运行并不能保证它会更快完成。仔细测试，看看它是否真的改善了什么。

We had a developer that created a DTS package to run 8 tasks at the same time. Unfortunately, it was only a 4-CPU server :)

我们有一个开发人员创建了一个 DTS 包来同时运行 8 个任务。不幸的是，它只是一个 4-CPU 服务器 :)

*Assuming default settings. This can be modified by altering the server's Maximum Degree of Parallelism or Affinity Mask, or by using the MAXDOP query hint.

*假设默认设置。这可以通过更改服务器的最大并行度或关联掩码或使用 MAXDOP 查询提示来修改。

回答by NotMe

Create a couple of SQL Server agent jobs where each one runs a particular proc.

创建几个 SQL Server 代理作业，每个作业运行一个特定的过程。

Then from within your master proc kick off the jobs.

然后从你的主进程中开始工作。

The only way of waiting that I can think of is if you have a status table that each proc updates when it's finished.

我能想到的唯一等待方法是，如果您有一个状态表，每个 proc 在完成时都会更新。

Then yet another job could poll that table for total completion and kick off a final proc. Alternatively, you could have a trigger on this table.

然后另一个工作可以轮询该表以完全完成并启动最终过程。或者，您可以在此表上设置触发器。

The memory implications are completely up to your environment..

内存影响完全取决于您的环境。

UPDATE:If you have access to the task system.. then you could take the same approach. Just have windows execute multiple tasks, each responsible for one proc. Then use a trigger on the status table to kick off something when all of the tasks have completed.

更新：如果您可以访问任务系统.. 那么您可以采用相同的方法。只需让 Windows 执行多个任务，每个任务负责一个过程。然后使用状态表上的触发器在所有任务完成后启动某些操作。

UPDATE2:Also, if you're willing to create a new app, you could house all of the logic in a single exe...

UPDATE2：另外，如果你愿意创建一个新的应用程序，你可以将所有的逻辑放在一个单独的 exe 中......

回答by Bernhard Hofmann

You do need to move your overnight sprocs to jobs. SQL Server job control will let you do all of the scheduling you are asking for.

您确实需要将隔夜的 sproc 转移到工作中。SQL Server 作业控制将让您完成您要求的所有计划。

回答by Tom H

You might want to look into using DTS (which can be run from the SQL Agent as a job). It will allow you pretty fine control over which stored procedures need to wait for others to finish and what can run in parallel. You can also run the DTS package as an EXE from your own scheduling software if needed.

您可能想考虑使用 DTS（可以作为作业从 SQL 代理运行）。它将允许您很好地控制哪些存储过程需要等待其他人完成以及哪些可以并行运行。如果需要，您还可以从自己的调度软件中将 DTS 包作为 EXE 运行。

NOTE:You will need to create multiple copies of your connection objects to allow calls to run in parallel. Two calls using the same connection object will still block each other even if you don't explicitly put in a dependency.

注意：您需要创建连接对象的多个副本，以允许并行运行调用。即使您没有明确地放入依赖项，使用相同连接对象的两个调用仍然会相互阻塞。