New version of sp_WhoIsActive (v11.20) is available – Deployed on 123 instances in less than 1 minute

Last night, I received Adam Machanic’s (b | t) newsletter “Announcing sp_whoisactive v11.20: Live Query Plans”.

For those who don’t know about it, sp_WhoIsActive is a stored procedure that provides detailed information about the sessions running on your SQL Server instance.
It is a great tool when we need to troubleshoot some problems such as long-running queries or blocking. (just two examples)

This stored procedure works on any version/edition since SQL Server 2005 SP1. Although, you only will be able to see the new feature (live query plans) if you run it on SQL Server 2016 or 2017.

If you don’t receive the newsletter you can read this announcement here and subscriber to receive the next ones here.

You can read the release notes on the download page.

Thank you, Adam Machanic!

The show off part

Using the dbatools open source PowerShell module I can deploy the new latest version of the stored procedure.

By running the following two lines of code, I updated my sp_WhoIsActive to the latest version (we always download the newest one) on my 123 instances in less than one minute (to be precise, in 51,717 seconds).

$SQLServers = Invoke-DbaSqlcmd -ServerInstance "CentralServerName" -Query "SELECT InstanceConnection FROM CentralDB.dbo.Instances" | Select-Object -ExpandProperty InstanceConnection
Install-DbaWhoIsActive -SqlInstance $SQLServers -Database master

The first line will retrieve from my central database all my instances’ connection string.
The second one will download the latest version, and compile the stored procedure on the master database on each of the instances in that list (123 instances).

Thanks for reading

2 thoughts on “New version of sp_WhoIsActive (v11.20) is available – Deployed on 123 instances in less than 1 minute

  1. I’m using the Registered Server feature (can be found in the View menu) of SSMS to deploy suff as this to all my instances at once.

    Yes, you have to set it up once and register all your servers (as you have to define your connection strings in the example above), but it allows you to create folders, so that you can organinze your servers (e.g. production, developement, legacy servers)

    Like

    1. Hi Thomas, thanks for your comment.

      There are multiple ways to accomplish this task, and your approach is perfectly valid. It doesn’t work so well in my environment because of CMS’ limited authentication options.

      If you’re interested, dbatools supports Registered Servers as input. Once you’ve got your Central Management Servers (CMS) setup, you can use `Get-DbaRegisteredServer` then pipe that to `Install-DbaWhoIsActive`” like this `Get-DbaRegisteredServer -SqlInstance server1 | Install-DbaWhoisActive`”
      You can read more about it here https://dbatools.io/functions/get-dbaregisteredservername/. You can even use the `-Group` parameter to filter the folders you want.

      If you need help with it please visit #dbatools channel on SQL Server Community on slack (https://dbatools.io/slack/)

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s