SQL Server (TSQL) - 是否可以并行执行 EXEC 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4571823/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server (TSQL) - Is it possible to EXEC statements in parallel?
提问by Outside the Box Developer
SQL Server 2008 R2
SQL Server 2008 R2
Here is a simplified example:
这是一个简化的示例：
EXECUTE sp_executesql N'PRINT ''1st '' + convert(varchar, getdate(), 126) WAITFOR DELAY ''000:00:10'''
EXECUTE sp_executesql N'PRINT ''2nd '' + convert(varchar, getdate(), 126)'
The first statement will print the date and delay 10 seconds before proceeding. The second statement should print immediately.
第一个语句将打印日期并在继续之前延迟 10 秒。第二个语句应立即打印。
The way T-SQL works, the 2nd statement won't be evaluated until the first completes. If I copy and paste it to a new query window, it will execute immediately.
T-SQL 的工作方式是，在第一个语句完成之前不会评估第二个语句。如果我将其复制并粘贴到新的查询窗口，它将立即执行。
The issue is that I have other, more complex things going on, with variables that need to be passed to both procedures.
问题是我还有其他更复杂的事情要做，需要将变量传递给两个过程。
What I am trying to do is:
我想做的是：
- Get a record
- Lock it for a period of time
- while it is locked, execute some other statements against this record and the table itself
- 获取记录
- 锁定一段时间
- 当它被锁定时，对该记录和表本身执行一些其他语句
Perhaps there is a way to dynamically create a couple of jobs?
也许有一种方法可以动态创建几个工作？
Anyway, I am looking for a simple way to do this without having to manually PRINT statements and copy/paste to another session.
无论如何，我正在寻找一种简单的方法来执行此操作，而无需手动打印语句并复制/粘贴到另一个会话。
Is there a way to EXEC without wait / in parallel?
有没有一种无需等待/并行执行的方法？
回答by Remus Rusanu
Yes, there is a way, see Asynchronous procedure execution.
是的，有一种方法，请参阅异步过程执行。
However, chances are this is not what you need. T-SQL is a data accesslanguage, and when you take into consideration transactions, locking and commit/rollback semantics is almost impossibleto have a parallel job. Parallel T-SQL works for instance with requests queues, where each requests is independent and there is no correlation between jobs.
但是，这很可能不是您所需要的。T-SQL 是一种数据访问语言，当你考虑事务时，锁定和提交/回滚语义几乎不可能有并行作业。例如，并行 T-SQL 与请求队列一起工作，其中每个请求都是独立的，作业之间没有相关性。
What you describe doesn't sound at all like something that can, nor should, actually be paralellized.
你所描述的听起来根本不像可以也不应该被并行化的东西。
回答by DVK
If you want to lock a record so you can execute statements against it, you may want to execute those statements as a transaction.
如果要锁定记录以便可以针对它执行语句，则可能希望将这些语句作为事务执行。
To execute SQL in parallel, you need to paralellize SQL calls, by executing your SQL from within separate threads/processes in Java, C++, perl, or any other programming language (hell, launching "isql" in shell script in background will work)
要并行执行 SQL，您需要并行化 SQL 调用，方法是在 Java、C++、perl 或任何其他编程语言中的单独线程/进程中执行 SQL（见鬼，在后台在 shell 脚本中启动“isql”会起作用）
回答by robotj
If after reading all above about potential problems and you still want to run things in parallel, you probably can try sql jobs, put your queries in different jobs, then execute by calling the jobs like this
如果在阅读了以上关于潜在问题的所有内容后，您仍然希望并行运行，您可能可以尝试 sql 作业，将您的查询放在不同的作业中，然后通过调用这样的作业来执行
EXEC msdb..sp_start_job 'Job1'
EXEC msdb..sp_start_job 'Job2'
回答by TJ Galama
It might be worth to check out the article Asynchronous T-SQL Execution Without Service Broker.
回答by crokusek
SQL Agent Jobs can run in parallel and be created directly from TSQL. The answer by Remus Rusanucontains a link that mentions this along with some disadvantages.
SQL 代理作业可以并行运行并直接从 TSQL 创建。Remus Rusanu的答案包含一个链接，其中提到了这一点以及一些缺点。
Another disadvantage is that additional security permissions are required to create the job. Also, for the implementation below, the job must run as a specific user+login with additional job management privileges.
另一个缺点是创建作业需要额外的安全权限。此外，对于下面的实现，作业必须以具有额外作业管理权限的特定用户+登录名运行。
It is possible to run the arbitrary SQL as a different (safer) user however I believe it requires sysadmin privilege to designate the job as such.
可以以不同的（更安全的）用户身份运行任意 SQL，但是我认为它需要 sysadmin 权限才能指定作业。
The returned @pJobIdHexOut can be used to stop the job if needed.
如果需要，返回的@pJobIdHexOut 可用于停止作业。
create function Common.ufn_JobIdFromHex(
@pJobIdBinary binary(16)
)
returns varchar(100) as
/*---------------------------------------------------------------------------------------------------------------------
Purpose: Convert the binary represenation of the job_id into the job_id string that can be used in queries
against msdb.dbo.sysjobs.
http://stackoverflow.com/questions/68677/how-can-i-print-a-binary-value-as-hex-in-tsql
http://stackoverflow.com/questions/3604603
MsgBoards
Modified By Description
---------- -------------- ---------------------------------------------------------------------------------------
2014.08.22 crokusek Initial version, http://stackoverflow.com/questions/3604603 and MsgBoards.
---------------------------------------------------------------------------------------------------------------------*/
begin
-- Convert from binary and strip off the '0x'.
--
declare
@jobIdHex varchar(100) = replace(convert(varchar(300), @pJobIdBinary, 1), '0x', '');
-- The endianness appears to be backwards and there are dashes needed.
--
return
substring(@jobIdHex,7,2) +
substring(@jobIdHex,5,2) +
substring(@jobIdHex,3,2) +
substring(@jobIdHex,1,2) +
'-' +
substring(@jobIdHex,11,2) +
substring(@jobIdHex,9,2) +
'-' +
substring(@jobIdHex,15,2) +
substring(@jobIdHex,13,2) +
'-' +
substring(@jobIdHex,17,4) +
'-' +
substring(@jobIdHex,21,12);
end
go
create proc [Common].[usp_CreateExecuteOneTimeBackgroundJob]
@pJobNameKey varchar(100), -- Caller should ensure uniqueness to avoid a violation
@pJobDescription varchar(1000),
@pSql nvarchar(max),
@pJobIdHexOut varchar(100) = null out, -- JobId as Hex string. For SqlServer 2014 binary(16) = varchar(64)
@pDebug bit = 0 -- True to include print messages
--
with execute as 'TSqlBackgroundJobOwner' -- requires special permissions (See below)
as
/*---------------------------------------------------------------------------------------------------------------------
Purpose: Create a one time background job and launch it immediately. The job is owned by the "execute as" UserName
Caller must ensure the @pSql argument is safe.
Required Permissions for "execute as" user:
-- User must be created with associated login (w/ deny connect).
use [msdb];
create user [$UserName$] for login [$LoginName$];
alter role [SQLAgentUserRole] add member [$UserName$];
alter role [SQLAgentReaderRole] add member [$UserName$];
alter role [SQLAgentOperatorRole] add member [$UserName$];
grant select on dbo.sysjobs to [$UserName$];
grant select on dbo.sysjobactivity to [$UserName$];',
use [Master];
create user [$UserName$] for login [$LoginName$];
grant execute on xp_sqlagent_is_starting to [$UserName$];
grant execute on xp_sqlagent_notify to [$UserName$];';
Modified By Description
---------- ----------- ------------------------------------------------------------------------------------------
2014.08.22 crokusek Initial version
2015.12.22 crokusek Use the SP caller as the job owner (removed the explicit setting of the job owner).
---------------------------------------------------------------------------------------------------------------------*/
begin try
declare
@usp varchar(100) = object_name(@@procid),
@currentDatabase nvarchar(100) = db_name(),
@jobId binary(16),
@jobOwnerLogin nvarchar(100);
set xact_abort on; -- ensure transaction is aborted on non-catchables like client timeout, etc.
begin transaction
exec msdb.dbo.sp_add_job
@job_name=@pJobNameKey,
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@delete_level=3,
@description=@pJobDescription,
@category_name=N'Database Maintenance',
-- If not overridden then the the current login is the job owner
--@owner_login_name=@jobOwnerLogin, -- Requires sysadmin to set this so avoiding.
@job_id = @jobId output;
-- Get the job_id string of the jobId (special format)
--
set @pJobIdHexOut = Common.ufn_JobIdFromHex(@jobId);
if (@pDebug = 1)
begin
print 'JobId: ' + @pJobIdHexOut;
print 'Sql: ' + @pSql;
end
exec msdb.dbo.sp_add_jobserver @job_id=@jobId; -- default is local server
exec msdb.dbo.sp_add_jobstep
@job_id=@jobId,
@step_name=N'One-Time Job Step 1',
@step_id=1,
@command=@pSql,
@database_name=@currentDatabase,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0,
@subsystem=N'TSQL',
@flags=0
;
declare
@startResult int;
exec @startResult = msdb.dbo.sp_start_job
@job_id = @jobId;
-- End the transaction
--
if (@startResult != 0)
raiserror('Unable to start the job', 16, 1); -- causes rollback in catch block
else
commit; -- Success
end try
begin catch
declare
@CatchingUsp varchar(100) = object_name(@@procid);
if (xact_state() = -1)
rollback;
--exec Common.usp_Log
-- @pMethod = @CatchingUsp;
--exec Common.usp_RethrowError
-- @pCatchingMethod = @CatchingUsp;
end catch
go
回答by Irawan Soetomo
You can create an SSIS that has 2 tasks that run in parallel. Then make an unscheduled agent job to call this SSIS. You can finally execute this unscheduled agent job using sp_start_job.
您可以创建一个具有 2 个并行运行的任务的 SSIS。然后做一个计划外的代理工作来调用这个 SSIS。您最终可以使用 sp_start_job 执行这个计划外的代理作业。
