Powershell using SMO is painfully slow

Question

Powershell using SMO is painfully slow

Lowell

SSC Guru

Points: 323504
More actions
March 13, 2014 at 12:23 pm

#283470

this is probably more of a rant than a request for a solution....
I threw a ton of time adapting and enhancing the script found in another topic here:
Powershell script to script out jobs, databases, users, linked servers, logins, roles, alerts, etc from a list of servers
if you looked at it, each function creates it's own instance of a $srv = "Microsoft.SqlServer.Management.Smo.Server" and does it's scripting work; i added some additional stuff that scripts out each database objects.
tested it on a single box, get the script results i like, and modify the txt file to have a few more for a more robust test.
just ten servers took hours with the original code; moving the SqlServer.Management.Smo.Server and other objects out of the functions,and passing them in speeded things up some, but dayum; in this world of instant satisfaction, powershell is looking to me like one of those overnight batch processing things.
I've found an SMO tweak like setting $Server.SetDefaultInitFields($true) so things load up front, instead of when they are referenced, but still, it's painfully slow;
if i needed to scan or re-scan a list of 20 or more servers, it's going to take all day. I'd need to start adding logic to skip if exists instead of letting it always script, just to squeeze a bit of performance.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

a part of the issue is probably memory management; i've been spoiled by the .Nets frameworks' garbage collection.

i see that PowerShell 2.0 had a problem with that, and explains why my simple powershell script was using a two plus gig of ram,and adding another meg per second or so, without even being close to 1/3 of the way through it's workload.

i'll install PS3.0 and try that, but i'm still a bit disappointed...powershell was supposed to make me a superhero.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398690 More actions · Answer 2

Hmmm... Not crazy about that approach in the script. Allen White has a script he uses to do the same thing. You might search his blog. I'll bet it's more efficient.

However, it's probably much more SMO than PowerShell. SMO has always been something of an afterthought at Microsoft.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLDCH SSChampion Points: 11601 More actions · Answer 3

It sounds like you may have already come across some of his information, but Ben Miller (@dbaduck) gave a presentation at our SQL Saturday much along these lines. Here's a link to the presentation and content, hopefully it helps.

http://sqlsaturday.com/viewsession.aspx?sat=222&sessionid=13897

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.