2

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) > 0
WHERE program_name <> '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
1
  • 1
    Either of the snapshot isolation levels enabled/used? Nov 21, 2012 at 19:28

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.