0

As part of performance regression testing of a backup product, one stage of the workflow requires updating a single table that exists in multiple databases on a single SQL Server instance and then taking incremental/differential backups of all the databases.

Currently I'm running the update query serially on each database via sp_ineachdb from the SQL Server First Responder Kit. sp_ineachdb executes the given query in the context of each database (in this case only user DBs).

I'm looking for advice on how to parallelize this update process as I have a highly performant SQL Server environment and it would be nice to be able to cut the completion time by a factor of 2 or more.

The query looks like this:

declare @cmd varchar(max)
declare @SqlError int

set @cmd = '

UPDATE [dbo].[Updates]
SET UN_ID = LTRIM(STR(FLOOR(RAND()*(10000000000-100000000+1))+100000000)),
    WHO_ID = LTRIM(STR(FLOOR(RAND()*(10000000000-100000000+1))+100000000)),
    CDC_ID = LTRIM(STR(FLOOR(RAND()*(10000000000-100000000+1))+100000000)),
    PracticeName = NEWID(),
    Incidents_2013 = RAND(CHECKSUM(NEWID())) * 1000000,
    Incidents_2014 = RAND(CHECKSUM(NEWID())) * 1000000,
    Incidents_2015 = RAND(CHECKSUM(NEWID())) * 1000000,
    Incidents_2016 = RAND(CHECKSUM(NEWID())) * 1000000,
    Incidents_2017 = RAND(CHECKSUM(NEWID())) * 1000000,
    Incidents_2018 = RAND(CHECKSUM(NEWID())) * 1000000
'

exec @sqlError = sp_ineachdb @command = @cmd, @user_only = 1;

I am running these updates from an automation framework. I could theoretically execute a separate C# program, but I'd prefer a T-SQL or Powershell solution.

IO blocking should not be an issue as primary storage is over 40 Gibps iSCSI vVols.

0

1 Answer 1

1

I have modified the Powershell script in Backup SQL Server Databases in Parallel with PowerShell by Pablo Echeverria on MSSQLTips.com to suit my purposes. I have so far seen update speed increases between 1.2x and 1.6x.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.