Improving Sql Server xp_cmdshell and CLR Integration Performance

Question

5.00/5 (1 vote)

See more:

Is it possible to keep dll's loaded in memory so that sql server does not load and unload them with every call to xp_cmdshell or an equivalent clr integration function?

I need to loop over a small dataset and see a very significant delay over having run the whole thing in C for example.

As an example, executing

'c:\Addins\TEST.exe'

takes a fraction of a second

However, the following takes 16 seconds!

master..xp_cmdshell 'c:\Addins\TEST.exe', no_output

I am thinking of creating an intermediate EXE to call C:\Addins\TEST.exe to see if the 15+ second delay persists.

If it does, should I try extended stored procedures?

Posted 15-Aug-11 3:07am

T2102

Updated 15-Aug-11 5:45am

v3

Add a Solution

Comments

Herman<T>.Instance 15-Aug-11 14:35pm

You can create a windows service in which you keep your object alive and where a timer each minute runs you check.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2011-08-15T09:49:00

Solution 1

When you execute the xp_cmdshell basically the following things happen:

check if the option is enabled
few other security checks
account information is gathered depending who calls the procedure
a new OS shell session is created with the account information and the environment is loaded
Command is executed
Output is gathered (unless no_output is used)
Shell session is closed and the output is shown

Especially the initialization of the shell session can be really time consuming. The next time you make the call, basically everything is done again.

If this is managed code, without knowing any more about the dependencies of your executable, I would suggest that you move the functionality of the exe inside a dll and create a small exe that uses (basically calls) this dll. After that, you can register this dll inside the SQL Server and create a clr stored procedure to call the functionality of the dll from T-SQL.

For more info about CLR stored procedures: http://msdn.microsoft.com/en-us/library/ms131094.aspx[^]

Posted 15-Aug-11 9:49am

Wendelius

Comments

T2102 15-Aug-11 17:48pm

Thanks, I am now using no_output. Could windows firewall have a meaningful effect on run-time too? I toggled it on and off a couple of times, and the run-time dropped. I think it's most likely due to less internet traffic now.

Wendelius 15-Aug-11 18:08pm

The firewall or the net traffic won't affect the execution time but they do affect the overall time. In other words if the request isn't going to the server quickly, the overall time suffers.

There are several other things that may affect the performance. For example during high load you may have a memory shortage on the server when you call your exe. In that case memory has to be freed first and then your exe starts running. This would affect the execution time directly. Same goes with CPU.

T2102 16-Aug-11 11:59am

When I execute the stored procedure calling the executable directly in an executable/dll, it seems to be faster than executing the stored procedure in SQL Server Management Studio. Could the time lag be attributable to SQL Server Management Studio or some option set in it? I've already changed the packet size to 32767 since my internet connection can be slow and a communicate with headquarters on the west coast when I live on the east coast.

Wendelius 17-Aug-11 11:48am

Yes, Management Studio has a small overhead, from query/query options you can set NOCOUNT option on and check that you don't use SET SHOWPLAN TEXT, SET STATISTICS TIME and SET STATISTICS IO. With a slow connection the amount of round-trips reduced the performance significantly. So instead of using Management Studio as a reference, you should create a small program that calls your procedure from your location. This way you will see the actual response time.