使用查询或存储过程延迟 SQL Server 2000 查询直到处理器利用率低于 50%？
我有几个非常昂贵的查询，它们似乎占用了资源，这似乎使系统变得不堪重负。
在 SQL Server 2000 - 2008 中是否可以调用延迟函数来等待处理器资源恢复正常？
我的最终目标是返回并使用存储过程来提高这些效率，但与此同时，我需要让这些尽快工作，因为我正在重写遗留代码。
I have a several very expensive queries which seem to hog resources that seems to put the system over the top.
Is there a delay function I can call to wait until processor resources come back down in SQL Server 2000 - 2008?
My eventual goal is to go back and make these more efficient, use a sproc, but in the meantime I need to get these to work asap because I'm rewriting legacy code.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你可以尝试这样的事情：
要确定@Ticks值，只需编写一个循环来每10秒打印出@@CPU_BUSY值之间的差异。当系统处于低负载时，使用该值作为@Ticks。
you could try something like this:
to determine the @Ticks value, just write a loop to print out the difference between @@CPU_BUSY values every 10 seconds. When the system is at your low load, use this value as @Ticks.
除了 SQL Server 2008 的更高版本之外，您无法控制或限制 CPU。
您最好的选择似乎是设置选项以允许仅一半（或更少）的 CPU 用于任何查询。 两种方式完成此操作
另请参阅：
编辑：
问题是：你想延迟执行（处理 CommandTimneout、用户响应时间等所有问题）还是提高所有查询的并发性
这个答案应该提高并发性：我通常处理客户端应用程序，我不能让业务用户等待。
延迟执行时，您还必须延迟所有查询（例如禁止运行昂贵的查询），这会减少整个过程中的并发性，因为调用将备份。而且你必须小心大约同时开始的 2 个昂贵的查询
You can't control or throttle CPU except for higher editions of SQL Server 2008.
Your best option seems to be to set options to allow only half (or less) your CPUs to be used for any query. This can be done 2 ways
Also see:
Edit:
The question would be: do you want to delay execution (with all the issues like CommandTimneout, user response time etc) or improve concurrency for all queries
This answer should improve concurrency: I usually deal with client apps and I can't make a business user wait.
When delaying execution, you also have to delay all queries (say to disallow the expensive queries from running) which reduce concurrency throughout as calls will back up. And you'll have to be careful about 2 expensive queries starting around the same time
我在这里唯一能想到的在安静的时候真正开始工作的方法就是使用计划任务和 osql 来执行语句。计划任务必须选择在空闲时运行。
不过我不确定 50% 的情况。
该策略也不应对 SQL 版本太敏感。
The only thing I can think of here to actually kick things off in quiet times is to use scheduled tasks and
osqlto execute your statements. Scheduled tasks has to option to run when idle.I'm not sure about the 50% bit though.
This strategy shouldn't be too sensitive to SQL version either.