MS DTC and sys.dm_tran_active_transactions

A  sql reindex command was being blocked by two active transactions in MS DTC.   Use

sys.dm_tran_active_transactions to report more detail on transactions

 
 

 

select * from sys.dm_tran_active_transactions where transaction_uow  = '922D5B60-A5CA-4C96-8891-703021B40AD8'

/*Returns: 

Transaction_id = “1770489134”	
Name = “DTCXact”
Transaction_begin_time =”2011-10-29 11:01:39.517”
Transaction_type = “4”
Transaction_uow=”922D5B60-A5CA-4C96-8891-703021B40AD8”
Transaction_state=”2”
Transaction_status=”12”
Transaction_status2 =”451
Dtc_state=”0”
Dtc_status=”0”
Dtc_isolation_level= “1048576”
*/

 

SQL Server Books Online has full details only all columns and values returned > For analysis purposes , I’ll highlight a few:

 Transaction_type = “4”  means it’s a distributed transaction

Transaction_state =”2”  means the transaction is active

 As a follow up I execute:

select * from sys.dm_tran_locks  where request_owner_guid = '922D5B60-A5CA-4C96-8891-703021B40AD8'

 This returns transactions  related to this owner guid.

If you see -2 in the request_session_id column , investigate further for orphaned distributed transactions.

Related Posts

SQL Server detected a DTC/KTM in-doubt transaction with UOW

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Monitoring a Rollback and sys.dm_exec_requests

The dynamic management view (DMV) sys.dm_exec_requests returns information about each request that is executing within SQL Server.

Instead of using Activity Monitor to view the status of a Rollback , use this T-SQL code. 

Activity monitor is based on window panes and is cumbersome to export any information.

Using  t-sql allows extra flexibility in reporting.

 

select 
der.session_id, 
der.command, 
der.status, 
der.percent_complete
from sys.dm_exec_requests as der
where command IN ('killed/rollback',’rollback’)

Author: Jack Vamvas (http://www.sqlserver-dba.com)

sys.dm_os_loaded_modules

sys.dm_os_loaded_modules  is a SQL Server OS Dynamic Management View (DMV).

When executing the t-sql statement below – a recordset is returned . I’ve included some output further down the page :

select file_version,product_version,company,[description] from sys.dm_os_loaded_modules  

 

I find this dmv useful when troubleshooting corruption .

An example , is to  check if there is any anti-virus module which is getting loaded in the SQL memory area. Normally , I’m looking for non-Microsoft Corporation modules .

Returns a row for each module loaded into the server address space.

 

2005.90:4340.0	9.0:4340.0	Microsoft Corporation	SQL Server Windows NT
5.2:3790.4789	5.2:3790.4789	Microsoft Corporation	NT Layer DLL
5.2:3790.4480	5.2:3790.4480	Microsoft Corporation	Windows NT BASE API Client DLL
8.0:50727.3053	8.0:50727.3053	Microsoft Corporation	Microsoft® C Runtime Library
7.0:3790.3959	6.1:8638.3959	Microsoft Corporation	Windows NT CRT DLL
8.0:50727.3053	8.0:50727.3053	Microsoft Corporation	Microsoft® C++ Runtime Library
5.2:3790.4455	5.2:3790.4455	Microsoft Corporation	Advanced Windows 32 Base API
5.2:3790.4759	5.2:3790.4759	Microsoft Corporation	Remote Procedure Call Runtime
5.2:3790.4530	5.2:3790.4530	Microsoft Corporation	Security Support Provider Interface
2005.90:4035.0	9.0:4035.0	Microsoft Corporation	SQLOS Hosting DLL

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server sql_handle returns sql statement

Certain Dynamic Management Views(DMV)  return a sql_handle column . 

Examples of DMVs returning the sql_handle column are :

sys.dm_exec_query_stats

sys.dm_exec_requests

 sql_handle is a varbinary(64)  . A varbinary is suitable  for variable length binary data.  The sql_handle is a token referencing a stored procedure or batch.

The table-valued function sys.dm_exec_sql_text returns the text of the sql_handle. It has a parameter : @handle  (varbinary(64))

 

--returns sql query text referencing aggregate performance statistics

-- on sys.dm_exec_query_stats.

select sql_handle,(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,

      ( (CASE WHEN statement_end_offset = -1

         THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)

         ELSE statement_end_offset END)  - statement_start_offset) / 2+1))  AS sql_statement from sys.dm_exec_query_stats

CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 

Author: Jack Vamvas (http://www.sqlserver-dba.com)

Top 5 SQL Server DMV for Index Analysis

SQL Server DMV  and functionsare one of my favourite SQL Server  features when  tuning SQL Server Index performance.

 

I use the following 5 SQL Server DMVs for general overview

 

 

1. sys.dm_db_index_usage_stats

Maintains counts for the range of index activity and the last performed time. Also displays statistics ob how an index is used against a query.

 

select object_schema_name(indexes.object_id) + '.' + object_name(indexes.object_id) as objectName,

indexes.type_desc,ius.user_seeks, ius.user_scans,ius.user_lookups, ius.user_updates from sys.indexes

left outer join sys.dm_db_index_usage_stats ius

                        on indexes.object_id = ius.object_id

                             and indexes.index_id = ius.index_id

                             and ius.database_id = db_id()

 

 

2. sys.dm_db_missing_index_details 

Returns detailed information about each missing index on a table. Information is lost at SQL Server recycle.

 SELECT * FROM sys.dm_db_missing_index_details

 

3. sys.dm_db_missing_index_columns

 

 

4. sys.dm_exec_query_stats

Performance statistics for cached plans. The information is only available while the plan remains in the cache.

 select qs.sql_handle, qs.execution_count

     , qs.total_elapsed_time, qs.last_elapsed_time

     , qs.min_elapsed_time, qs.max_elapsed_time

     , qs.total_clr_time, qs.last_clr_time

     , qs.min_clr_time, qs.max_clr_time

  from sys.dm_exec_query_stats as qs

 

5.sys.dm_db_index_operational_stats

Returning IO , locking , latching and access activity. Useful for identifying index hotspots , waits for read\writes to a table. Will give information about insert,update, and delete

 SELECT OBJECT_NAME(A.[OBJECT_ID]) AS [OBJECT NAME],
       I.[NAME] AS [INDEX NAME],
       A.LEAF_INSERT_COUNT,
       A.LEAF_UPDATE_COUNT,
       A.LEAF_DELETE_COUNT
FROM   SYS.DM_DB_INDEX_OPERATIONAL_STATS (NULL,NULL,NULL,NULL ) A
       INNER JOIN SYS.INDEXES AS I
         ON I.[OBJECT_ID] = A.[OBJECT_ID]
            AND I.INDEX_ID = A.INDEX_ID
WHERE  OBJECTPROPERTY(A.[OBJECT_ID],'IsUserTable') = 1

 
To assist in long term SQL Server index analysis record the output of the SQL Server DMVs , output the recordsets to a data depository . As the data is lost when SQL Server restarts

Returns database columns missing indexes. Information is lost at SQL Server recycle.

select * from sys.dm_db_missing_index_columns(1)

Normally, I use them as a general approach and then drill into a specific SQL Server performance problem.

Source:Jack Vamvas (http://www.sqlserver-dba.com)