Tempdb data files are growing fast in SQL server 2014? A few days back we have moved to 2014 from 2012 server and we kept the same settings as earlier like we are using 8 data files in Tempdb for better performance and all placed in single drive.

As this is data warehouse environment batch loads are running continuously and tempdb drive is getting full.Even though 98% free space in Database it could not able reuse space and throwing disk is full.


  Insufficient space in tempdb to hold row versions. Need to shrink the
  version store to free up some space in tempdb. Transaction
  (id=239368387 xsn=1273322 spid=126 elapsed_time=1590) has been marked
  as a victim and it will be rolled back if it accesses the version
  store. If the problem persists, the likely cause is improperly sized
  tempdb or long running transactions. Please refer to BOL on how to
  configure tempdb for versioning.


Question


Is there any specific settings for tempdb to avoid these issues in SQL Sever 2014?
Any recommendation for tempdb performance improvement like enabling trace flag -T1118 ?


In 2012 it was taking only 64 GB and now even 450 GB is not sufficient.

Server specification.

SQL Server detected 4 sockets with 15 cores per socket and 30 logical processors per socket, 120 total logical processors; using 120 logical processors based on SQL Server licensing. 512 GB RAM.
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 &lt;&gt; @@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 &lt; 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 &gt; 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 &gt; 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 &gt; 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