Celebrating Pi-Day by drawing sPiral

Happy Pi-Day!

That is pretty easy exercise to draw a sPiral from Pi-Digits.
The most difficult part was to get 5000 of them!

Here is the code to draw the sPiral:

SET NOCOUNT ON

DECLARE @t TABLE (g GEOMETRY, p TINYINT, id INT IDENTITY(1,1));

DECLARE @i INT = 1;

DECLARE @R FLOAT = 0;

DECLARE @x0 FLOAT = 0, @y0 FLOAT = 0;

DECLARE @x2 FLOAT = 0, @y2 FLOAT = 0;

DECLARE @Angle FLOAT = 0, @Delta FLOAT = 0;

DECLARE @Pi VARCHAR(8000) =

'3.1415926535897932384626433832795028841971693993

7510582097494459230781640628620899862803482534211

7067982148086513282306647093844609550582231725359

4081284811174502841027019385211055596446229489549

3038196442881097566593344612847564823378678316527

1201909145648566923460348610454326648213393607260

2491412737245870066063155881748815209209628292540

9171536436789259036001133053054882046652138414695

1941511609433057270365759591953092186117381932611

7931051185480744623799627495673518857527248912279

3818301194912983367336244065664308602139494639522

4737190702179860943702770539217176293176752384674

8184676694051320005681271452635608277857713427577

8960917363717872146844090122495343014654958537105

0792279689258923542019956112129021960864034418159

8136297747713099605187072113499999983729780499510

5973173281609631859502445945534690830264252230825

3344685035261931188171010003137838752886587533208

3814206171776691473035982534904287554687311595628

6388235378759375195778185778053217122680661300192

7876611195909216420198938095257201065485863278865

9361533818279682303019520353018529689957736225994

1389124972177528347913151557485724245415069595082

9533116861727855889075098381754637464939319255060

4009277016711390098488240128583616035637076601047

1018194295559619894676783744944825537977472684710

4047534646208046684259069491293313677028989152104

7521620569660240580381501935112533824300355876402

4749647326391419927260426992279678235478163600934

1721641219924586315030286182974555706749838505494

5885869269956909272107975093029553211653449872027

5596023648066549911988183479775356636980742654252

7862551818417574672890977772793800081647060016145

2491921732172147723501414419735685481613611573525

5213347574184946843852332390739414333454776241686

2518983569485562099219222184272550254256887671790

4946016534668049886272327917860857843838279679766

8145410095388378636095068006422512520511739298489

6084128488626945604241965285022210661186306744278

6220391949450471237137869609563643719172874677646

5757396241389086583264599581339047802759009946576

4078951269468398352595709825822620522489407726719

4782684826014769909026401363944374553050682034962

5245174939965143142980919065925093722169646151570

9858387410597885959772975498930161753928468138268

6838689427741559918559252459539594310499725246808

4598727364469584865383673622262609912460805124388

4390451244136549762780797715691435997700129616089

4416948685558484063534220722258284886481584560285

0601684273945226746767889525213852254995466672782

3986456596116354886230577456498035593634568174324

1125150760694794510965960940252288797108931456691

3686722874894056010150330861792868092087476091782

4938589009714909675985261365549781893129784821682

9989487226588048575640142704775551323796414515237

4623436454285844479526586782105114135473573952311

3427166102135969536231442952484937187110145765403

5902799344037420073105785390621983874478084784896

8332144571386875194350643021845319104848100537061

4680674919278191197939952061419663428754440643745

1237181921799983910159195618146751426912397489409

0718649423196156794520809514655022523160388193014

2093762137855956638937787083039069792077346722182

5625996615014215030680384477345492026054146659252

0149744285073251866600213243408819071048633173464

9651453905796268561005508106658796998163574736384

0525714591028970641401109712062804390397595156771

5770042033786993600723055876317635942187312514712

0532928191826186125867321579198414848829164470609

5752706957220917567116722910981690915280173506712

7485832228718352093539657251210835791513698820914

4421006751033467110314126711136990865851639831501

9701651511685171437657618351556508849099898599823

8734552833163550764791853589322618548963213293308

9857064204675259070915481416549859461637180270981

9943099244889575712828905923233260972997120844335

7326548938239119325974636673058360414281388303203

8249037589852437441702913276561809377344403070746

9211201913020330380197621101100449293215160842444

8596376698389522868478312355265821314495768572624

3344189303968642624341077322697802807318915441101

0446823252716201052652272111660396665573092547110

5578537634668206531098965269186205647693125705863

5662018558100729360659876486117910453348850346113

6576867532494416680396265797877185560845529654126

6540853061434443185867697514566140680070023787765

9134401712749470420562230538994561314071127000407

8547332699390814546646458807972708266830634328587

8569830523580893306575740679545716377525420211495

5761581400250126228594130216471550979259230990796

5473761255176567513575178296664547791745011299614

8903046399471329621073404375189573596145890193897

1311179042978285647503203198691514028708085990480

1094121472213179476477726224142548545403321571853

0614228813758504306332175182979866223717215916077

1669254748738986654949450114654062843366393790039

7692656721463853067360965712091807638327166416274

8888007869256029022847210403172118608204190004229

6617119637792133757511495950156604963186294726547

3642523081770367515906735023507283540567040386743

5136222247715891504953098444893330963408780769325

9939780541934144737744184263129860809988868741326

0472'

DECLARE @c CHAR(1), @s TINYINT = 0, @n TINYINT = 0

