March 16, 2015 at 10:02 am
Hi,
I'm trying to achieve a parallel queue execution but our DBAs refuses to activate Service Broker.
It seems to worked fine on SQL 2008R2, but when testing it out on SQL 2014, it seems to run only 1 process at a time even though osql with -Q should start the query then exits immediately, but it seems to wait for the query to finish now. Luckily, with 2014 columnstore index, the query takes 1 minute instead of 10, but I would still like to run them in parallel.
The goal is to launch a stored procedure and go to the next without awaiting the result.
Any tip on why it runs 1 at a time or any idea better than my current script would be appreciated.
CREATE PROCEDURE [Etl].[ParallelQueue_Process]
@BatchID INT
,@nbMaxProcessQueue INT = 20
,@ConnectionString VARCHAR(255)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ServerNameNVARCHAR(100)
DECLARE @DatabaseName NVARCHAR(100)
-- Server Name
DECLARE @Server_CS NVARCHAR(MAX)
SET @Server_CS = SUBSTRING(@ConnectionString,1,CHARINDEX(';',@ConnectionString)-1)
SELECT @ServerName = SUBSTRING(@Server_CS, CHARINDEX('=',@Server_CS)+1,LEN(@Server_CS))
-- Database Name
DECLARE @DB_CS NVARCHAR(MAX)
SET @DB_CS = SUBSTRING(@ConnectionString,CHARINDEX(';',@ConnectionString)+1, (CHARINDEX(';',@ConnectionString, CHARINDEX(';',@ConnectionString)+1) - 1) - CHARINDEX(';',@ConnectionString))
SELECT @DatabaseName = SUBSTRING(@DB_CS, CHARINDEX('=',@DB_CS)+1,LEN(@DB_CS))
DECLARE @nbRunningProcessINT
DECLARE @nbProcessINT
DECLARE @nbCompletedProcess INT
DECLARE @nbError INT
DECLARE @i INT
SET @nbProcess = ( SELECT COUNT(*) FROM [Etl].[ParallelExecutionQueue] WHERE BatchID = @BatchID)
SET @i = @nbProcess
SET @nbCompletedProcess = 0
SET @nbRunningProcess = 0
SET @nbError = 0
-- Loop until all processes are executed or an error occured
WHILE ((@i > 0 OR @nbProcess <> @nbCompletedProcess) AND @nbError = 0)
BEGIN
-- Check if queue limit is attained
IF @nbRunningProcess < @nbMaxProcessQueue AND @i > 0
BEGIN
DECLARE @sql_Proc VARCHAR(MAX)
DECLARE @rc INT
-- Get Query to be executed
SET @sql_Proc = ( SELECT StoredProcCall FROM [Etl].[ParallelExecutionQueue] WHERE ParallelExecutionKey = @i AND BatchID = @BatchID)
--EXEC [Etl].[ParallelQueue_ExecAsyncStoredProcedure] @ServerName, @DatabaseName, @sql_Proc
DECLARE @osql_cmd varchar(1000)
SET @osql_cmd = 'osql -E -d' + @DatabaseName + ' -S' + @ServerName + ' -Q"' + @sql_proc + '"'
EXEC xp_cmdshell @osql_cmd
SET @i = @i - 1
END
SET @nbRunningProcess = ( SELECT COUNT(*) FROM [Etl].[ParallelExecutionQueue] WHERE IsRunning = 1 AND BatchID = @BatchID)
SET @nbCompletedProcess = ( SELECT COUNT(*) FROM [Etl].[ParallelExecutionQueue] WHERE IsCompleted = 1 AND BatchID = @BatchID)
SET @nbError = ( SELECT COUNT(*) FROM [Etl].[ParallelExecutionQueue] WHERE IsError = 1 AND BatchID = @BatchID)
END
IF @nbError > 0
BEGIN
DECLARE @errorcode INT
DECLARE @ErrorMessage VARCHAR(MAX)
SELECT TOP 1 @errorcode = ErrorCode, @ErrorMessage = ErrorMessage
FROM Etl.ParallelExecutionQueue
WHERE BatchID = @BatchID
AND IsError = 1
UPDATE Etl.ParallelQueueBatch
SET BatchStatus = 'ERROR', Endtime = GETDATE(), ErrorCode = @errorcode, ErrorMessage = @ErrorMessage
WHERE BatchId = @BatchID
RAISERROR (@ErrorMessage, 15, 1)
END
ELSE
BEGIN
UPDATE Etl.ParallelQueueBatch
SET BatchStatus = 'OK', EndTime = GETDATE()
WHERE BatchID = @BatchID
END
END
Thanks
March 16, 2015 at 1:22 pm
If anyone is interested, I found out how to make then asynchronous.
Replace
SET @osql_cmd = 'osql -E -d' + @DatabaseName + ' -S' + @ServerName + ' -Q"' + @sql_proc + '"'
EXEC xp_cmdshell @osql_cmd
par le code ci-dessous
declare @rc int
declare @object int
declare @src varchar(255)
declare @desc varchar(255)
declare @osql_cmd varchar(1000)
-- create shell object
exec @rc = sp_oacreate 'wscript.shell', @object out
if @rc <> 0
begin
exec sp_oageterrorinfo @object, @src out, @desc out
select hr=convert(varbinary(4),@rc),
source=@src,
description=@desc
return
end
SET @osql_cmd = 'osql -E -d' + @DatabaseName + ' -S' + @ServerName + ' -Q"' + @sql_proc + '"'
exec @rc=sp_oamethod @object,
'run',
null,
@osql_cmd
print @rc
if @rc <> 0
begin
exec sp_oageterrorinfo @object, @src out, @desc out
select hr=convert(varbinary(4),@rc),
source=@src,
description=@desc
return
end
-- destroy shell object
exec sp_oadestroy @object
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply