3

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

  1. Is there any specific settings for tempdb to avoid these issues in SQL Sever 2014?

  2. 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.

3

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:

  1. https://blogs.msdn.microsoft.com/sqlserverfaq/2010/10/13/troubleshooting-tempdb-growth-due-to-version-store-usage/
  2. How to identify which query is filling up the tempdb transaction log?
  3. https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
  4. https://technet.microsoft.com/en-us/library/cc966545.aspx

Below is must read:

  1. https://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx
  2. https://technet.microsoft.com/en-us/library/cc966545.aspx
  • Remember that space used by user objects is instead "space used by active sessions", so nothing will turn up if no active session is using tempdb (I wrote that query around five years ago) – spaghettidba Jul 14 '16 at 9:02
  • Thats Right ,i will update that.. – TheGameiswar Jul 14 '16 at 9:03
0

One possibility: Between 2012 and 2014 some significant changes were made to the cardinality estimation routines. While these changes create massive improvements for some complex queries there are a few circumstances where it has a detrimental effect - this could cause the query planner to pick a plan that is less efficient than that picked by the older routines. These bad plans could cause a lot of spooling to disk that did not previously happen and this could cause tempdb to see much more activity.

If your databases are using the 2014 compatabilit level (you can check this using SELECT compatibility_level FROM sys.databases WHERE name = '<database_name>';, the value returned will be 120 for 2014 and 110 for 2012) you could try turning it back down to the 2012 level (110) temporarily to see if this alleviates the immediate issue. If it does then you need to investigate which queries are causing the problem. You may be able to refactor them to cooperate better with the new estimator. You can force the old estimator to be used even in 2014 compatibility level by specifying WITH OPTION(QUERYTRACEON 9481), but keeping the old estimator in place (globally by setting CL=110 or locally with the querytrace option) is not a recommended long-term solution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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