github twitter linkedin
Improve Load Time on SQLPS
16 Nov 2015
2 minutes read

Over the month of November a fellow PowerShell enthusiast, Mike Fal (blog|@Mike_Fal), did a series on the SQL Server PowerShell module (SQLPS). In reading his series curiosity struck on what the module does when it was loading. I also just answered a question on DBA.SE that I figured out by tweaking a particular file for this module, so thought I would share more details.

WARNING: You are modifying the files at your own risk. You have been warned.

If you are not familiar with the files involved with a module, you can read more on that here. The file I found most interesting is the “SqlPsPostScript.PS1” file, located in the SQLPS module folder for the given version of SQL Server:

C:\Program Files (x86)\Microsoft SQL Server\<version>\Tools\PowerShell\Modules\SQLPS\

This file is being called after the module is loaded as a nested module. You can find all of this by reading through the manifest file for SQLPS, SQLPS.PSD1.

The post script file does a handful things:

  • Sets the location of your session to SQLSERVER:\ (the provider)
  • Has a function that does the same thing, if you just enter “SQLSERVER:” at the prompt instead of typing the Set-Location. Could be a waste of 47 characters to me.
  • Loads the DAC assemblies for managing and creating DAC packages with PowerShell.
  • Loads the SQLAS module, if not already loaded.

Now the most annoying thing I always have with the SQLPS module is that it puts me in the location of the provider after it loads. I stop that by simply commenting out that first line in the file. A Measure-Command on loading the module before and after, shaves off a whole second (time is money).

The other thing I found was the warnings on the WMI service the poster on DBA.SE was getting, was strictly due to the SQLAS module being loaded. Which in the documentation I linked to in that question shows that this module utilizes WMI. Which that begs the question for Microsoft: Why you put WMI management into SQLAS, and not SQLPS?

Now the module itself when loading also loads numerous DLL files, which all those contain the magic that is SQLPS.

Back to posts