XP_cmdshell performance

Question

XP_cmdshell performance

Judy Why

Hall of Fame

Points: 3656
More actions
January 29, 2008 at 8:28 am

#121911

Hi all,
Recently, I created a stored procedure to script all the databases on our production server and developement server based on the article I found in this website.
In developement, this command run 15 seconds.
exec master.dbo.xp_cmdshell '"C:\Microsoft SQL Server\MSSQL\Upgrade\Scptxfr.exe" /s DEVServer /P password /d DBName /f c:\Scripts\DBName_2008.01.29.sql /r'
in production, this command run 2 seconds
exec master.dbo.xp_cmdshell '"C:\Microsoft SQL Server\MSSQL\Upgrade\Scptxfr.exe" /s PROServer /I /d DBName /f c:\Scripts\DBName_2008.01.29.sql /r'
This db is very small. all the objects on both server are same. Production server is more powerful than developement server.
The question is why development took longer than production.

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

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

I'm sure there are a number of reasons specifically, but who cares really? The prod box is beefier... doesn't that answer the question right there?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Judy Why Hall of Fame Points: 3656 More actions · Answer 2

Thanks your reply.

the thing is Scripting all the databases in developemnt takes around 54 mins. but in production only 6 minutes.

I just want to know what affects the performance of xp_cmdshell

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

While I can't really comment on the performance of xp_cmdshell varying between environments (there shouldn't really be any other factors than the horsepower of the machine), I can highly recommend that you should not be using xp_cmdshell. There is a reason they've now disabled it by default in 2005.

If you're looking for a really good application to script out and source control your database objects, I HIGHLY recommend using Bill Wunder's DDL Archive Utility. I have been using it for over 2 years and currently implementing it my new job.

http://bwunder.com/ArchiveUtility.aspx

If you have any questions, I'd be more than happy to help.

Hopefully you find it as useful as I have.