如何限制SQL查询CPU利用率？
通过我的 ASPX 页面构建的大型 SQL 查询运行后，我看到 sql 探查器中列出了以下两项。
Event Class TextData ApplicationName CPU Reads Writes
SQL:BatchCompleted Select N'Testing Connection...' SQLAgent - Alert Engine 1609 0 0
SQL:BatchCompleted EXECUTE msdb.sbo.sp_sqlagent_get_perf_counters SQLAgent - Alert Engine 1609 96 0
这些CPU与查询相同，那么该查询实际上需要1609*3=4827吗？
同样的情况也发生在 case 上：
Audit Logout
我可以限制这个吗？我正在使用 sql server 2005。
After a large SQL Query is run that is built through my ASPX Pages I see the following two items listed in sql profiler.
Event Class TextData ApplicationName CPU Reads Writes
SQL:BatchCompleted Select N'Testing Connection...' SQLAgent - Alert Engine 1609 0 0
SQL:BatchCompleted EXECUTE msdb.sbo.sp_sqlagent_get_perf_counters SQLAgent - Alert Engine 1609 96 0
These CPU is the same as the query so does that query actually take 1609*3=4827?
Same thing happens with case :
Audit Logout
Can I limit this? I am using sql server 2005.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
首先，您在 SQL Profiler 中看到的一些内容是累积的，因此您不能总是将数字相加。 例如，SPCompleted 事件将显示组成该事件的所有 SPStatementCompleted 事件的总时间。 不确定这是否是你的问题。
提高 CPU 性能的唯一方法是实际改进查询。 确保其使用索引，最大限度地减少读取的行数等。与经验丰富的 DBA 合作研究其中一些技术，或 读一本书。
我能想到的唯一缓解措施是限制查询运行的CPU 数量（这称为并行度，或DOP） 。 您可以在服务器级别设置它，或者在查询级别指定它。 如果您有一台多处理器服务器，这可以确保单个长时间运行的查询不会占用机器上的所有处理器 - 它将留下一个或多个处理器以供其他查询运行。
First of all, some of what you see in the SQL Profiler is cumulative, so you can't always just add the numbers up. For example, a SPCompleted event will show the total time of all the SPStatementCompleted events that make it up. Not sure if that's your issue here.
The only way to improve the CPU is to actually improve your query. Make sure its using indexes, minimize the number of rows read, etc. Work with an experienced DBA on some of these techniques, or read a book.
Only other mitigation I can think of is to limit the number of CPUs the query runs on (this is called Degree of Parallelism, or DOP). You can set this at the server level, or specify it at the query level. If you have a multiple processor server, this can ensure that a single long-running query doesn't take over all processors on the box--it will leave one or more processors free for other queries to run.
不，总共需要 1609 毫秒的 CPU 时间。 持续时间是多少？
我打赌相同或稍多，因为我怀疑 SQL Agent 查询是否使用并行性。
您是否正在尝试减少使用 CPU 的后台进程？ 如果是这样，则可以通过禁用 SQL 代理（例如，不进行备份）并使用 switch -x
您也无法停止“审核注销”事件...这就是您断开或关闭连接时发生的情况。
但是，您是否充分利用了处理器？ 如果是这样，您需要区分用于查询的“用户”内存和用于分页或（上帝禁止）在 RAID 5 磁盘上生成奇偶校验的“系统”内存。
高 CPU 通常可以通过更多 RAM 和更好的磁盘配置来解决。
No, it takes 1609 milliseconds of CPU in total. What is the duration?
I bet the same or slighty more because I doubt SQL Agent queries use parallelism.
Are you trying to reduce background processes using CPU? If so, then you reduce functionality by disabling SQL Agent (no backups then for example) and restarting SQL Server with switch -x
You also can not stop "Audit logout" events... this is what happens when you disconnect or close a connection.
However, are you maxing the processors? If so, you'll need to differentiate between "user" memory for queries and "system" memory used for paging or (god forbid) generating your parity on RAID 5 disks.
High CPU can often be solved by more RAM and a better disk config.
SQL Server 2008 有一个新的“资源调控器”，可能会有所帮助。 我不知道您是否使用 SQL Server 2008，但您可能想看一下 此处
SQL Server 2008 has a new "Resource Governor" that may help. I don't know if you're using SQL Server 2008 or not but you may want to take a look here
这是连接字符串的问题。 如果审核注销占用了太多的CPU，那么尝试使用不同的连接字符串。
This is an issue of connection string. If audit logout takes too much of your cpu then try to play with different connection string.