To know the Which Database use more transactions like insert/update/delete:
Script 1:
WITH trans
AS (SELECT
DB_NAME(dm_tran_database_transactions.database_id) AS DB
,COUNT(*) AS NumberOfTrans
FROM sys.dm_tran_database_transactions
GROUP BY DB_NAME(dm_tran_database_transactions.database_id))
SELECT TOP 10
trans.DB
,trans.NumberOfTrans
FROM
trans
ORDER BY trans.NumberOfTrans DESC
Script 2:
SELECT TOP 10
dm_os_performance_counters.object_name
,dm_os_performance_counters.counter_name
,dm_os_performance_counters.instance_name
,dm_os_performance_counters.cntr_value
,dm_os_performance_counters.cntr_type
FROM
sys.dm_os_performance_counters
WHERE
counter_name = 'Transactions/sec'
AND dm_os_performance_counters.instance_name <> N'_Total'
ORDER BY
dm_os_performance_counters.cntr_value DESC
Script 3:
SELECT transaction_id, DB_NAME(database_id) DatabaseName,
database_transaction_begin_time TransactionBegin,
CASE database_transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read only'
WHEN 3 THEN 'System' END AS TransactionType,
CASE database_transaction_state
WHEN 1 THEN 'Not Initialized'
WHEN 3 THEN 'Transaction No Log'
WHEN 4 THEN 'Transaction with Log'
WHEN 5 THEN 'Transaction Prepared'
WHEN 10 THEN 'Commited'
WHEN 11 THEN 'Rolled Back'
WHEN 12 THEN 'Commited and Log Generated' END AS TransactionState,
database_transaction_log_record_count LogRecordCount,
database_transaction_log_bytes_used LogBytesUsed,
database_transaction_log_bytes_reserved LogBytesReserved
FROM sys.dm_tran_database_transactions
--WHERE transaction_id > 1000
Script 1:
WITH trans
AS (SELECT
DB_NAME(dm_tran_database_transactions.database_id) AS DB
,COUNT(*) AS NumberOfTrans
FROM sys.dm_tran_database_transactions
GROUP BY DB_NAME(dm_tran_database_transactions.database_id))
SELECT TOP 10
trans.DB
,trans.NumberOfTrans
FROM
trans
ORDER BY trans.NumberOfTrans DESC
Script 2:
SELECT TOP 10
dm_os_performance_counters.object_name
,dm_os_performance_counters.counter_name
,dm_os_performance_counters.instance_name
,dm_os_performance_counters.cntr_value
,dm_os_performance_counters.cntr_type
FROM
sys.dm_os_performance_counters
WHERE
counter_name = 'Transactions/sec'
AND dm_os_performance_counters.instance_name <> N'_Total'
ORDER BY
dm_os_performance_counters.cntr_value DESC
Script 3:
SELECT transaction_id, DB_NAME(database_id) DatabaseName,
database_transaction_begin_time TransactionBegin,
CASE database_transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read only'
WHEN 3 THEN 'System' END AS TransactionType,
CASE database_transaction_state
WHEN 1 THEN 'Not Initialized'
WHEN 3 THEN 'Transaction No Log'
WHEN 4 THEN 'Transaction with Log'
WHEN 5 THEN 'Transaction Prepared'
WHEN 10 THEN 'Commited'
WHEN 11 THEN 'Rolled Back'
WHEN 12 THEN 'Commited and Log Generated' END AS TransactionState,
database_transaction_log_record_count LogRecordCount,
database_transaction_log_bytes_used LogBytesUsed,
database_transaction_log_bytes_reserved LogBytesReserved
FROM sys.dm_tran_database_transactions
--WHERE transaction_id > 1000
No comments:
Post a Comment