Sunday, May 5, 2013

[MS SQL Server] High CPU Issue

[MS SQL Server] High CPU Issue

High CPU Issue

Posted: 04 May 2013 02:34 PM PDT

Hi,We have a high CPU issue, it's almost 80 - 90% and sometimes close to 100%.I will be trying to Rebuild the indexes as lots of indexes having fragmentation issue, I will be also updating the statistics and Recompiling the objects to improve Stored Procedure.Do you suggest anything which can help to identify the CPU issue and resolve it?Thanks,

HOW TO FIND the OFFENDING SESSION FROM sys.dm_tran_database transaction

Posted: 04 May 2013 07:31 AM PDT

Investigating on the use of tempdb with the following very simple query:select convert(char(15), db_name(database_id)) as dbname ,transaction_id ,database_transaction_begin_time ,database_transaction_log_bytes_used from sys.dm_tran_database_transactions where database_transaction_log_bytes_used > 0[/font]dbname transaction_id database_transaction_begin_time database_transaction_log_bytes_used--------------- -------------------- ------------------------------- -----------------------------------tempdb 4078898703 2013-05-04 17:41:57.570 149539028I can't find the owner session of transaction #4078898703But with the following query:SELECT session_id ,connect_time ,last_read ,substring(text,1, 18)FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text (most_recent_sql_handle)WHERE last_read between '20130504 17:40:00' and '20130504 17:42:00' 87 2013-05-04 17:41:56.170 2013-05-04 17:41:56.207 CREATE Procedure ...The offending session is #87 (probably)Why I can't find a reference to transaction #4078898703 in ALL the DMV?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog