Parallel Execution of multiple queries in SQL Server

Parallel Execution with Logging =>

Sometimes it is necessary for a process to execute several queries in parallel. There are few ways to do it. Lets look at them.

1. Using parallel tasks in SSIS package. That is most appropriate way to execute parallel queries. You can have these queries hard coded, you will have full execution logging and error handling. That method requires you to have SSIS knowledge and Visual Studio installed. It is most appropriate for processes, which are already using SSIS and parallelism will be done naturally;

2. Creation of an extended DLL function to run queries on the server. That method requires some programming skills in VB, C#, C++ or other available languages to write DLL. That might be too complex for an individual to develop error handling and can produce some unexpected problems during application migration;

3. Using SQL Agent. That is the easiest, but most unsecured way. At first, it requires SQL Agent to be available and running. At second, it uses dynamic SQL for it's executions, which might be pretty dangerous.
However, because it is the easiest way, I'll demonstrate how you can use it.

Here is a stored procedure, which will make a trick:

DROP proc IF EXISTS SP_Parallel_Execution;

GO

CREATE PROC sp_Parallel_Execution

@SQLCommand NVARCHAR(4000),

@QueueLen INT = 100

AS

SET NOCOUNT ON

DECLARE @DBName sysname = DB_NAME();

DECLARE @SQL NVARCHAR(4000);

/* Check for current queue */

IF @QueueLen < ( 

       SELECT COUNT(a.queued_date)

       FROM msdb.dbo.sysjobs as j WITH (NOLOCK)

       INNER JOIN msdb.dbo.sysjobactivity as a WITH (NOLOCK)

       ON j.job_id = a.job_id

       WHERE j.name like 'Parallel_Execution_Command_%'

              and a.queued_date is NOT null

)

BEGIN

  RAISERROR('Can''t execute a query. SQL Agent queue length is bigger than current threshold %d.',16,1, @QueueLen);

  RETURN -1;

END

/* Generate new job */

DECLARE @CommandName sysname = 'Parallel_Execution_Command_'

       + REPLACE(CAST(NEWID() as NVARCHAR(36)),'-','_')

       + '_' + CONVERT(NVARCHAR(150), HASHBYTES ('SHA2_512', @SQLCommand), 1);

EXEC msdb.dbo.sp_add_job @job_name = @CommandName;

/* Generate first job's step with code execution */

