Is it normal for tempdb TPS to be higher than all other databases combined? I'm investigating some performance issues on a production DB here, and I've noticed a slightly odd thing that I'm not sure is weird or not.

If I run a quick Performance Monitor trace for the SQLServer:Transactions/sec counter for each DB on the server, I'm seeing that tempdb accounts for a huge majority of the transactions - as they're transactions against 'real' tables, shouldn't there be some correlation with other database stats too?

Database  Average
-----------------
tempdb    2,127
master      115
MyDb1        63
OtherDbs      5

TOTAL     2,700


I have a query I was given to show queries active on the tempdb, and that seems to show queries that aren't temp tables etc, but 'normal' queries on other databases.

SELECT
es.host_name,
es.login_name,
es.program_name,
st.dbid as QueryExecContextDBID,
DB_NAME(st.dbid) as QueryExecContextDBNAME,
st.objectid as ModuleObjectId,
SUBSTRING
(
    st.text,
    (er.statement_start_offset / 2) + 1,
    (
        (
            CASE
                WHEN er.statement_end_offset = -1 THEN
                    LEN(CONVERT(nvarchar(max),st.text)) * 2
                ELSE
                    er.statement_end_offset
            END
        ) - (er.statement_start_offset / 2)
    )
) as Query_Text,
tsu.session_id,
tsu.request_id,
tsu.exec_context_id,
(tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) as OutStanding_user_objects_page_counts,
(tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) as OutStanding_internal_objects_page_counts,
er.start_time,
er.command,
er.open_transaction_count,
er.percent_complete,
er.estimated_completion_time,
er.cpu_time,
er.total_elapsed_time,
er.reads,
er.writes,
er.logical_reads,
er.granted_query_memory
FROM sys.dm_db_task_space_usage tsu
inner join sys.dm_exec_requests er ON (tsu.session_id = er.session_id and tsu.request_id = er.request_id)
inner join sys.dm_exec_sessions es ON (tsu.session_id = es.session_id)
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) st
-- WHERE (tsu.internal_objects_alloc_page_count+tsu.user_objects_alloc_page_count) &gt; 0
WHERE program_name &lt;&gt; 'Microsoft SQL Server Management Studio - Query'
ORDER BY
    (tsu.user_objects_alloc_page_count - tsu.user_objects_dealloc_page_count) +
        (tsu.internal_objects_alloc_page_count - tsu.internal_objects_dealloc_page_count) DESC
For poorly designed databases and queries, tempdb will often become very active. Since it is a shared resource between all databases on the instance - dont be surprised to see a high transaction count there.

As Mark also comments, snapshot isolation can have a strong effect on tempdb too.