Checking Performance Monitor, I could easily see that the SQLServer process was using the majority of the CPU:
High memory utilization by SQLServer is normal. It will use all memory allocated to it. I then used Process Monitor to look at the threads within SQL to see which one was the culprit:
From Process Monitor, I could see that the Thread ID 5252, which was taking up the most CPU, was started on 9/19/2011.
To learn more about this thread and any other running processes, I issued the following query:
select * from master..sysprocesses where status = 'runnable' order by cpu desc
From this query, I was able to find the following additional information:
SPID: 161
Login_time: 2011-09-19 06:52:02.580
Hostname: XXXXXXXXXXXX
Loginame: AppUser
Finally, I issued this query to see the actual command that was being run:
dbcc inputbuffer(161)
Here is the output:
select replace(inpc.invno + '2011' + inpc.Unit,'-',''),inpc.[invno],[itemno],[itemsub],[seq],[comment],inpc.[orderno], CAST(MONTH(inpc.createddate) AS VARCHAR(10)),'2011',CAST(MONTH(inpc.createddate) AS VARCHAR(10)) ,CAST(MONTH(inpc.createddate) AS VARCHAR(10)),inpc.[unit] from inpcbkup inpc --join inphbkup inph on inpd.orderno = inph.orderno and inpd.invno = inph.invno inner join INvoiceReference I on inpc.orderno =I.orderno and inpc.invno= I.InvoiceNo where inpc.invno not in ( select DISTINCT INvNo FROM [sql2].[OrderEntryImport].[dbo].[reprint_incm] where CALNDRYR = 2011 and INvNo is not null ) and I.status1='P' and IsNull(I.batch,'') <> '' and inpc.unit = '4111' and Year(inpc.createddate) = 2011 and INvNo is not null
Based on Application and System logs on XXXXXXXXXX, Developer1 issued the command, but is no longer logged into that server.
I then issued the following command within SSMS:
KILL 161
However, after 10 minutes the process still had not terminated. I then used Process Monitor to kill the process. This was successful, but left SQL in an unstable condition:
Processor utilization returned to normal levels:
To return SQL to proper operation, I stopped and started the SQL Server and SQL Agent process in cluster manager. After doing that, processor and memory utilization returned to normal. Response times on the Sharepoint website immediately improved.
Resources:
http://www.sql-server-performance.com/2005/sysprocesses-performance-part1
http://www.sqldbadiaries.com/2010/07/16/using-process-explorer-to-resolve-sql-server-cpu-spikes
