If SQL Server 2008 is showing 100% CPU usage then following actions should be taken to find the cause of the problem and to rectify it.
1. Verify in Task Manager—>Processes, that Sqlserver.exe is using 99% or 100% CPU activity.
2. Invoke Activity Monitor in SQL Server Management Studio to check the processes taking most of CPU time for execution. Also check if any process is in Suspended status.
3. To further dig deeper, execute the query to find blocking processes-
select * from sys.sysprocesses where blocked >0
4. If it does not show any record then try to find query which might be taking most of the CPU time. Execute sp_who2 ‘Active’
This will give details of processes which are active. If any process which is active but in Suspended state then that SPID is the culprit query. For more details on sp_who2 check the link
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/aa06e533-ef6a-494a-9f9b-df3b29b73d85
5. Execute dbcc inputbuffer(<SPID>), replace <SPID> with the SPID found in step 4. This will provide details of the query. For more details on dbcc check the link
http://msdn.microsoft.com/en-us/library/aa258826%28v=sql.80%29.aspx
6. To find the exact query, execute the following query to find the query and the time when it was started. [replace <SPID> from step 4].
SELECT ltrim(rtrim(SQLTEXT.TEXT)), req.start_time, req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS SQLTEXT
WHERE SESSION_ID = <spid>
7. Once the query is confirmed then this process can be killed to stop the execution of the query.
Kill <SPID>
Check the CPU usage will immediately come down to normal. There are many different reasons for 100% CPU Usage. The one explained above is one of the case where query is taking lot of CPU time.