{"id":2516,"date":"2015-05-27T05:18:11","date_gmt":"2015-05-27T05:18:11","guid":{"rendered":"http:\/\/bicortex.com\/?p=2516"},"modified":"2015-07-02T02:28:43","modified_gmt":"2015-07-02T02:28:43","slug":"asynchronous-sql-execution-via-sql-server-agent-jobs","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/asynchronous-sql-execution-via-sql-server-agent-jobs\/","title":{"rendered":"Asynchronous SQL Execution via SQL Server Agent Jobs"},"content":{"rendered":"

Recently, one of the clients tasked me with creating a data acquisition routine for an externally hosted MySQL database data, a part of a larger ETL solution to merge dispersed data sources into a staging area for further data warehouse processing. The database size was very small i.e. just over a few gigabytes, distributed across around 35 tables with the largest object holding over 15 million records. Microsoft SQL Server 2014 instance served as the local data storage engine and all the code required to be Microsoft dialect of SQL i.e. Transact-SQL, with data flow and execution governed by Integration Services packages (SSIS). The interface to querying MySQL database data was through a linked server set up on the SQL Server instance using Oracle’s ODBC driver, over 100Mbps internet link. The requirements seemed quite easy to accommodate i.e. create a SSIS package to manage data acquisition pipelines, either through a collection of SSIS-specific transformations or T-SQL code. Also, a few preliminary tests showed that the connection speed should be robust enough to copy or even merge the data across source\/target without much hassle.<\/p>\n

Once the development commenced, I quickly learned that Integration Services may not have been the best tool for the job (more due to the ODBC driver stability rather then SSIS functionality). Regardless of how much reconfiguration and tweaking went into optimising SSIS package to handle moving the data across the linked server connection, the package could not cope with a few of the large tables, continually failing on execution with cryptic messages and confusing error logs. Likewise, running pure SQL MERGE statements often resulted in connection time-outs or buffer overflows, indicating that the ODBC driver was too flaky and unstable to handle long running queries. The only solution that seemed not to throw the connection out of balance was to abandon more convenient ‘upserts’ for truncations and inserts, broken down into multiple statements to dissect the data flow across the network. The less data needed to go through the network as a single transaction the more reliable the transfer was, with one caveat \u2013 the largest MySQL database table (15 million records) was taking nearly one hour to read from and insert into the local SQL Server instance. Regardless of how many batches the data acquisition job was comprised of, the performance oscillated around 55 minutes mark \u2013 an unacceptable result by both my and the client’s standards, even if proven to solve the reliability issues. As data acquisition failures occurrence increased with the source tables records count (when executed as a single transaction) and breaking it up into multiple batches seemed to provide the needed stability, albeit at the expense of prolonged execution, the one potential solution and a silver bullet to this conundrum was to find a half-point between the two approaches. That compromise turned out to be batch processing (stability) combined with asynchronous, concurrent execution (speed).<\/p>\n

Microsoft SQL Server does not offer built-in asynchronous SQL processing \u2013 all statements part of a query execute in a sequential, non-parallel fashion. Even when looping through a process using a WHILE statement or a cursor, each SQL statement gets invoked in a synchronous manner. There are different approaches which can help achieving concurrent SQL execution, however, these require either a programming knowledge or a fair amount of inelegant hacking to circumvent this limitation. One simple solution that I found worked really well for my project was to create a number of SQL Server Agent jobs (using T-SQL only), which would get initialised in parallel to dissect the query thus ‘multi-threading’ the whole process and shaving a considerable amount of time off the final execution duration.<\/p>\n

Let’s look at an example of two tables placed in two different databases simulating this event \u2013 first one containing ten million records of mock data and the second one containing no data which will become the target of the parallel INSERT operation. The following code creates two sample databases with their corresponding objects and populates the source table with dummy data.<\/p>\n

\r\n--CREATE 'Source' AND 'Target' DATABASES\r\nUSE master;\r\nGO\r\nIF DB_ID('SourceDB') IS NOT NULL\r\n    BEGIN\r\n        ALTER DATABASE SourceDB SET SINGLE_USER\r\n        WITH ROLLBACK IMMEDIATE;\r\n        DROP DATABASE SourceDB;\r\n    END;\r\nGO\r\nCREATE DATABASE SourceDB;\r\nGO\r\nALTER DATABASE SourceDB SET RECOVERY SIMPLE;\r\nGO\r\nALTER DATABASE SourceDB\r\nMODIFY FILE\r\n    (NAME = SourceDB,\r\n    SIZE = 10240MB);\r\nGO\r\n\r\nUSE master;\r\nGO\r\nIF DB_ID('TargetDB') IS NOT NULL\r\n    BEGIN\r\n        ALTER DATABASE TargetDB SET SINGLE_USER\r\n        WITH ROLLBACK IMMEDIATE;\r\n        DROP DATABASE TargetDB;\r\n    END;\r\nGO\r\nCREATE DATABASE TargetDB;\r\nGO\r\nALTER DATABASE TargetDB SET RECOVERY SIMPLE;\r\nGO\r\nALTER DATABASE TargetDB\r\nMODIFY FILE\r\n    (NAME = TargetDB,\r\n    SIZE = 10240MB);\r\nGO\r\n\r\n--CREATE 'Source' AND 'Target' TABLES\r\nUSE SourceDB;\r\nCREATE TABLE dbo.SourceTable\r\n    (\r\n      ID INT IDENTITY(1, 1) NOT NULL ,\r\n      Value1 INT NOT NULL ,\r\n      Value2 INT NOT NULL ,\r\n      Value3 DECIMAL(10, 2) NOT NULL ,\r\n      Value4 DATETIME NOT NULL ,\r\n      Value5 DATETIME NOT NULL ,\r\n\t  Value6 NVARCHAR (512) NOT NULL,\r\n\t  Value7 NVARCHAR (512) NOT NULL,\r\n\t  Value8 UNIQUEIDENTIFIER,\r\n      CONSTRAINT PK_ID PRIMARY KEY CLUSTERED ( ID ASC )\r\n    );\r\n\r\nUSE TargetDB;\r\nCREATE TABLE dbo.TargetTable\r\n    (\r\n      ID INT IDENTITY(1, 1) NOT NULL ,\r\n      Value1 INT NOT NULL ,\r\n      Value2 INT NOT NULL ,\r\n      Value3 DECIMAL(10, 2) NOT NULL ,\r\n      Value4 DATETIME NOT NULL ,\r\n      Value5 DATETIME NOT NULL ,\r\n\t  Value6 NVARCHAR (512) NOT NULL,\r\n\t  Value7 NVARCHAR (512) NOT NULL,\r\n\t  Value8 UNIQUEIDENTIFIER,\r\n      CONSTRAINT PK_ID PRIMARY KEY CLUSTERED ( ID ASC )\r\n    );\r\n\r\n--POPULATE 'SourceTable' WITH TEST DATA\r\nUSE SourceDB;\r\nSET NOCOUNT ON;\r\nDECLARE @count INT = 0;\r\nDECLARE @records_to_insert INT = 10000000;\r\nWHILE @count < @records_to_insert\r\n    BEGIN\r\n        INSERT  INTO dbo.SourceTable\r\n                ( Value1 ,\r\n                  Value2 ,\r\n                  Value3 ,\r\n                  Value4 ,\r\n                  Value5 ,\r\n\t\t\t\t  Value6 ,\r\n\t\t\t\t  Value7 ,\r\n\t\t\t\t  Value8\r\n                )\r\n                SELECT  10 * RAND() ,\r\n                        20 * RAND() ,\r\n                        10000 * RAND() \/ 100 ,\r\n                        DATEADD(ss, @count, SYSDATETIME()) ,\r\n                        CURRENT_TIMESTAMP ,\r\n\t\t\t\t\t\tREPLICATE(CAST(NEWID() AS NVARCHAR(MAX)),10) ,\r\n\t\t\t\t\t\tREPLICATE(CAST(RAND() AS NVARCHAR(MAX)),10) ,\r\n\t\t\t\t\t\tNEWID();\r\n        SET @count = @count + 1;\r\n    END;\r\n<\/pre>\n
Next let’s test out sequential INSERT performance, inserting all records from SourceDB database and SourceTable table into TargetDB database and TargetTable table. For this purpose I simply run INSERT statement, coping all the data across with no alterations or changes in the target object generating the following execution plan.<\/p>\n
\r\nSET STATISTICS TIME ON;\r\nGO\r\nINSERT  INTO TargetDB.dbo.TargetTable\r\n        ( Value1 ,\r\n          Value2 ,\r\n          Value3 ,\r\n          Value4 ,\r\n          Value5 ,\r\n\t\t  Value6 ,\r\n\t\t  Value7 ,\r\n\t\t  Value8\r\n        )\r\n        SELECT  Value1 ,\r\n                Value2 ,\r\n                Value3 ,\r\n                Value4 ,\r\n                Value5 ,\r\n\t\t\t\tValue6 ,\r\n\t\t\t\tValue7 ,\r\n\t\t\t\tValue8\r\n        FROM    SourceDB.dbo.SourceTable;\r\nGO\r\nSET STATISTICS TIME OFF;\r\nGO\r\n<\/pre>\n
\"Async_SQL_Exec_INSERT_Statement\"<\/a><\/p>\n
Given that SQL Server 2014 edition offers some enhancements to SELECT…INTO execution, I think it’s worthwhile to also try it out as an alternative. If there is a provision to create the target table from scratch and the developer has the liberty to execute SELECT…INTO in place of an INSERT statement, Microsoft claims that this improvement will allow it to run in a parallel mode in SQL Server 2014 (as confirmed by the execution plan below), speeding up the execution considerably.<\/p>\n
\r\nIF OBJECT_ID('TargetDB.dbo.TargetTable', 'U') IS NOT NULL\r\n  BEGIN\r\n\tDROP TABLE TargetDB.dbo.TargetTable\r\n  END\r\n\r\nSET STATISTICS TIME ON;\r\nGO\r\nSELECT\t\t\tValue1 ,\r\n                Value2 ,\r\n                Value3 ,\r\n                Value4 ,\r\n                Value5 ,\r\n\t\t\t\tValue6 ,\r\n\t\t\t\tValue7 ,\r\n\t\t\t\tValue8\r\n\t\tINTO\tTargetDB.dbo.TargetTable\r\n        FROM    SourceDB.dbo.SourceTable;\r\nGO\r\nSET STATISTICS TIME OFF;\r\nGO\r\n<\/pre>\n
\"Async_SQL_Exec_SELECT_INTO_Statement\"<\/a><\/p>\n
Finally, let look at the solution to enforce parallel queries execution, in this case also INSERT statement by means of ‘spinning up’ multiple SQL Server Agent jobs. The following code creates a stored procedure which ‘dissects’ all source table records into multiple, record-count comparable batches (the exact number controlled by the parameter value passed) and generates SQL Server Agent jobs executing in parallel.<\/p>\n
\r\nUSE [SourceDB]\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\nCREATE PROCEDURE [dbo].[usp_asyncTableInsert]\r\n(@Source_DB_Name\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Source_DB_Schema_Name\t\tVARCHAR\t\t\t\t(128) ,\r\n@Source_DB_Object_Name\t\tVARCHAR\t\t\t\t(256) ,\r\n@Target_DB_Name\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Schema_Name\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Object_Name\t\tVARCHAR\t\t\t\t(256) ,\r\n@SQL_Exec_No\t\t\t\tVARCHAR\t\t\t\t(10))\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON;\r\n\r\n\t\/*\r\n\tDeclare additional variables:\r\n\t(1) @SQL\t\t\t\t- stores dynamic SQL string\r\n\t(2) @Error_Massage\t\t- stores error message string\r\n\t(3) @Is_Debug\t\t\t- stores a binary flag for allowing\/disallowing displaying messages during procedure execution\r\n\t(4) @Check_Count\t\t- stores the starting number for the cursor execution loop\r\n\t(5) @Max_Check_Count\t- stores the maximum number for the cursor execution loop\r\n\t*\/\r\n    DECLARE @SQL\t\t\t\tNVARCHAR(MAX)\r\n\tDECLARE @Error_Message\t\tVARCHAR (4000)\r\n\tDECLARE @Is_Debug\t\t\tINT = 1\t\t\t\r\n\r\n\t\/*\r\n\tCreate temporary table to hold the ranges of values for dissecting\r\n\tthe 'source' table into semi-equal chunks of data for further processing\r\n\t*\/\r\n    IF OBJECT_ID('tempdb..#Ids_Range') IS NOT NULL\r\n\t\tBEGIN\r\n\t\t\tDROP TABLE #Ids_Range;\r\n        END;\r\n    CREATE TABLE #Ids_Range\r\n    (\r\n    id SMALLINT IDENTITY(1, 1) ,\r\n    range_FROM BIGINT ,\r\n    range_TO BIGINT\r\n    );\t\t\t\t\r\n\r\n\t\/*\r\n\tCreate and populate temporary variables and #Ids_Range temporary table with value ranges\r\n\tThis table contains value ranges i.e. pairs of values which will be used to build the WHERE\r\n\tclause of the INSERT statment, breaking down all the 'source' records into smaller chunks\r\n\t*\/\r\n\tSET @SQL =\t\t\t'DECLARE @R1 INT = (SELECT MIN(ID) AS ID FROM '\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t''+@Source_DB_Name+'.'+@Source_DB_Schema_Name+'.'+@Source_DB_Object_Name+')'\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'DECLARE @R2 BIGINT = (SELECT (MAX(ID)-MIN(ID)+1)\/'+@SQL_Exec_No+' AS ID FROM'\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t''+@Source_DB_Name+'.'+@Source_DB_Schema_Name+'.'+@Source_DB_Object_Name+')'\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'DECLARE @R3 BIGINT = (SELECT MAX(ID) AS ID FROM '\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t''+@Source_DB_Name+'.'+@Source_DB_Schema_Name+'.'+@Source_DB_Object_Name+')'\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'DECLARE @t int = @r2+@r2+2 '\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'INSERT INTO #Ids_Range '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'(range_FROM, range_to) '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'SELECT @R1, @R2 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'UNION ALL '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'SELECT @R2+1, @R2+@R2+1 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'WHILE @t<=@r3 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'BEGIN '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'INSERT INTO #Ids_Range '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'(range_FROM, range_to) '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'SELECT @t, CASE WHEN (@t+@r2)>=@r3 THEN @r3 ELSE @t+@r2 END '\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'SET @t = @t+@r2+1 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'END'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\r\n\tEXEC(@SQL)\r\n\r\n\t\/*\r\n\tTruncate Target table if any data exists\r\n\t*\/\r\n\tSET @SQL =\t\t\t'IF EXISTS (SELECT TOP 1 1 FROM '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t''+@Target_DB_Name+'.'+@Target_DB_Schema_Name+'.'+@Target_DB_Object_Name+''\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'WHERE ID IS NOT NULL) BEGIN '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t'TRUNCATE TABLE '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\tSET @SQL = @SQL +\t''+@Target_DB_Name+'.'+@Target_DB_Schema_Name+'.'+@Target_DB_Object_Name+' END'\t\t\t+CHAR(13)\r\n\r\n\tEXEC(@SQL)\r\n\r\n\t\/*\r\n\tCreate temporary #Temp_Tbl_AgentJob_Stats table to store SQL Server Agent job\r\n\t*\/\r\n\tIF OBJECT_ID('tempdb..#Temp_Tbl_AgentJob_Stats') IS NOT NULL\r\n    BEGIN\r\n\t\tDROP TABLE #Temp_Tbl_AgentJob_Stats;\r\n    END;\r\n    CREATE TABLE #Temp_Tbl_AgentJob_Stats\r\n    (\r\n    ID SMALLINT IDENTITY(1, 1) ,\r\n    Job_Name VARCHAR(256) ,\r\n    Job_Exec_Start_Date DATETIME\r\n    );\t\t\r\n\r\n\t\/*\r\n\tCreate a cursor and a range of variables to define SQL execution string\r\n\tto create and manage SQL Server Agent jobs (exact count defined by @SQL_Exec_No\r\n\tvaraiable). This SQL splits the INSERT statment into multiple, concurrently-executing\r\n\tbatches which run in asunchronous mode. This part of code also manages stopping\r\n\tand deleting already created jobs in case a re-run is required as well as deleting\r\n\tSQL Agent jobs created as part of this process on successful completion\r\n\t*\/\r\n\tIF CURSOR_STATUS('global', 'sp_cursor') >= -1\r\n\t\tBEGIN\r\n\t\t\tDEALLOCATE sp_cursor\r\n\t\tEND\r\n\t\tDECLARE @z INT\r\n\t\tDECLARE @err INT\r\n\t\tDECLARE sp_cursor CURSOR\r\n\t\tFOR\r\n\t\tSELECT id FROM #ids_range\r\n\t\tSELECT  @err = @@error\r\n        IF @err <> 0\r\n\t\t\tBEGIN\r\n\t\t\t\tDEALLOCATE sp_cursor\r\n\t\t\t\tRETURN @err\r\n            END\r\n\t\tOPEN sp_cursor\r\n\t\tFETCH NEXT\r\n\t\tFROM sp_cursor INTO @z\r\n\t\tWHILE @@FETCH_STATUS = 0\r\n\t\t\tBEGIN\r\n\t\t\t\tDECLARE\r\n\t\t\t\t@range_from\t\tVARCHAR(10)\t\t= (SELECT CAST(range_FROM AS VARCHAR(10)) FROM #ids_range where id = @z),\r\n\t\t\t\t@range_to\t\tVARCHAR(10)\t\t= (SELECT CAST(range_TO AS VARCHAR(10)) FROM #ids_range where id = @z),\r\n\t\t\t\t@job_name\t\tVARCHAR (256)\t= 'Temp_'+UPPER(LEFT(@Target_DB_Object_Name,1))+LOWER(SUBSTRING(@Target_DB_Object_Name,2,LEN(@Target_DB_Object_Name)))+'_TableSync_'+'AsyncJob'+'_'+CAST(@z AS VARCHAR (20)),\r\n\t\t\t\t@job_owner\t\tVARCHAR (256)\t= 'sa'\r\n\t\t\t\tDECLARE\r\n\t\t\t\t@sql_job_delete VARCHAR (400)\t= 'EXEC msdb..sp_delete_job @job_name='''''+@job_name+''''''\r\n\t\t\t\tDECLARE\r\n\t\t\t\t@sql_job\t\tNVARCHAR(MAX)\t=\r\n\t\t\t\t'INSERT  INTO TargetDB.dbo.TargetTable\r\n\t\t\t\t(Value1, Value2, Value3, Value4, Value5, Value6, Value7,Value8)\r\n\t\t\t\tSELECT Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8\r\n\t\t\t\tFROM SourceDB.dbo.SourceTable\r\n\t\t\t\tWHERE id >= '+cast(@range_FROM as varchar (20))+' AND ID <= '+cast(@range_to as varchar(20))+''\r\n\r\n\t\t\t\tSET @SQL =\t\t\t'IF EXISTS'\r\n\t\t\t\tSET @SQL = @SQL +\t'(SELECT TOP 1 1 FROM msdb..sysjobs_view job JOIN msdb.dbo.sysjobactivity activity'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'ON job.job_id = activity.job_id WHERE job.name = N'''+@job_name+''''\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'AND activity.start_execution_date IS NOT NULL AND activity.stop_execution_date IS NULL)'\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'BEGIN'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_stop_job @job_name=N'''+@job_name+''';'\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_delete_job @job_name=N'''+@job_name+''', @delete_unused_schedule=1'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'END'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'IF EXISTS'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'(SELECT TOP 1 1 FROM msdb..sysjobs_view job JOIN msdb.dbo.sysjobactivity activity'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'ON job.job_id = activity.job_id WHERE job.name = N'''+@job_name+''''\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'AND activity.start_execution_date IS NULL AND activity.stop_execution_date IS NOT NULL)'\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'BEGIN'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_delete_job @job_name=N'''+@job_name+''', @delete_unused_schedule=1'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'END'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_job '''+@job_name+''', @owner_login_name= '''+@job_owner+''';'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_jobserver @job_name= '''+@job_name+''';'\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_jobstep @job_name='''+@job_name+''', @step_name= ''Step1'', '\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'@command = '''+@sql_job+''', @database_name = '''+@Target_DB_Name+''', @on_success_action = 3;'\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_jobstep @job_name = '''+@job_name+''', @step_name= ''Step2'','\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +   '@command = '''+@sql_job_delete+''''\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_start_job @job_name= '''+@job_name+''''\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+REPLICATE(CHAR(13),4)\r\n\t\t\t\tEXEC (@SQL)\r\n\r\n\t\t\t\t\/*\r\n\t\t\t\tWait for the job to register in the SQL Server metadata sys views \r\n\t\t\t\t*\/\r\n\t\t\t\tWAITFOR DELAY '00:00:01'\t\t\t\t\t\r\n\r\n\t\t\t\tINSERT INTO #Temp_Tbl_AgentJob_Stats\r\n\t\t\t\t(Job_Name, Job_Exec_Start_Date)\r\n\t\t\t\tSELECT job.Name, activity.start_execution_date\r\n\t\t\t\tFROM msdb.dbo.sysjobs_view job\r\n\t\t\t\tINNER JOIN msdb.dbo.sysjobactivity activity\r\n\t\t\t\tON job.job_id = activity.job_id\r\n\t\t\t\tWHERE job.name = @job_name    \r\n\r\n                FETCH NEXT\r\n\t\t\t\tFROM sp_cursor INTO @z\r\n\t\t\tEND\r\n\t\t\tCLOSE sp_cursor\r\n\t\t\tDEALLOCATE sp_cursor\r\nEND\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n<\/pre>\n
Running the stored procedure (with the prior target table truncation) will generate as many INSERT statements as specified by the value of @SQL_Exec_No parameter passed. When executed, we can observe multiple instances of the SQL statement inserting records falling between certain ID ranges, here shown as an output from Adam Mechanic’s sp_whoisactive stored procedure<\/a> as well as SQL Server Agent jobs view. Given that the value of @SQL_Exec_No parameter was 5, there were five instances of this statement running in parallel as per image below.<\/p>\n
\"Async_SQL_Exec_StoredProc_Agent_Parallel_View\"<\/a><\/p>\n
Looking at the execution times (run on my trusty Lenovo x240 laptop) and the differences between individual approaches, it is evident that the enhancements made in SQL Server 2014 edition regarding parallelisation of SELECT…INTO statement are profound, however, given the optimizer taking advantage of multiple cores I also noticed a large spike in CPU utilization i.e. 20% for INSERT and 90% for SELECT…INTO. If, on the other hand, SELECT…INTO is not an option and a substantial amount of data is to be copied as part of a long-running transaction, asynchronous execution via SQL Server Agent jobs seems like a good solution.<\/p>\n
\"Async_SQL_Exec_Speed_Comparison\"<\/a><\/p>\n
I have only scratched the surface of how SQL Server Agent jobs can facilitate workload distribution across the server to speed up data processing and take advantage of SQL Server available resource pool. There are other ways to achieve the same result but given the simplicity and the ease of development\/provisioning I think that taking advantage of this technique can provide a quick and robust workaround to parallel SQL statements execution with SQL Server.<\/p>\n","protected":false},"excerpt":{"rendered":"
Recently, one of the clients tasked me with creating a data acquisition routine for an externally hosted MySQL database data, a part of a larger ETL solution to merge dispersed data sources into a staging area for further data warehouse processing. The database size was very small i.e. just over a few gigabytes, distributed across […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,46],"tags":[49,19],"class_list":["post-2516","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-sql","tag-sql-server"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=2516"}],"version-history":[{"count":29,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2516\/revisions"}],"predecessor-version":[{"id":2556,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2516\/revisions\/2556"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=2516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=2516"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=2516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}