How to know the queries running in SQl server currently

If SQl server consumes 100% CPU usage execute the below query to know which thread is repetitive and consuming more CPU.

 

SELECT p.spid, p.status, p.hostname, p.loginame, p.cpu, r.start_time, t.text FROM sys.dm_exec_requests as r, master.dbo.sysprocesses as p CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) t WHERE p.status NOT IN (‘sleeping’, ‘background’) AND r.session_id = p.spid
The above query will list the hosts,users who are currently accessing the DB, the cpu used by those hosts.
To know the logged in user of SQL server, login to the application server, execute ChangeDbServer.bat from bin and know the username of SQl. Using this information you can get to know the user and host which is consuming more cpu for troubeshooting performance issue.

Need More Help?

Are you looking for a personalized customer support? Submit your question or Call our Toll Free Number to speak with ServiceDesk Plus MSP Expert who can offer a better solution for your requirements.

Submit a Ticket
Email : sdpmsp@manageengine.com
Toll Free : 1-888-720-9500