SQL Server 2008 R2 事務計數

下午好，我們有一個相當大的 SQL Box 32proc 128gb。

我們的環境發生了變化，不確定到底是什麼變化，db/code。我們也在調查這方面。我們從基線統計中註意到的一件事是交易數量。它從 10-20K 變為 60-80K。

SELECT name, transaction_type, COUNT(1)
FROM sys.dm_tran_active_transactions
WHERE name <> 'worktable'
GROUP BY name, transaction_type

Results:
name
implicit_transaction
INSERT
sort_init
UPDATE STATISTICS
UpdateQPStats
user_transaction
LobStorageProviderSession
SELECT
sort_fake_worktable
topn_fake_worktable
workfile
WorkFileGroup_fake_worktable

有些名稱是不言自明的，有些則不是。我無法在網上找到任何這些。有沒有人有我可以找到答案的解釋或連結？

謝謝，

檢查事務日誌文件。有沒有明顯增加。還可以通過查詢檢查完整的交易。它將為您提供有關實際執行內容的更多詳細資訊。

/* Query to check transaction count per query and session_id */  

select er.session_id, er.request_id,
er.open_transaction_count, er.transaction_id,
at.name, at.transaction_begin_time,
REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text
from     sys.dm_exec_requests er 
join     sys.dm_tran_active_transactions at
 on     er.transaction_id = at.transaction_id
join     sys.dm_exec_query_stats stat
 on     er.plan_handle = stat.plan_handle  
CROSS
APPLY    sys.dm_exec_sql_text (stat.sql_handle)
GO

/* Check task allocation by query */

SELECT t1.session_id, t1.request_id, t1.task_alloc,
t1.task_dealloc, t2.statement_start_offset, 
t2.statement_end_offset, REPLACE (REPLACE([text], char(13), ' '), CHAR(10), ' ') AS sql_text
FROM (Select session_id, request_id,
SUM(internal_objects_alloc_page_count) AS task_alloc,
SUM (internal_objects_dealloc_page_count) AS task_dealloc 
FROM sys.dm_db_task_space_usage 
GROUP BY session_id, request_id) AS t1, 
       sys.dm_exec_requests AS t2
INNER JOIN sys.dm_exec_query_stats stat ON t2.plan_handle = stat.plan_handle
CROSS 
APPLY  sys.dm_exec_sql_text (stat.sql_handle)
WHERE  t1.session_id = t2.session_id
 AND  (t1.request_id = t2.request_id)
GO

引用自：https://dba.stackexchange.com/questions/116508