xp_cmdshell vs. stored proc

Question

xp_cmdshell vs. stored proc

APSolutely

Old Hand

Points: 390
More actions
December 17, 2014 at 12:28 am

#299569

Hi All,
Due to our 3 month, year end change freeze, we are not allowed to create new stored procedures at the moment on a production database.
We have done all the development work for new features on the production site and would like to start running them as soon as possible.
However due to the change freeze till end January, the only way we can reliable run the scripts is by running the *.sql file using parameters through xp_cmdshell.
I have examined the execution plans and everything looks the same (development proc vs xp_cmdshell execution), we do however see a minor decrease in performance when using the xp_cmdshell method. We can live with that however for the course of the change freeze.
Are there any other down sides to using this method that we might have over looked and are the execution plans cached when we use the xp_cmdshell method ?
Here is a sample of how we would execute the script till February using XP_cmdshell
DECLARE @SQLCMD VARCHAR(8000)
----CLIENTA
SET @SQLCMD = 'sqlcmd -S SERVER\MSSQL2014DEV -d CLINETA_Database -i \\servername\scripts\script1.sql -v db="TargetDatabase" destination="OutputTable" schema="CUSTOM" source="SourceTable"'
EXECUTE xp_cmdshell @SQLCMD;
This is how the stored proc kicks off
EXEC dbo.CLIENTA_NewProc
@db = 'TargetDatabase',
@destination = 'OutputTable',
@schema = 'CUSTOM',
@source = 'SourceTable'
Thanks in advance

Viewing 6 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 246963 More actions · Answer 1

To be honest, I'd be speaking to whoever has asked for these changes and telling them that they cannot go to production because of the change freeze.

It is then down to them to argue with whoever polices the freeze if they want to bend the rules. If they get an official OK to that request, you can go ahead and do the job properly.

Otherwise, you are setting yourself up for a fall: if something in your procs causes production issues, who do you think will get into trouble?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

When 'freezing' new development i'd freeze XP_cmdshell first of all, i should say. 🙂

Obvious reasons why performance slightly degrades are

1.Every time you run new sqlcmd instance it creates new connection.

2.This utility uses ODBC to execute Transact-SQL batches instead of SqlClient.

Sqlcmd is an ordinary client application from SS point of view and all queries it submits to SS are treated as any other queries.

APSolutely Old Hand Points: 390 More actions · Answer 3

Thanks for the replies,

Phil Parker, the request comes from high up the food chain and business will never understand why for 3 months of year we can only do "theoretical" development work which will only come to fruition in February. We did have the conversation around the purpose of the system freeze, primarily because we run a skeleton staff compliment over the Christmas period, but their argument was "but its December, systems should be quiet, what better time to do development". So in their infinite wisdom being told to find a quick fix till February was a viable option and execute xp_cmdshell was the only solution we had.

The disconnect between business and any IT department ...

andrew gothard SSChampion Points: 12314 More actions · Answer 4

Phil's point still stands.

There's a production freeze, you ignored it, if / when this comes out you're right in the firing line for ignoring it. In most places I've ever worked, production freezes are there for a reason, ignoring them is a dismissable (and in some cases potentially a criminal) offence.

There is usually a protocol for exceptions, changes which are essential may be put in place, usually with authorisation from a C level bigwig or the board. Putting in kludges to get round a freeze could be career limiting.

I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I have to agree with Phil and Andrew. I would adhere to corporate policy and let the requester engage in the fight to get their work released to production. You might not see the rationale for the freeze, but that doesn't mean it's invalid. Besides, if it came from "high up the food chain" then they have a good chance of getting it done. To Andrew's point, there's always a process to handle exceptions.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions