提高Sql Server xp_cmdshell和CLR集成性能 [英] Improving Sql Server xp_cmdshell and CLR Integration Performance

问题描述

是否可以将dll的加载保持在内存中，以便sql server不会在每次调用xp_cmdshell或等效的clr集成函数时加载和卸载它们?

我需要遍历一个小的数据集，例如，在C中运行整个程序会遇到很大的延迟.

例如，执行

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''

只需一秒钟的时间

但是，以下过程需要16秒！

takes a fraction of a second

However, the following takes 16 seconds!

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

我正在考虑创建一个中间EXE来调用C:\ Addins \ TEST.exe，以查看15秒钟以上的延迟是否仍然存在.

如果是，我应该尝试扩展存储过程吗?

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?

推荐答案

在执行xp_cmdshell时，基本上会发生以下情况:

• 检查是否启用了该选项
• 很少进行其他安全检查
• 根据谁调用该过程收集了帐户信息
• 创建了新的OS Shell会话帐户信息并加载环境
• 执行命令
• 收集输出(除非使用no_output)
• 关闭Shell会话并输出显示

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

这篇关于提高Sql Server xp_cmdshell和CLR集成性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！