SQLPS takes 30 minutes to import. How to fix it?

  • Hey SSC,

    SQLPS on my workstation used to take around 30 seconds to import. Not exactly speed-of-thought, but acceptable.

    Now it takes around 30 minutes! This is unacceptable because it blocks my spontaneous PowerShell queries.

    What could have changed to make the module so unbearably slow to load?

    The ISE debugger has helped me to narrow down the cause, but not enough to say what the problem is or how to fix it.

    In a new instance of ISE I open a one-line script to import the module.

    Import-Module SQLPS

    I set a breakpoint on the line and start debugging.

    I press 's' repeatedly at the prompt to step into every subsequent breakpoint until the module has loaded.

    The debugger seems to skip some lines. I'm not sure I'm getting the whole picture, but I carry on.

    Between this line in Sqlps.ps1:

    Set-Variable -scope Global -name SqlServerMaximumTabCompletion -Value 1000

    and this line in SqlPsPostScript.ps1:

    Set-Location SQLSERVER:

    I have to wait a very long time.

    Just before the debugger returns control, it produces this output.

    WARNING: The local computer does not have an active instance of Analysis Services.

    The warning about Analysis Services could be misleading. Analysis Services has never been installed on my workstation. The warning appeared when performance was still fast enough.

    How can I track down the true source of the problem and fix it?

    Thanks!

  • SQLPS depends on another module called Microsoft.SqlServer.Management.PSProvider.dll.

    Importing the PSProvider module takes a very long time for me.

    Incidentally, it's the source of the warning about Analysis Services.

    I commented out the problem module in the NestedModules list to stop SQLPS importing it.

    NestedModules = @("Microsoft.SqlServer.Management.PSSnapins.dll"<#,"Microsoft.SqlServer.Management.PSProvider.dll"#>,"SqlPsPostScript.ps1")

    Now SQLPS imports quickly, but generates an error because it can't set the working location to SQLSERVER:.

    $ Import-Module SQLPS

    Set-Location : Cannot find drive. A drive with the name 'SQLSERVER' does not exist.

    At C:\Program Files (x86)\Microsoft SQL

    Server\110\Tools\PowerShell\Modules\SQLPS\SqlPsPostScript.ps1:1 char:1

    + Set-Location SQLSERVER:

    + ~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo : ObjectNotFound: (SQLSERVER:String) [Set-Location], D

    riveNotFoundException

    + FullyQualifiedErrorId : DriveNotFound,Microsoft.PowerShell.Commands.SetLocat

    ionCommand

    At least Invoke-Sqlcmd is working again, so this is a better situation.

    I would still like to get the SQLSERVER: drive working quickly because I use it to navigate my central management server.

    If I really need the SQLSERVER: drive and have 30 minutes spare, to I can import the provider directly.

    Import-Module "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll"

    Does anyone have a solution that doesn't involve hacking the SQLPS module?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply