HOW TO: Output STATISTICS IO Details Using Powershell

Recently I found myself looking to automate a process that would connect to a database server and run a bunch of queries and return the results of SET STATISTICS IO for each one. I could certainly load my scripts into SSMS and output the results to a text file, but I wanted to leverage Powershell for my testing because Powershell can do things that SSMS cannot do as easily.

After deploying my Google-fu on the series of tubes that make up the Internet I found the -Verbose switch and decided to give that a try. Using the Powershell ISE I input the following code:

invoke-sqlcmd -ServerInstance "SQLROCKSTAR-ONE\JAMBON" -Database master -Query "SET STATISTICS IO ON;
SELECT c.CustomerID, soh.ShipDate
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Sales.Customer c ON c.CustomerID = soh.CustomerID
WHERE sod.SpecialOfferID = 2
AND soh.ShipDate between '2003-01-01 00:00:00.000' and '2010-01-01 00:00:00.000'
AND c.TerritoryID = 9;
SET STATISICS IO OFF" -Verbose | Out-File c:\scripts\out.txt

But that code didn’t work as I expected. I got an output file with the result set, but no statistics details. The ISE output window did show my statistics info, however, so I knew that -Verbose was working:

So now I had a bit of a mystery. How was I to get those details into my output file? I want them in the output file to make it easier for reviewing the results against multiple databases, or even multiple servers.

So, how to get that done?

Back to my Google-fu and I found out about the Start-Transcript command in Powershell. This seems promising! I did a quick rewrite of my query, added in some line breaks using Write-Host (don’t judge me) and arrived at the following code:

$TranscriptFile = "c:\scripts\trans.txt"
Start-Transcript -Path $TranscriptFile

Write-Host "Running customer query"
Write-Host "`r`n`r`n"

invoke-sqlcmd -ServerInstance "SQLROCKSTAR-ONE\JAMBON" -Database AdventureWorks2012 -Query "SET STATISTICS IO ON;
SELECT c.CustomerID, soh.ShipDate
FROM Sales.SalesOrderDetail sod
INNER JOIN Sales.SalesOrderHeader soh ON sod.SalesOrderID = soh.SalesOrderID
INNER JOIN Sales.Customer c ON c.CustomerID = soh.CustomerID
WHERE sod.SpecialOfferID = 2
AND soh.ShipDate between '2003-01-01 00:00:00.000' and '2010-01-01 00:00:00.000'
AND c.TerritoryID = 9;
SET STATISTICS IO OFF" -Verbose | Out-File c:\scripts\out.txt

Write-Host "`r`n`r`n"
Write-Host "End customer query"
Write-Host "`r`n`r`n"

Stop-Transcript

Invoke-Item $TranscriptFile

I was all excited until I ran it and got this:

Start-Transcript : This host does not support transcription.

Well, that sucked. But all is not lost,  I simply need to run this script from the command line in order for the transcript to work:

The statistics details are now included in my output file, which you can see here:

Considering Powershell is so flexible I am certain there are better ways to get this done, but this solution works for me at the moment. I did find this post from the Scripting Guys that details more about how you can create a function in order to use the transcript from the ISE, so feel free to head there and modify it for your own needs.

As always, you’re welcome!