SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
iainelder
iainelder
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 210
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!
iainelder
iainelder
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 210
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?
Cody Konior
Cody Konior
SSC-Addicted
SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)SSC-Addicted (441 reputation)

Group: General Forum Members
Points: 441 Visits: 1105
Try this: http://blogs.msdn.com/b/powershell/archive/2008/09/02/speeding-up-powershell-startup-updating-update-gac-ps1.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search