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.
