How to limit SQL queries CPU utilization?

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

This is an issue of connection string. If audit logout takes too much of your cpu then try to play with different connection string.