Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQLPS takes 30 minutes to import. How to fix it? Expand / Collapse
Author
Message
Posted Thursday, June 5, 2014 7:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 11, Visits: 203
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!
Post #1577787
Posted Monday, June 9, 2014 1:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 11, Visits: 203
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?
Post #1578962
Posted Monday, July 28, 2014 7:53 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, November 16, 2014 4:52 AM
Points: 214, Visits: 871
Try this: http://blogs.msdn.com/b/powershell/archive/2008/09/02/speeding-up-powershell-startup-updating-update-gac-ps1.aspx
Post #1597139
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse