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
59 Seconds and a significantly use of CPU
Code 2 – running workflow
1.56 Seconds and I can use my laptop as cooker
Code 3 – running asynchronous using runspaces
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