WHILE @i <= 5110

BEGIN

  SELECT @c = SUBSTRING(@Pi,@i,1)

  IF @c between '0' and '9'

  BEGIN

       SET @n = CAST(@c as INT)

       SELECT @Delta = CASE

              WHEN @R = 0 AND @i = 1 THEN 0

              WHEN @R = 0 AND @i > 1 THEN (@s+@n+0.2)*Pi()/9

              WHEN @i < 5 THEN (@s+@n-@i/3)/(@R)

              ELSE (@s+@n+0.1)/@R END

       SELECT @Angle += @Delta

       SELECT @R += 9 * @Delta / Pi()

       SELECT @x2 = @x0 + COS(@Angle) * @R

              , @y2 = @y0 + SIN(@Angle) * @R

       SET @s = @n

       INSERT INTO @t

       SELECT CONVERT(GEOMETRY,'POINT('

              +CAST(@x2 as VARCHAR)+' '

              +CAST(@y2 as VARCHAR)+')'

              ).STBuffer(@s+0.1), @s

  END

  SET @i += 1

END

SELECT TOP 5000 * FROM @t ORDER BY id;

GO

Parallel Execution of multiple queries with Logging

<= Simple Parallel Execution without Logging

In my previous post I created a simple procedure to run multiple queries in parallel.
It was very simple and easy to use, but for more advanced production processes it is too simple.
In order to satisfy additional user requirements I added some functionality to that procedure.

Here is the new edition I have:

CREATE PROC sp_Parallel_Execution

@SQLCommand NVARCHAR(4000),

@QueueLen INT = 100,

@delete_level INT = 3,

@LogQuery INT = 1

/****************************************************************************************

Name: sp_Parallel_Execution

Description: Allows parallel execution of multiple queries using SQL Server Agent.

Parameters:

@SQLCommand - SQL Server command to execute.

@QueueLen - Limit of jobs in execution queue (default is 100)

@delete_level Options:

3 - Job will be deleted after execution (default);

2 - Job will be deleted in case of failure;

1 - Job will be deleted in case of success;

0 - Job won't be deleted.

@LogQuery Options:

0 - Executed SQL Command won't be included in the log;

1 - Executed SQL Command will be included in the log only in case of an error (default);

2 - Executed SQL Command will be included in the log.

Author: Slava Murygin

www.slavasql.blogspot.com/2019/02/parallel-execution-of-multiple-queries.html

****************************************************************************************/

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, @delete_level=@delete_level;

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

SET @SQL = 'DECLARE @SQL NVARCHAR(4000), @Message VARCHAR(8000), @IsError TINYINT = 0, @Start_Dt DATETIME = GetDAte();' + CHAR(10)

 + 'BEGIN TRY' + CHAR(10)

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

 + ' EXEC sp_executesql @SQL;' + CHAR(10)

 + 'END TRY' + CHAR(10)

 + 'BEGIN CATCH' + CHAR(10)

 + ' IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;' + CHAR(10)

+ ' SELECT @IsError = 1, @Message = ' + CHAR(10)

+ ' ''Error Number: "'' + CAST(ERROR_NUMBER() AS VARCHAR) + ''"'' + CHAR(10)' + CHAR(10)

+ ' + ''In Line #'' + CAST(ERROR_LINE() AS VARCHAR) + CHAR(10)' + CHAR(10)

+ ' + ''Message: "'' + ERROR_MESSAGE() + ''"'';' + CHAR(10)

+ 'END CATCH ' + CHAR(10)

+ 'IF OBJECT_ID(''dbo.tbl_Parallel_Execution_Log'') is Not Null' + CHAR(10)

+ 'BEGIN' + CHAR(10)

+ ' INSERT INTO dbo.tbl_Parallel_Execution_Log(Start_Dt, IsError, Message, SQLQuery)' + CHAR(10)

+ ' SELECT @Start_Dt, @IsError, @Message, '

+ CASE @LogQuery WHEN 0 THEN 'NULL' WHEN 2 THEN '@SQL' ELSE 'CASE @IsError WHEN 1 THEN @SQL END' END + CHAR(10)

+ 'END' + CHAR(10);

/*

Create a table for logging

CREATE TABLE tbl_Parallel_Execution_Log(ID INT IDENTITY(1,1), Start_Dt DATETIME, Finish_Dt DATETIME DEFAULT GetDAte(), IsError TINYINT, Message VARCHAR(8000), SQLQuery NVARCHAR(4000));

*/

EXEC msdb.dbo.sp_add_jobstep @job_name = @CommandName, @step_name = N'Parallel_Execution_Command_1', @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

The major changes I've made:
1. Added execution logging functionality. If table "tbl_Parallel_Execution_Log" is present in current database then execution will be automatically logged. By default it won't log executed query, but you can still record it if you specify "@LogQuery" parameter as "2". Log table definition is included into stored procedure just in case:

CREATE TABLE tbl_Parallel_Execution_Log(

       ID INT IDENTITY(1,1),

       Start_Dt DATETIME,

       Finish_Dt DATETIME DEFAULT GetDAte(),

       IsError TINYINT,

       Message VARCHAR(8000),

       SQLQuery NVARCHAR(4000)

);

2. By Steve Ledridge's advise I've added SQL Agent Job deletion not as a separate step, but as job's property. Now by controlling "@delete_level" parameter you have a flexibility to delete a job on failure or success. However, with logging functionality there is very limited number of cases when you'd need to research failed job.

Enjoy!

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.