顺序或并行启动存储过程 [英] Start stored procedures sequentially or in parallel

问题描述

我们有一个每晚运行的存储过程，该存储过程又启动了许多其他过程.从逻辑上讲，其中一些过程可以与其他一些过程并行运行.

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.

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

• 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 same procedure, 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 2000.

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

请注意，这很重要，因为响应从大型机系统到服务器的数据转储完成，开始了主过程.大型机转储每个晚上大约需要2个小时，而我们对此无能为力.因此，我们一直在努力寻找减少处理时间的方法.

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.

推荐答案

我最近必须对此进行研究，因此找到了这个古老的问题，希望获得更完整的答案.完全明确地说: TSQL本身不是 .具有异步启动其他TSQL操作的功能.

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.

这并不意味着您仍然没有很多选择(在其他答案中提到了其中一些):

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

• 自定义应用程序:使用异步方法，以您选择的语言编写一个简单的自定义应用程序.在每个应用程序线程上调用一个SQL存储的proc.
• SQL代理作业:创建多个SQL作业，然后使用sp_start_job从您的proc中异步启动它们.您可以按照这篇文章，作者:艾伦·卡普兰(Alan Kaplan)(仅适用于SQL2005 +).
• 计划的Windows任务:出于完整性考虑而列出，但我不喜欢此选项.

• 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_jobs as described in this excellent article by 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_oacreate and sp_oamethod to 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_AddSql and Parallel_Execute as described in this article by Alan Kaplan (SQL2005+ only).
• Scheduled Windows Tasks: Listed for completeness, but I'm not a fan of this option.

我在Service Broker或CLR方面经验不足，因此无法评论这些选项.如果是我，那么我可能会在较简单的场景中使用多个Job，而在较复杂的场景中使用DTS/SSIS软件包.

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.

最后一条评论:SQL已经尽可能尝试并行化各个操作*.这意味着不能同时运行两个任务，而不能同时运行两个任务，这不能保证它会更快地完成.仔细测试以查看它是否真的可以改善任何东西.

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.

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

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 :)

*假定为默认设置.可以通过更改服务器的最大并行度"或相似性掩码"，或使用MAXDOP查询提示来修改.

*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.

这篇关于顺序或并行启动存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！