We have multiple sqlsrv2.exe processes (about 9) each eating up 5-10% of the CPU and it has stayed that way for more than 24 hours. It is causing clocking in/out of jobs to take an enormous amount of time (more than a minute) just to clock into an operation per employee. Is there any simple way to figure out what is causing the CPU load and to fix it without having to reboot the server??
Do you have any long running active tasks? MRP and such that are marked “Active” in the Task Agent?
Another option would be an infinite loop on a BPM. DO you have any new BPMS that could be running wild?
_sqlsrv2 processes are spawned when an ODBC connection to a Progress database is made. Do you have any long running ODBC Crystal reports?
Yes, we had tried running a crystal report that uses ODBC and it hung, then tried it again, and it hung. But we had ran that report many times before without issue. Is rebooting the only option I have to clear them out?
You could run sp_who on sql and kill the long running process with pkill but that’s a risk you may not want to take
You can use DBCC INPUTBUFFER() from sp_who2 (I would use the sp_who2 over sp_who) This will show you the lastest running transaction for the that SPID. It will also show you any locking/blocking. Which can happen.
I cannot agree more with Jose Kill is great, but use with caution, if at all.
Here is a link about the two What is difference between SP_WHO & SP_WHO2?
If you are interested in going deeper then you can use tools like sp_WhoIsActive by Adam Machanic.
This link has some more SQL information
It can be rather interesting to delve down looking at things and how SQL works, but it all takes time and at the end of the day we want to resolve the problem with as little impact to our users, and restarting the server does sound like a sledge hammer approach.
Is the report a custom or out of the box?
I am assuming there is a BAQ behind the report, what does it do?