Identifying log running/cpu intensive SQL query using Performance Monitor

Identifying log running/cpu intensive SQL query using Performance Monitor

This article describe how to identify a log running sql script using Performance Monitor tool
1. Launch the System Performance tool from, Administrative tools-> Performance
2. Select Thread from Performance Object drop down box
3. Select % Processor time and ID Thread from counter list box
4. Select all sqlsrv instance from instance list and click add button
5. Change the view style to report form

You can identify the long running/cpu intensive SQL Server thread by watching the % process time column.
To get the SQL statement that it currently executing use

DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE kpid = 1000--(replace it with the ID Thread value)
SELECT * FROM ::fn_get_sql(@Handle)