SET @SQL = 'DECLARE @SQL NVARCHAR(4000);' + CHAR(10)

       + 'SET @SQL = ''' + REPLACE(@SQLCommand,'''','''''') + ''';' + CHAR(10)

       + 'EXEC sp_executesql @SQL;'

EXEC msdb.dbo.sp_add_jobstep @job_name = @CommandName, @step_name = N'Parallel_Execution_Command_1', @subsystem = N'TSQL', 

       @database_name = @DBName, @command = @SQL, @on_success_action = 3, @on_fail_action = 3;

/* Generate second job's step with deleting the job */

SET @SQL = 'EXEC msdb.dbo.sp_delete_job  @job_name = ''' + @CommandName + ''';';

EXEC msdb.dbo.sp_add_jobstep @job_name = @CommandName, @step_name = N'Parallel_Execution_Command_2', @subsystem = N'TSQL', 

       @database_name = @DBName, @command = @SQL;

/* Adding job to the server and executing it */

EXEC msdb.dbo.sp_add_jobserver @job_name = @CommandName;

EXEC msdb.dbo.sp_start_job @job_name = @CommandName;

RETURN 0;

GO

Now we can test it. Run following code to see how it works:

CREATE TABLE ##Test_Parallel_Execution(F VARCHAR(20));

GO

DECLARE @SQL NVARCHAR(4000);

DECLARE @i INT = 30;

DECLARE @DelaySeconds INT = 30;

WHILE @i > 0

BEGIN

       SET @SQL = '

       DECLARE @i INT = ' + CAST(@DelaySeconds as VARCHAR) + '

       WHILE @i > 0

       BEGIN

         INSERT INTO ##Test_Parallel_Execution(F)

         SELECT ''A' + CAST(@i as VARCHAR) + '-'' + CAST(@i as VARCHAR);

         WAITFOR DELAY ''00:00:01'';

         SET @i -= 1;

       END'

  EXEC sp_Parallel_Execution @SQL;

  SET @i -= 1;

END

SELECT * FROM ##Test_Parallel_Execution;

If you monitor content of "##Test_Parallel_Execution" table you'll see that for 30 seconds it will grow by 30 records each second. Just do not forget to drop temporary table after the test.
If you take a look at list of your SQL Agent jobs in SSMS during the test you might see something like this:

After all jobs are executed they are supposed to disappear from the list.

Here is an explanation how "sp_Parallel_Execution" procedure works:
1. To run any query for the parallel execution you just pass it as a parameter to the procedure;
2. To run too many parallel queries via SQL Agent can be too dangerous. If you create a million jobs for execution it will blow SQL Server "MSDB" database even if your queries will do nothing. To avoid that I've set "@QueueLen" parameter, which is set to 100 by default. If happens that SQL Agent queue riches 100 jobs, the next requested execution will be errored. You can adjust that parameter for your environment.
3. After checking for the queue, stored procedure generates new job name. It contains wording "Parallel_Execution_Command_" plus hash value of your query in addition to uniqueidentifier, which is supposed to make your new execution job totally unique.
4. As the first step of new job will be added your query. It can be any dynamic query against current database or a local stored procedure.
5. The second step of a job is a deletion of the job. That is made to prevent accumulation of executed jobs in SQL Agent.
6. At the end, procedure executes the newly created job and finishes.
7. The job is supposed to delete itself right after executed submitted query without any check if execution was successful.

Disclosure:

1. The stored procedure is provided "as is" and at your own risk. Do not run it in production before the comprehensive testing in Dev environment.
2. That method is very insecure. Whoever has rights to run that procedure can make a damage to your whole SQL Server.
3. Because SQL Agent job is self-deleting, you won't see any execution results of it. That means you have to do your own error handling. The most preferred way to wrap all executed queries inside of hard coded stored procedures with their own error handling. Later on I'll come up with more comprehensive version of that procedure to provide error handling and execution check.

Parallel Execution with Logging =>

SSIS Script Task:Error: The Script Task is corrupted.

There are probably a lot of SSIS corruption errors, but that is one that is very easy to solve.

The whole error message is texted like this:

Script Task:Error: The Script Task is corrupted.

Script Task:Error: There were errors during task validation.

Script Task:Error: There was an exception while loading Script Task from XML: System.Exception: The Script Task "ST_74aca886806a416fa34ae89cac6237c2" uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services. at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

That error came from an execution of SSIS package and it points that particular Script task is corrupted.

It is very confusing, because if you try to run the same package in the Visual Studio everything will be fine:

Just to be short: The error is caused not by corruption during deployment and not by corruption of SSIS DLL library, but just by different version of the compiled code.

Here is how to reproduce that error:
1. Create a package with a Script Task

2. Keep it simple - use C# as a language and click "Edit Script":

3. Enter only one line of a code "int TestVariable = 0;" into the Main section:

4. Then build a solution and deploy it on the SSIS server.
5. On SSIS server execute newly deployed solution:

6. When SSMS asks you want to see a report click "Yes"

7. As the result you will see "Status: Failed". If you click "View Messages" you'll see error details:

Here is how to Fix that problem:
1. In the menu chose Project's properties:

2. In the "Configuration Properties" section you will see TargetServerVersion. In my case it is 2017, while my SQL Server is only at version 2016.

3. The fix is simple: Just specify version of your SQL Server, where you want to deploy that package. Save, re-build/re-compile and re-deploy.

As the result, execution status of your SSIS package has to be "Succeeded":

Hope that post saves you a little time.

Giving special rights for group or user

SQL Server provides great flexibility for different types of security solutions.
In this post I want to show one of them.

Problem description:
1. Need to create a group/user "User1", which has to have only CRUD (Create-Read-Update-Delete) permissions for data in schema called "Schema1".
2. Need to create a group/user "User2", which has to have similar permissions as "User1" and have to be able create Views/Procedures/Functions in schema called "Schema2".
3. The group/user "User1" has to have Select/Execute permissions for all newly created objects in "Schema2".

Solution: Create a special database role for group/user "User2".

Here is how it can be done:

Preparation:

/* Create Test database */
use master;
GO
CREATE DATABASE TestPermissionDB;
GO
ALTER DATABASE [TestPermissionDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT;
GO
USE TestPermissionDB;
GO

/* Create Test Schemas */
CREATE SCHEMA Schema1;
GO 
CREATE SCHEMA Schema2;
GO 
/* Create Test Users and give them CRUD permissions*/
CREATE USER User1 WITH PASSWORD=N'39+pjFkM6+9ll131C9RBWIYL4KcaFSHIqYwK16+B9ec=';
GO
CREATE USER User2 WITH PASSWORD=N'sw/Efa4VCM6bUrz5s+jl8zuRas5r6U8IP8eiUO83NTk=';
GO
ALTER ROLE [db_datareader] ADD MEMBER User1;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User1;
GO
ALTER ROLE [db_datareader] ADD MEMBER User2;
GO
ALTER ROLE [db_datawriter] ADD MEMBER User2;
GO
/* Create Test table */
SELECT 'ABC' as Sample_Data
INTO Schema1.tbl_Sample_Table;
GO

Give special permissions for User1:

/* Grant Permissions for User1 */
GRANT EXECUTE TO User1;
GO

Create special Role:

/* Create special test role and give it specific permissions */
CREATE ROLE SpecialRole;
GO
GRANT CREATE PROCEDURE to SpecialRole;
GO
GRANT CREATE VIEW to SpecialRole;
GO
GRANT CREATE FUNCTION to SpecialRole;
GO

And here is the magic.

/* Associate special test role with Schema2 and assign User2 to that role */
ALTER AUTHORIZATION ON SCHEMA::Schema2 TO SpecialRole;
GO
ALTER ROLE SpecialRole ADD MEMBER User2;
GO

From this point User2 has all permissions it needs.

Test User2 Permissions:

/* test Permissions for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE FUNCTION Schema2.fn_Test() RETURNS CHAR(3) as 
BEGIN
RETURN (SELECT TOP 1 Sample_Data FROM Schema1.tbl_Sample_Table)
END;
GO  
CREATE VIEW Schema2.vw_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE Schema2.sp_Test @VAL CHAR(3) as 
SELECT * FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = @VAL;
GO  
REVERT ;  
GO 

Everything should execute successfully.

Now will test restrictions for User2:

/* test Permission failures for User2*/
EXECUTE AS USER = 'User2' ;  
GO  
CREATE VIEW Schema1.vw_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
CREATE PROCEDURE dbo.sp_Failed_Test as 
SELECT * FROM Schema1.tbl_Sample_Table;
GO  
REVERT ;   

It should return following errors:
Msg 2760, Level 16, State 1, Procedure vw_Failed_Test, Line 1 [Batch Start Line 92]
The specified schema name "Schema1" either does not exist or you do not have permission to use it.
Msg 2760, Level 16, State 1, Procedure sp_Failed_Test, Line 1 [Batch Start Line 95]
The specified schema name "dbo" either does not exist or you do not have permission to use it.

Test permissions for User1:

 /* test Permissions for User1*/

EXECUTE AS USER = 'User1' ;  
GO  
INSERT INTO Schema1.tbl_Sample_Table VALUES ('XYZ');
GO
EXEC Schema2.sp_Test 'XYZ';
GO
UPDATE Schema1.tbl_Sample_Table 
SET Sample_Data = '123' 
WHERE Sample_Data = 'XYZ';
GO
SELECT * FROM Schema2.vw_Test;
GO
DELETE FROM Schema1.tbl_Sample_Table
WHERE Sample_Data = '123';
GO
SELECT Schema2.fn_Test() as Function_Result;
GO
REVERT ;  
GO

It should return no errors:

If we try to use User1 to create an object like this:

/* test Permission failures for User1*/
EXECUTE AS USER = 'User1' ;  
GO  
CREATE PROCEDURE Schema2.sp_Failed_Test as SELECT 1 as ABC;
GO
REVERT ;  
GO

Will get an error:
Msg 262, Level 14, State 18, Procedure sp_Failed_Test, Line 1 [Batch Start Line 130]
CREATE PROCEDURE permission denied in database 'TestPermissionDB'.

Do not forget to drop test database at the end:

/* Drop Test database */
use master;
GO
DROP DATABASE TestPermissionDB;
GO