How to determine which query is causing high dbsrv12 CPU usage

[Former Member]

Our system is composed of two components and SQL Anywhere, a back-end that runs as a Windows service, the SQL Anywhere server, and a front end program.

We know from watching Task Manager that, at certain points in operation, the CPU usage of dbsrv12 skyrockets, but only when both the back-end and the front end program are running. If we stop the front end program, the back-end runs along & dbsrv12's CPU usage is low.

Thanks to the feedback of Jeff Albion & JBShuler, I've got the CPU usage of my database monitor code down to next to nothing, and the CPU usage is still spiking, so I've proven that wasn't the cause.

I've got the SQL Console running right now on a laptop machine. I emptied its database of all data and started the download process over again. When the front end program is running, I can see dbsrv12's CPU usage hit more than 80%. The console is logging requests, but nothing in there tells me anything about how much CPU or any other resource the query that's running is using.

I really need to figure out which query is causing dbsrv12's CPU usage to increase. How do I narrow it down to a particular query or group of queries?

[graeme_perrow]

Does the CPU usage stay high for an unusually long time? Does the server response become noticeably slower? High CPU usage by itself just means the server is doing work, it's not necessarily indicative of a problem.

[Former Member]

The CPU usage is generally higher with the front-end running than without it. On one machine in our offices, which has 8 cores, the CPU usage of dbsrv12 without the front end running is less than 5%, but with the front end running, it's as high as 18%. At the time, our download process from our server product was not downloading anything, so our system was idle. Upon stopping the front end, CPU usage went back down, but it went back up when it was restarted.

Other times, the CPU usage is fine with both programs running.

If the database server is just performing some maintenance tasks & it's not our code's fault, that's fine, but I need to prove it. I need to show that it can't be a query that's running from the front end that's causing the problem, or that its a specific one that is causing the problem.

It sure looks like it's the front end that's causing the high CPU usage.

[Breck_Carter]

See Database Tracing In 50 Easy Steps.

Also: See what's happening now with Foxhound.