Using popt To Switch Tabs In Powershell ISE

Jana Sattainathan shows how to open a specific Powershell ISE tab without moving your hands off the keyboard:

This is a function that activates a tab based on a partial search string that you type in the CLI portion of the ISE. Among the numerous tabs you have open in the ISE, to activate a tab that has the word “register”, you would just type in

1
popt register

That’s it. The tab that has the word “register” will become active.

Why “popt” instead of a standard name?

“popt” is the alias you can create for the real “search and activate function” Pop-FileInISETab. It stands for “poptab”. You don’t want to trade one problem with another by having to type that long name, so, there is functionality to optionally create an alias named “popt” for the function.

At first I said to myself, “Why would anybody have this many tabs open?”  Then I looked at my instance of SQL Server Management Studio.  Then I decided I wanted this for SSMS.

Export-DMVInformation Updates

Sander Stad has made changes to his Export-DMVInformation Powershell module:

Last Friday I had the chance to show the Export-DMVInformation module to the Dutch Powershell user group. After the presentation I got a couple of suggestions and wanted to put them in place them into the module.

Changes:

  1. Possibility to execute the module using the pipeline

  2. Get all the databases in one statement by assigning the value “ALL” to the database parameter.

  3. Replaced messages with verbose

Read on for more information, including where you can get the module and its Export-DMVInformation cmdlet.

Using Powershell To Add SQL Agent Job Steps

Rob Sewell gives a detailed walkthrough of a small Powershell script which adds a job step to every SQL Server Agent job:

This code was run on PowerShell version 5 and will not run on PowerShell version 3 or earlier as it uses the where method
I put all of our jobs that I required on the estate into a variable called $Jobs. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file

Click through for the script and line-by-line explanation.

Avoiding Hard-Coded Paths In Powershell

Jana Sattainathan gives a few options for getting around hard-coded paths in a Powershell script:

What if the KillerApp’s home folder suddenly moves?

Now, how do you make your app work with all its scripts without having to change code if you move it to a different folder?

You could now change the initial script that that dot-sources all the functions to alter the path and you are all set. This is still not ideal because you have to make a change when the location changed.

Click through for several options, including PSDrives and even automatically dot-sourcing Powershell scripts in the current and all child directories.

Altering Job Steps With Powershell

Rob Sewell uses Powershell to modify hundreds SQL Agent job steps on hundreds of SQL Server instances:

We will use the sqlserver module, so you will need to have installed the latest version of SSMS from https://sqlps.io/dl

This code was run using PowerShell version 5 and will not work on Powershell version 3 or lower as it uses the where method.

Lets grab all of our jobs on the estate. (You will need to fill the $Servers variable with the names of your instances, maybe from a database or CMS or a text file)

One oddity with SQL Agent jobs is that you absolutely need to call the Alter() method at the end or else the changes will not actually take effect.

Searching For Powershell Functions In Scripts

Stuart Moore has a regex which looks for Powershell cmdlets used in a script:

Having had a quick bingle around for a prewritten regex example I didn’t come up with much that fitted the bill. So in the hope that this will help the next person trying to do this here they are:

Assumptions:

  • A PowerShell function name is of the form Word-Word

  • A PowerShell function definition is of the form “Function Word-Word”

  • A Powershell function call can be preceeded by a ‘|’,'(‘, or ‘ ‘

  • The script is written using a reasonable style, so there is a ‘ ‘ post call

Click through for the script.

Exporting The Plan Cache

Grant Fritchey wrote a Powershell script to export query plans from the plan cache into a .sqlplan file:

I’ve gone minimal on the script. I’m creating a connection to the local instance, defining a command, and returning the data into a data set. From there, since the data set consists of a single column, I’m walking through them all to export out to a file:

It’s Powershell, so it’s a short snippet.

Using Powershell To Shred Query Plan XML

Mike Fal shows how to use Powershell (or any .NET language) to read parts of a query plan:

Once the pattern is down, the use is pretty straightforward. There’s also more options accessible to you. If we just look at the RunTimeCountersPerThread node, we can compare other values such as Rows, Scans, and CPU time. We could really get crazy and extract all the different statements within the batch. There are numerous possibilities for analysis and review.

I’m not here to tell you that you should start using PowerShell to automate query tuning. Query performance is an art form and requires a lot of case-by-case analysis. However, like any great carpenter, it’s good to know the capabilities of your tool set. Understanding the options available to you not only helps you be more effective, but can also provide answers you may not have had access to.

It’s another tool for the belt.

Powershell Defaults

Michael Sorens has some time-saving defaults for Powershell:

Besides setting up some convenient shortcuts for use with built-in cmdlets, for developers it is also handy to be able to work some magic with your own custom cmdlets. In my shop, for example, we have one module containing a couple dozen cmdlets, many of which use a common parameter, Mode. This Mode parameter varies from client to client, but for any single client, every cmdlet working on their data needs to use the same value of Mode. So I have to add -Mode hist-0010-dev.test onto each cmdlet I am using, which gets very tiring/annoying very quickly. You could, of course, put that value into a variable and then just use, e .g. -Mode $myMode, which is less typing, but if less is better, then no typing at all is better still.

There are a lot of tips in this article, so take some time with it.

Powershell On Windows 10 Bash

Max Trinidad goes all the way with installing an Ubuntu environment on Windows:

It’s a known fact, if you install PowerShell Open Source in Windows 10 Bash subsystem, that it won’t work correctly. As soon as start typing $PSVersionTable and press enter, the cursor goes to the top of the screen. And, you keep typing and it gets very ugly.

Now, what if I tell you, I found the way to run PowerShell Open Source without any of these issues. Just like running it like it was installed in a Linux environment. No issues with the cursor going crazy and able to page up and down.

There are quite a few steps here, but Max lays them out clearly.

Categories

February 2017
MTWTFSS
« Jan  
 12345
6789101112
13141516171819
20212223242526
2728