Before troubleshooting TempDB space, it is good to know what objects are using TempDB.
Even though you have issues only with Version Store, knowing what objects are using TempDB will give you a good idea on where to start.
Remember, TempDB is a global resource for all databases, so this will be used for all databases. At a high level these are the objects that can use TempDB:
1. User objects
- User-defined tables and indexes
- System tables and indexes
- Global temporary tables and indexes
- Local temporary tables and indexes
- Table variables
- Tables returned in table-valued functions
2. Internal objects
These are internal objects created by SQL Server for Work Tables, Hash Joins, sorts...
3. Version Store
- Triggers
- MARS
- Online index
- Row version-based isolation levels: requires setting at the database level
Further Cursors, Database Mail, DBCC CHECKDB can also use TempDB. See this article for more details Capacity Planning for tempdb
Now that we know how TempDB is used, we can track TempDB space utilization using DMVS provided by SQL Server..
To Track user objects which are consuming the most space:
space used by user objects:(Active Only)
WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE session_id <> @@SPID
GROUP BY session_id, request_id
)
SELECT TSU.session_id,
TSU.alloc_pages * 1.0 / 128 AS [internal object MB space],
TSU.dealloc_pages * 1.0 / 128 AS [internal object dealloc MB space],
EST.text,
-- Extract statement from sql text
ISNULL(
NULLIF(
SUBSTRING(
EST.text,
ERQ.statement_start_offset / 2,
CASE WHEN ERQ.statement_end_offset < ERQ.statement_start_offset
THEN 0
ELSE( ERQ.statement_end_offset - ERQ.statement_start_offset ) / 2 END
), ''
), EST.text
) AS [statement text],
EQP.query_plan
FROM task_space_usage AS TSU
INNER JOIN sys.dm_exec_requests ERQ WITH (NOLOCK)
ON TSU.session_id = ERQ.session_id
AND TSU.request_id = ERQ.request_id
OUTER APPLY sys.dm_exec_sql_text(ERQ.sql_handle) AS EST
OUTER APPLY sys.dm_exec_query_plan(ERQ.plan_handle) AS EQP
WHERE EST.text IS NOT NULL OR EQP.query_plan IS NOT NULL
ORDER BY 3 DESC;
Space used by Internal Objects((Active Only)):
SELECT
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t2.plan_handle
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
WHERE t1.session_id = t2.session_id AND
(t1.request_id = t2.request_id)
ORDER BY t1.task_alloc DESC
Version Store:
You can find the list of transactions which are using version store. To see how much space they consume, refer to article at the end
SELECT top 2
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
Space used by transaction will be cleaned automatically when the transaction has completed. If the transaction is open for long time, it will prevent Version Store cleanup and you may have to check why, by joining with sys.dm_exec_sessions or sys.sysprocesses
You also can automate this space usage collection for later analysis.
This is just to give you idea, but it may not work in your version as some columns might have changed.
Query to gather details:
CREATE PROC sp_sampleTempDbSpaceUsage AS
Instance level tempdb FILE space usage FOR ALL files WITHIN
-- tempdb
INSERT tempdb_space_usage (
scope,
Instance_unallocated_extent_pages,
version_store_pages,
Instance_userobj_alloc_pages,
Instance_internalobj_alloc_pages,
Instance_mixed_extent_alloc_pages)
SELECT
'instance',
SUM(unallocated_extent_page_count),
SUM(version_store_reserved_page_count),
SUM(user_object_reserved_page_count),
SUM(internal_object_reserved_page_count),
SUM(mixed_extent_page_count)
FROM sys.dm_db_file_space_usage
-- 2. tempdb space usage per session
--
INSERT tempdb_space_usage (
scope,
session_id,
Sess_task_userobj_alloc_pages,
Sess_task_userobj_deallocated_pages,
Sess_task_internalobj_alloc_pages,
Sess_task_internalobj_deallocated_pages)
SELECT
'session',
session_id,
user_objects_alloc_page_count,
user_objects_dealloc_page_count,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id > 50
-- 3. tempdb space usage per active task
--
INSERT tempdb_space_usage (
scope,
session_id,
Sess_task_userobj_alloc_pages,
Sess_task_userobj_deallocated_pages,
Sess_task_internalobj_alloc_pages,
Sess_task_internalobj_deallocated_pages,
query_text)
SELECT
'task',
R1.session_id,
R1.user_objects_alloc_page_count,
R1.user_objects_dealloc_page_count,
R1.internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count,
R3.text
FROM sys.dm_db_task_space_usage AS R1
LEFT OUTER JOIN
sys.dm_exec_requests AS R2
ON R1.session_id = R2.session_id
OUTER APPLY sys.dm_exec_sql_text(R2.sql_handle) AS R3
WHERE R1.session_id > 50
Once you have gathered enough data, you can analyze using below queries:
Query 1: This query reports the maximum allocated space in TempDB over all the data points collected
SELECT
CONVERT (float, (MAX(version_store_pages +
Instance_userobj_alloc_pages +
Instance_internalobj_alloc_pages +
Instance_mixed_extent_alloc_pages)))/ 128.0
AS max_tempdb_allocation_MB
FROM tempdb_space_usage
WHERE scope = 'instance'
Query 2: This query computes the maximum allocated pages and the version store size in megabytes over all the data points collected. If the amount of TempDB space that is allocated to the version store is large, it implies that long-running transactions are generating or consuming versions.
SELECT
MAX(version_store_pages) AS max_version_store_pages_allocated,
MAX(version_store_pages/128.0) AS max_version_store_allocated_space_MB
FROM tempdb_space_usage
WHERE scope = 'instance'
Query 3: This query shows the top five queries that are allocating the most pages for internal objects.
SELECT top 5 MAX ((Sess_task_internalobj_alloc_pages) - (Sess_task_internalobj_deallocated_pages))
AS Max_Sess_task_allocated_pages_delta, query_text
FROM tempdb_space_usage
WHERE scope = 'task' and session_id > 50
GROUP BY query_text
ORDER BY Max_Sess_task_allocated_pages_delta DESC
Below are the references i used which can help you further:
- https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage/
- How to identify which query is filling up the tempdb transaction log?
- https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
- https://technet.microsoft.com/en-us/library/cc966545.aspx
Below is must read:
- https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
- https://technet.microsoft.com/en-us/library/cc966545.aspx
Best Answer
Firstly as @jonhg points out the servers can give you different execution plans as the tables and indexes are rebuilt. The server hardware (memory, cpu, hard drives) can be different and finally you don't have the same version of SQL server in both cases. Any of these would be sufficient to give different results.
Other queries could be interfering, as the cache on the servers can be different. All the answers to this are found here: http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf