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.