PowerShell Progress Meter

In many of my past PowerShell scripts, I've included my own home-spun progress report in the results window. I like that a lot, but my mind was blown when I noticed that there is a "Write-Process" cmdlet...with some re-tweaking I made it rock and much more efficient.

So the situation where you would want to use this is if you are looping over a large dataset and it takes a while to bring back results (I often will use PowerShell to hit all the servers I control, get some information about them and return it to me (backup status, if they are VM's, configuration info, etc). It can take 20+ minutes to get to all the servers and it is nice to know how far along the process is; thats where this comes in handy.

Some things to keep in mind...You need to have some resultset to work with, it doesn't have to be a query, but it does have to be some kind of System.Array. After you generate the Array, run this bit of code...I'm calling my array "results":

<################################
 ##  SETUP PROGRESS VARIABLES  ##
 ################################>
$ResultsLen = $results.Length
$LastPercentComplete = 0
[datetime]$DateStart = Get-Date -format G

This sets up some of the variables we will need inside the progress meter. Next, just after you issue your "foreach ($result in $results) {" You run the following code:

<##########################
 ##  RUN PROGRESS METER  ##
 ##########################>
foreach ($result in $results) 
{
 $currentRow++
 $PercentComplete = [math]::floor(($CurrentRow/$ResultsLen)*100)
 [datetime]$DateNow = Get-Date -format G
 $diff = $DateNow-$DateStart
 $DiffSeconds = $diff.TotalSeconds
 $SecondsPerRecord = $DiffSeconds/$currentRow
 $RemainingSeconds = $SecondsPerRecord*($ResultsLen-$currentRow)
 $SecondsPerRecord = [Math]::Round($SecondsPerRecord,2)
 
 Write-Progress -Activity "Investigating Servers..." `
  -PercentComplete $PercentComplete `
  -SecondsRemaining $RemainingSeconds `
  -CurrentOperation "Current Row: $currentRow / $ResultsLen.   Seconds/Record: $SecondsPerRecord   Run Time (seconds): $DiffSeconds" `
  -Status "Please wait."

 Remove-Variable DateNow
 Remove-Variable DiffSeconds
 Remove-Variable SecondsPerRecord
 Remove-Variable RemainingSeconds
}

You don't need to remove your variables, I just like to do it to clean up my code, I do this at the bottom of the script:

Remove-Variable ResultsLen
Remove-Variable currentRow
Remove-Variable LastPercentComplete
Remove-Variable DateStart

Want to see it in action? Take my script and add this to the top of it (creates an array with 10000 items, which should keep the status bar up long enough for you to see):
$results = 1..10000

This looks awesome in PowerGUI, which is what I use for PowerShell Development, it looks a bit funky in the native PS window, but good enough to get you the idea. If you aren't using PowerGUI, give it a shot, it's free. http://www.powergui.org

As always, please leave comments below if you find this useful or have other suggestions. It's nice to know people read these posts.

SQL 2012 Extended Event Wizard Question

I'm having a bit of trouble figuring out how to translate my 2008 R2 Extended Events into the GUI for 2012. Most of it seems pretty straight forward, but I'm having an issue how to put the values from the WHERE clause into the GUI.

Here is my query (which works in 2012, if I simply execute it)

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='MonitorExpensiveQuery') 
 DROP EVENT SESSION MonitorExpensiveQuery ON SERVER 
GO 
--Creating a Extended Event session with CREATE EVENT SESSION command 
CREATE EVENT SESSION MonitorExpensiveQuery ON SERVER 
ADD EVENT sqlserver.sql_statement_completed 
( 
 ACTION 
 ( 
        sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.username
        ,sqlserver.nt_username
        ,sqlserver.session_id
        ,sqlserver.sql_text
 ) 
 WHERE (
   duration > 3000000
   OR cpu_time > 1000
   OR reads > 10000
   OR writes > 10000
  )
)
,ADD EVENT sqlserver.sp_statement_completed 
( 
 ACTION 
 ( 
        sqlserver.client_app_name
        ,sqlserver.client_hostname
        ,sqlserver.username
        ,sqlserver.nt_username
        ,sqlserver.session_id
        ,sqlserver.sql_text
 ) 
 WHERE (
   duration > 3000000
   OR cpu_time > 1000
   OR reads > 10000
   OR writes > 10000
  )
)
ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS, STARTUP_STATE = ON) -- The target
GO

ALTER EVENT SESSION MonitorExpensiveQuery ON SERVER STATE = START
GO

So I kick off the GUI and the first few pages seem pretty straight forward...Add my Session name:

Choose to not use a template:

Select the Events:

Select the Actions:

But when I get to the Filter page, which I am guessing is the equivalent of the "WHERE" caluse, I don't see any of my fields (duration, cpu_time, reads or writes):

So I'm unsure how to properly set this up. Any help appreciated

SQL Saturday 149 Announcement

I found out that my submission for SQL Saturday 149 was accepted and I will again be presenting at the MN SQL Saturday this year. If you haven't signed up yet and you will be in the Twin Cities MN area on Saturday September 29th, 2012, you are missing out on one of the best SQL Server conferences around. This year, the event will be held at the University of Minnesota - Keller Hall, 200 Union Street SE, Minneapolis, MN 55455. Admittance to this event is free, but they do charge a lunch fee of 10.00 so that they can provide a lunch - not pizza! Please register soon as seating is limited, and let friends and colleages know about the event. You can find out more about this (and register!!) at the event homepage: http://www.sqlsaturday.com/149/eventhome.aspx