It doesn't really make sense to track version store by session, or by transaction, or by query. If two different users are making use of the same version of a row/table, who owns it?
You can track this by object, though, which can help you narrow down which modules are causing the churn. Have a look at sys.dm_tran_top_version_generators
:
USE [your database];
GO
SELECT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
+ '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
vs.aggregated_record_length_in_bytes
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1);
And on SQL Server 2008+, you can also figure out which modules reference these tables by adding sys.dm_sql_referencing_entities
:
SELECT
obj = QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
+ '.' + QUOTENAME(OBJECT_NAME(p.object_id)),
referenced_by = QUOTENAME(r.referencing_schema_name)
+ '.' + QUOTENAME(r.referencing_entity_name),
vs.aggregated_record_length_in_bytes AS size
FROM sys.dm_tran_top_version_generators AS vs
INNER JOIN sys.partitions AS p
ON vs.rowset_id = p.hobt_id
CROSS APPLY sys.dm_sql_referencing_entities
(
QUOTENAME(OBJECT_SCHEMA_NAME(p.object_id))
+ '.' + QUOTENAME(OBJECT_NAME(p.object_id)), 'OBJECT'
) AS r
WHERE vs.database_id = DB_ID()
AND p.index_id IN (0,1)
ORDER BY size DESC, referenced_by;
This assumes that none of the version store could be created by ad hoc queries. However, it doesn't tell you which of those modules could be causing it - hopefully the naming scheme is logical and helps you narrow it down a bit.
(On 2005 you might be able to go through sysdepends
and other old-style dependency views but I'm not 100% sure how reliable that would be.)
MS SQL Server is an ACID compliant RDBMS. One of the critical tools involved in providing ACID compliance is a concept of a transaction log. The transaction log is where the pages modified is saved, so it can revert them and put them back in the previous state if you need to rollback a transaction. There is much to consider here but you want to ensure you size your VLFs and manage it in advance.
TempDB is a temporary area used by MS SQL Server to store objects like temp tables, join results, some caching, etc. Unless you have performance issues, you generally don't want to worry about this, don't even back it up. It get's rebuilt on startup. It is good practice to create at least 2 or maybe up to 4 physical files for TempDB so you don't get PAGELATCH contention.
Paul Randall (An authority on MS SQL Server) recommends letting your transaction log be at whatever size it grows to after a full weeks activity after reindexing. Really, unless it grows a lot, don't worry too much about it. Just make sure to avoid growths.
Best Answer
From http://www.sqlservercentral.com/scripts/tempdb/72007/
EDIT
As Martin pointed out in a comment, this would not find active transactions that are occupying space in tempdb, it will only find active queries that are currently utilizing space there (and likely culprits for current log usage). So there could be an open transaction but the actual query that causes the problem is no longer running.
You could change the
inner join
onsys.dm_exec_requests
to aleft outer join
, then you will return rows for sessions that aren't currently actively running queries.The query Martin posted...
...would identify
session_id
s with active transactions that are occupying log space, but you wouldn't necessarily be able to determine the actual query that caused the problem, since if it's not running now it won't be captured in the above query for active requests. You may be able to reactively check the most recent query usingDBCC INPUTBUFFER
but it may not tell you what you want to hear. You can outer join in a similar way to capture those actively running, e.g.:You can also use the DMV
sys.dm_db_session_space_usage
to see overall space utilization by session (but again you may not get back valid results for the query; if the query is not active, what you get back may not be the actual culprit).With all of these queries at your disposal, you should be able to narrow down who is using up tempdb and how, especially if you catch them in the act.
some tips for minimizing tempdb utilization
SORT_IN_TEMPDB
option if it isn't neededYou may also consider that your tempdb log usage may be caused by internal processes that you have little or no control over - for example database mail, event notifications, query notifications and service broker all use tempdb in some way. You can stop using these features, but if you're using them you can't dictate how and when they use tempdb.