How to limit the use of SQL queries?
After running a large SQL query that is generated through my ASPX pages, I see the following two items listed in the sql profile.
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 CPUs are the same as the request, so this request really accepts 1609 * 3 = 4827?
The same happens with the case:
Audit Logout
Can I limit this? I am using SQL Server 2005.
source to share
No, it only takes 1609 processor milliseconds. What is the duration? I am betting on the same or slightly more because I doubt SQL Agent queries are using parallelism.
Are you trying to reduce background processes with the CPU? If so, then you are reducing functionality by disabling the SQL Agent (no backup, for example) and restarting SQL Server with switch -x
You also can't stop Auditing Login events ... this is what happens when you disconnect or close the connection.
However, are you maximizing processors? If so, you need to distinguish between "user" query memory and "system" memory used for swaping or (God forbid) generating your parity on RAID 5 disks.
A high CPU can often be solved with more RAM and better disk configuration.
source to share
First of all, some of what you see in SQL Profiler is cumulative, so you can't just add numbers on top. For example, the SPCompleted event will show the total time of all SPStatementCompleted events that make up it. Not sure if this is the problem here.
The only way to improve the processor is to really improve your query. Make sure it uses indices, minimizes the number of rows read, etc. Work with an experienced DBA on some of these techniques, or read a book .
The only slight mitigation I can think of is limiting the number of processors a request is made on (this is called the degree of Parallelism or DOP ). You can set this at the server level or specify it at the request level. If you have a multiprocessor server, this can ensure that one long request doesn't take up all the processors on the box - it leaves one or more processors free for other requests.
source to share