Emailing tempdb Query Results to Paul Randal with PowerShell

I was referred to someone on twitter today who wants to email query results without setting up database mail.  I explained this in Post 6 of PowerShell Week at SQL University but that example was somewhat complicated. Instead, I thought I’d whip up a new example using Paul’s latest survey.  Paul want’s to know know how many cores your instances have and how many data files that tempdb has.

Paul’s query is pretty simple:

SELECT os.Cores, df.Files
FROM
(SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,

(SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;
GO

I simply took that query and wrapped it with almost the same code that I used at SQL University.  The difference allows me to show off two new tricks. 

• First, I separated out the part that grabs the list of servers from your Registered Servers to happen before the foreach loop.  I had received feedback from a reader that A) Having it there made it hard to read on my website, and B) it simplified the logic flow.  It also has the added benefits of performing faster when you’re running against hundreds of instances like me, and making it a little more clear how to swap it out when you want to use something besides Registered Servers to list your instances.  For more information on the different data sources that you can use for reading in a list of servers have a look here at Post 5.
• Second I added this $S++; it is a little piece of code to enumerate the instances for you.  Last time I had written the results to a table and then read them out using DENSE_RANK but since we don’t have multiple results this time [and because I love showing off cool PowerShell code] I used this instead.

$ServerList = dir -recurse SQLSERVER:SQLRegistration'Database Engine Server Group' | where {$_.Mode -ne "d"}            
foreach ($RegisteredSQLs in $ServerList )
{
$S++;
$dt+=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -Query "
    (SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
    (SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df" -As 'DataTable'
}            
$MultipleResults = $dt | ConvertTo-Html -Property Instance, Cores, Files | Out-String;            
Send-MailMessage -To paul@SQLskills.com `
 -Subject "tempdb Survey Results" `
 –From YourEmailAddress@GoesHere.com `
 -SmtpServer YourSMTPServerGoesHere `
 -Body $MultipleResults -BodyAsHtml

Hopefully this will help you email query results to your heart’s content   Big thanks to TJay Belt ( blog | @TJayBelt ) for pointing out this request on the #SQLHelp hotline to me.  I’ve done this tons of times myself and I LOVE that anyone can walk up with a question and when people don’t have the answer they still do what they can to help them out right away.  I less than three community!!

Please Note:

DO NOT SPAM Paul Randal! You should probably try emailing this to yourself a time or two before you decide to send it to him.

I’m using Chad Miller’s (Blog|Twitter) invoke-sqlcmd2 to output the results as a data table (you’ll need that).

Finally a friendly reminder that unlike SQL Server, PowerShell persists variables so if you run this multiple times you’ll want to clear out the $dt variable like this: $dt=$null.