My school on SQL Server was in performance and tuning. I started my career as SQL Server developer doing stored procedures.

My thought was always getting it right and run fast. When I moved into administration, one of my goals has always been my life easier by creating solutions to automating processes. I don’t need to say how much PowerShell helps me.

Even thinking about automation, besides having to create flexible and simple solutions, the “run fast” also counts a lot. I have a friend who uses a solution, that I did, in Powershell and SQL Server Agent, gathering Event Viewer Data from the last day, filtering errors , storing in a SQL Server Table , generating a Excel file  and sending by email

He does in 350 instances of SQL Server. There is no way to do this procedure without being as fast as possible too. In this solution I use the split-job and runspaces and this process takes on average 30 minutes.

When I heard the first time on processing workflows and I confess I did not expect to be faster than using runspaces, but thought, erroneously, that it might be faster than the serialized process . Obvious that for 350 instances is out of the question even in parallel processing, but maybe for some other process with fewer instances.

Then, today, I started my test. I do need to study a LOT workflows, I am newbie on that and it is an AWESOME feature, but I just needed to try my idea.

I have a txt with several instances and 30 .SQL files to gathering data from SQL Server. The process is for each server, run these .SQL files in a specific Database and export to a .csv files

This is my ServersWorkflow.txt :

Code 1 : running synchronously

$VerbosePreference ='Continue'

$Database = "SQLDMRepository"

$Server = "Win8-Vm"

$pathSQL = "c:\scripts"

measure-command {

    Get-Content -Path "C:\temp\ServersWorkFlow.txt" | % {

        $ServerName = $_ 

        $ServerNameToFile = $_ -replace '\\','_'

        Get-childitem $pathSQL -Filter "*.sql"  | % {

            $FileName = $_.basename

            Write-Verbose "Performing $($ServerName) File $($FileName)"

            Invoke-Sqlcmd2     -Database $Database `

                            -Query (Get-Content $_.fullname) `

                            -ServerInstance $ServerName |Export-Csv "C:\Scripts\output\$($ServerNameToFile)_$($FileName).csv" `

                                                                -NoTypeInformation -Force 

        }

    }

} 

$VerbosePreference ='SilentlyContinue'

59 Seconds and a significantly use of CPU

Code 2 – running workflow

workflow Invoke-ScriptsParallel {

    param (

            [string]$Database,

            [string]$ServerInstance,

            [string]$pathSQL

          )

    $server = Get-Content -Path "C:\temp\ServersWorkFlow.txt" 

    foreach -parallel ($ServerName in $server) {

         $ServerNameToFile = $_ -replace '\\','_'

        $TSQL =  Get-childitem -path $pathSQL -Filter "*.sql"  

        "Server $($ServerName)"

        foreach -parallel($InTSQL in $TSQL) {

            $FileName = $InTSQL.basename

            $FullName = $InTSQL.fullname

            #"Performing $($FileName)"

               "Script $($FileName)"

            inlinescript {

                   ipmo functions -DisableNameChecking -Force

                Invoke-Sqlcmd2     -Database $using  atabase -Query (Get-Content -Path $using:FullName) -ServerInstance $using:ServerInstance |  Export-Csv    -Path "C:\Scripts\output\$($using:ServerNameToFile)_$($FileName).csv"  -NoTypeInformation -Force 

            }

        }

    }

}

$Database = "SQLDMRepository"

$Server = "Win8-Vm"

$pathSQL = "c:\scripts"

measure-command {

Invoke-ScriptsParallel $Database $Server  $pathSQL 

}

1.56 Seconds and I can use my laptop as cooker

Code 3 – running asynchronous using runspaces

measure-command {

    Get-Content -Path "C:\temp\ServersWorkFlow.txt" | % {

        $ServerName = $_ 

        $ServerNameToFile = $_ -replace '\\','_'

        Get-childitem -path "c:\scripts" -Filter "*.sql"  | split-job { % {

                    $Filename = "$($_.basename)"

                    write-verbose "Performing $($Filename)"

                    Invoke-Sqlcmd2     -Database SQLDMRepository  -Query (Get-Content -Path $_.FullName) -ServerInstance $ServerName | Export-Csv    -Path "C:\Scripts\output\$($ServerNameToFile)_$($FileName).csv"   -NoTypeInformation -Force 

        } } -InitializeScript {    ipmo functions -DisableNameChecking -Force } -noprogress 

    }

}

32 Seconds and my CPU is ok.

Then, facing these values, I asked for a help to Sir Jeffery Hicks and Don Jones.

The answer is the main purpose to use workflows it is not to run fast . There is some overhead in the  engine to create the workflow XML and handle the workflow endpoint. Because I am using INLINE script, my invoke-sqlcmd2 is running locally , generating with more overhead.

According Sir Jeffery Hicks, “Generally I think we’ll use workflows for long running, unattended tasks that we’ll kick off”.

Before to think in solutions on SQL Server using workflows in this scenario (long running, unattended tasks ), I DO need to understand it better.

I can say that is an AWESOME feature and like anything else,need to be carefully studied and tested.

That is it guys . After all, it is all about solutions

The thread that Jeffery and Don reply to me you can see in HERE

#PowerShellLifeStyle