TempDB running out of space

First of all need to check, if any stored procedure (or nested) are running in Transaction. If transaction is very long means there are lot of things are happening between Begin Tran and Commit Tran, then it will keep on consuming tempdb.

https://www.mssqltips.com/sqlservertip/3276/sql-server-alert-for-tempdb-growing-out-of-control/

SQL Server Alert for Tempdb Growing Out of Control

By: Erin Cook   |   Read Comments (7)   |   Related Tips: More > System Databases

Problem

The system database, Tempdb, can appear to have a life of its own. The primary purpose of this database is to temporarily store data for specific processes before being committed to a database. When a Tempdb grows out-of-control it can be a daunting task trying to figure out what is causing the growth. This can be especially problematic when there is little disk space left to work with. One way to stop Tempdb from growing is to restart the SQL services, but one loses all data that might be beneficial in knowing what caused the problem in the first place and who can afford any downtime?  How can I get insight into TempDB growth and get notified if there is an issue?

Solution

One option to get notified when TempDB grows is to create a SQL Alert to fire a SQL Agent Job that will automatically send an email alerting the DBA when the Tempdb reaches a specific file size. Let's look at setting that up and seeing the benefit.

Create the Alert and Job

In SQL Server Management Studio (SSMS), under the SQL Server Agent node, right-click and select New Alert:

• On the General Tab, Enter Name: SQL Alert - tempdb Above 3GB (use a value that is conducive to your own environment here)

• Select Type: SQL Server performance condition alert

• Under Performance condition alert definition: Select Object: SQLServer:Databases

• Select Counter: Data File(s) Size (KB)

• Select Instance: tempdb

• Under Alert if counter, Select: rises above

• Enter Value: For this sample, I am using 3GB, or 3145728KB, but use a value that fits your needs.

Next, go to the Response tab on the left-hand side of the window. Select the checkbox Execute job and click on the New Job...button:

A New Job window will appear:

• On the General Tab, Enter Name: DBA - tempdb Above 3GB (use a value that is conducive to your own environment here)

• Enter Owner

• Make sure the Enabled checkbox is checked.

Next, go to the Steps tab on the left-hand side of the window:

• On the General Tab, Enter Step Name: Capture Queries and Send Database Mail
• Select Type: Transact-SQL script (T-SQL)

• Select Database: tempdb

• Enter Command - Copy and paste your edited sample text below into the command text field

• Make sure you edit the variables @recipients, @copy_recipients (if applicable), @subject, and @profile_name to be those that suit your environment's Database Mail profile

• Once completed with your edits of the code sample below, click the OK button

Lastly, finish setting up the SQL Alert by going to the Response tab and selecting the newly created SQL Agent Job from the dropdown list and click the OK button:

Below is a sample email results after alert has been triggered, with instructions on how to KILL the session that is growing the Tempdb database:

Code for finding query growing tempdb and emailing it

DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)

SET @xml =CAST((

  SELECT TOP 5 --Change number accordingly

  su.Session_ID AS 'td','',

  ss.Login_Name AS 'td','', 

  rq.Command AS 'td','',

  su.Task_Alloc AS 'td','',

  su.Task_Dealloc AS 'td','',

 --Find Offending Query Text:

  (SELECT SUBSTRING(text, rq.statement_start_offset/2 + 1,

   (CASE WHEN statement_end_offset = -1 

         THEN LEN(CONVERT(nvarchar(max),text)) * 2 

         ELSE statement_end_offset 

   END - rq.statement_start_offset)/2)

  FROM sys.dm_exec_sql_text(sql_handle)) AS 'td'

  FROM      

  (SELECT su.session_id, su.request_id,

   SUM(su.internal_objects_alloc_page_count + su.user_objects_alloc_page_count) AS Task_Alloc,

   SUM(su.internal_objects_dealloc_page_count + su.user_objects_dealloc_page_count) AS Task_Dealloc

  FROM sys.dm_db_task_space_usage AS su

  GROUP BY session_id, request_id) AS su, 

   sys.dm_exec_sessions AS ss, 

   sys.dm_exec_requests AS rq

  WHERE su.session_id = rq.session_id 

   AND(su.request_id = rq.request_id) 

   AND (ss.session_id = su.session_id)

   AND su.session_id > 50  --sessions 50 and below are system sessions and should not be killed

   AND su.session_id <> (SELECT @@SPID) --Eliminates current user session from results

  ORDER BY su.task_alloc DESC  --The largest "Task Allocation/Deallocation" is probably the query that is causing the db growth

FOR XML PATH ('tr'), ELEMENTS ) AS NVARCHAR(MAX))

--BODY OF EMAIL - Edit for your environment

SET @body ='<html><H1>Tempdb Large Query</H1>

<body bgcolor=white>The query below with the <u>highest task allocation 

and high task deallocation</u> is most likely growing the tempdb. NOTE: Please <b>do not kill system tasks</b> 

that may be showing up in the table below.

<U>Only kill the query that is being run by a user and has the highest task allocation/deallocation.</U><BR> 

<BR>

To stop the query from running, do the following:<BR>

<BR>

1. Open <b>SQL Server Management Studio</b><BR>

2. <b>Connect to database engine using Windows Authentication</b><BR>

3. Click on <b>"New Query"</b><BR>

4. Type <b>KILL [type session_id number from table below];</b> - It should look something like this:  KILL 537; <BR>

5. Hit the <b>F5</b> button to run the query<BR>

<BR>

This should kill the session/query that is growing the large query.  It will also kick the individual out of the application.<BR>

You have just stopped the growth of the tempdb, without having to restart SQL Services, and have the large-running query available for your review.

<BR>

<BR>

<table border = 2><tr><th>Session_ID</th><th>Login_Name</th><th>Command</th><th>Task_Alloc</th><th>Task_Dealloc</th><th>Query_Text</th></tr>' 

SET @body = @body + @xml +'</table></body></html>'

--Send email to recipients:

EXEC msdb.dbo.sp_send_dbmail

@recipients =N'dba@domain.com', --Insert the TO: email Address here

@copy_recipients ='dba_Manager@domain.com', --Insert the CC: Address here; If multiple addresses, separate them by a comma (,)

@body = @body,@body_format ='HTML',

@importance ='High',

@subject ='THIS IS A TEST', --Provide a subject for the email

@profile_name = 'DatabaseMailProfile' --Database Mail profile here

http://dba.stackexchange.com/questions/19231/tempdb-is-getting-full-very-quickly-in-microsoft-sql-server-2008

use tempdb

go;

SELECT sum(unallocated_extent_page_count) [Free_Pages],

 (sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]

FROM sys.dm_db_file_space_usage;

Depending on what your tool does when generating reports - creates lot of tempdb tables, spills data to tempdb when using sort operators, etc, I would suggest you to presize your tempdb.

You are better off optimizing the SQL that is generating the report. May be putting proper index/s will definitely help.

Its always a best practice to only query the data that you require.

Start by analyzing wait stats.

·         If you are seeing lots of PAGELATCH waits, then add more tempdb data files. make sure to keep the files equally sized as SQL Server uses proportional fill algorithm to fill up the data files.

·         Enable Trace Flags 1117 (GROW ALL FILES IN A FILEGROUP EQUALLY) and 1118 (FULL EXTENTS ONLY).

You can use sp_whoisactive to analyze whats going on your server instance.

Below query will help you to identify the sessions that use the tempdb heavily :

/*

Lists the TempDB usage per each active session.

It helps identifying the sessions that use the tempdb heavily with internal objects.

When the internal objects usage is high, the session is probably using big hash tables or spooling in worktables. It could be a symptom of an inefficient plan or a missing index.

Shrinking a TempDB full of internal objects will probably have no effect, because the engine will not release the deallocated space.

Ref: http://dba.stackexchange.com/a/19871/8783

*/

;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

--- Changed from inner join to left outer join to return rows for sessions that aren't currently actively running queries.

left outer 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, 5 DESC

Use it carefully on a production server !!

The only possible alternative to restarting the service, is running

DBCC FREESYSTEMCACHE('ALL')-- that will clear all cached objects, including not only internal objects, but also cached query plans.

DBCC FREESYSTEMCACHE ('tempdb') -- clears cache for tempdb

DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables

http://searchsqlserver.techtarget.com/tip/Examining-data-files-when-SQL-Server-tempdb-is-full

How can I narrow down the source?

First, let's take a look at how to determine what takes up the most space, which server process ID number (SPID) we are dealing with and which host the request comes from. The query below will return the top 1000 SPIDs that are taking up space in the database. Keep in mind that the values returned are page counts. To make it easier, I included the calculations to come up with the memory values (in megabytes). Also, note that these counters are cumulative over the life of the SPID:

Shining a light on SQL Server storage tactics

SELECT top 1000
s.host_name, su.[session_id], d.name [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
[Usr_DeAlloc_MB],
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
[Int_DeAlloc_MB]
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_alloc_page_count > 0 or
su.internal_objects_alloc_page_count > 0)
order by case when su.user_objects_alloc_page_count > su.internal_objects_alloc_page_count then
su.user_objects_alloc_page_count elsesu.internal_objects_alloc_page_count end desc

The next query is similar; it returns the top 1000 items in which SPIDs have de-allocated the most space. This query can be used to trace a process that loops, creates objects as it goes, or creates and deletes many temporary objects as it runs:

SELECT top 1000 s.host_name, su.[session_id], d.name [DBName], su.[database_id],
su.[user_objects_alloc_page_count] [Usr_Pg_Alloc], su.[user_objects_dealloc_page_count] [Usr_Pg_DeAlloc],
su.[internal_objects_alloc_page_count] [Int_Pg_Alloc], su.[internal_objects_dealloc_page_count] [Int_Pg_DeAlloc],
(su.[user_objects_alloc_page_count]*1.0/128) [Usr_Alloc_MB], (su.[user_objects_dealloc_page_count]*1.0/128)
[Usr_DeAlloc_MB],
(su.[internal_objects_alloc_page_count]*1.0/128) [Int_Alloc_MB], (su.[internal_objects_dealloc_page_count]*1.0/128)
[Int_DeAlloc_MB]
FROM [sys].[dm_db_session_space_usage] su
inner join sys.databases d on su.database_id = d.database_id
inner join sys.dm_exec_sessions s on su.session_id = s.session_id
where (su.user_objects_dealloc_page_count > 0 or
su.internal_objects_dealloc_page_count > 0)
order by case when su.user_objects_dealloc_page_count > su.internal_objects_dealloc_page_count then
su.user_objects_dealloc_page_count elsesu.internal_objects_dealloc_page_count end desc

Since the tempdb does not report its size properly after it has shrunk, the following query will get you the available space in tempdb:

SELECT sum(unallocated_extent_page_count) [Free_Pages],
(sum(unallocated_extent_page_count)*1.0/128) [Free_Space_MB]
FROM sys.dm_db_file_space_usage

Once you have determined the SPID, you can determine what T-SQL is running with dbcc inputbuffer(SPID).

Let's presume you know the T-SQL code that ran, but you also need to know the temporary table(s) involved. You could run the following:

select * from tempdb.sys.objects where type = 'u'

Temporary tables originating from users in T-SQL should have the format of #YourDefinedTblName____UniqueID. This will help you identify the code involved. You could also join the SPID involved from the sys.dm_exec_requests command and use sys.dm_exec_sql_text(SQL_Handle) to get the line running at the time, but this requires a "polling loop" to monitor when the script is actually running.

Conclusion

With existing system tables and views, it can be difficult to narrow down the issue without advance preparation. The source of a full tempdb can be as simple as a single SPID or as complex as a combination of many sessions, but the processes that I've outlined above should help you narrow down which processes or combinations are causing problems.

https://thesqldude.com/2012/05/15/monitoring-tempdb-space-usage-and-scripts-for-finding-queries-which-are-using-excessive-tempdb-space/

Monitoring tempdb space usage and scripts for finding queries which are using excessive tempdb space

Posted by Sudarshan Narasimhan on May 15, 2012

21 Votes

Many times during the life of a DBA, you might notice the tempdb database growing excessively, though no changes have recently been done. It’s often the case that due to data increase, the application T-SQL queries are not written to scale up, hence end up doing excessive sorting/hashing operations which consume space from your tempdb database. Here are some T-SQL scripts that you can use to monitor who/what is consuming space from tempdb and plan accordingly.

Before we get into identifying queries that use tempdb, it is very important to understand what all activities in SQL Server (both internal & user activities), which use the tempdb database. Broadly you can classify these into 3 categories:-

1.     Internal Objects

2.     Version Stores

3.     User Objects

From a feature perspective, here are the features in SQL Server that use space from tempdb.

1.     Query

2.     Triggers

3.     Snapshot isolation and read committed snapshot (RCSI)

4.     MARS

5.     Online index creation

6.     Temporary tables, table variables, and table-valued functions

7.     DBCC CHECK

8.     LOB parameters

9.     Cursors

10.   Service Broker and event notification

11.   XML and LOB variables

12.   Query notifications

13.   Database mail

14.   Index creation

15.   User-defined functions

From a query performance standpoint, here are some operators that use tempdb space.

1. Sort Operator : The sort operator needs tempdb space to sort the full rowset of incoming rows. This is usually send when user ODER BY and also for DISTINCT ORDER BY

        

2. Hash Match Operator: Depending on the size of row, a hash table could use tempdb

      

3. Spool Operator: This operator is used to save the intermediate set of rows for re-use and uses the tempdb database to save the query result set.

         

Tempdb out of space error

Error: 1105, Severity 17, State 2 
Could not allocate space for object dbo.TBL1  in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full.

Identify which type of tempdb objects are consuming  space

The following query helps you understand if user objects or version store or internal objects are the ones using the space in tempdb. According to this output, you can focus on the below sections.

SELECT

SUM (user_object_reserved_page_count)*8 as user_obj_kb,

SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,

SUM (version_store_reserved_page_count)*8  as version_store_kb,

SUM (unallocated_extent_page_count)*8 as freespace_kb,

SUM (mixed_extent_page_count)*8 as mixedextent_kb

FROM sys.dm_db_file_space_usage

If user_obj_kb is the highest consumer, then you that objects are being created by user queries like local or global temp tables or table variables. Also don’t forget to check if there are any permanent 

tables created in TempDB. Very rare, but I’ve seen this happening.

If version_store_kb is the highest consumer, then it means that the version store is growing faster than the clean up. Most likely there are long running transactions or open transaction (Sleeping state), 

which are preventing the cleanup and hence not release tempdb space back.

Query that identifies the currently active T-SQL query, it’s text and the Application that is consuming a lot of tempdb space

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

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

Tempdb and the Version Store

The version stored (SQL 2005 onwards) is a collection of objects that are used when Snapshot Isolation or Read-Committed Snapshot Isolation (RCSI) or online index rebuild etc. are used in a database.

Version store contains the committed rows which is how a SELECT operation does not get blocked when another UPDATE/DELETE is operating on the same row, because the SELECT reads the row from the version store, instead of the actual base table. When you enable this, the row has to be stored somewhere and tempdb happens to be the place. A row is maintained in the version store when there are transactions operating on that row in questions. When the transaction is committed, the row is cleaned up from the version store tables.

You can check the version store using the DMV sys.dm_tran_version_store

At times, when there are long running transactions or orphaned transactions, you might notice tempdb growth due to the version store.

You can use the following query to find the oldest transactions that are active and using row versioning.

SELECT top 5 a.session_id, a.transaction_id, a.transaction_sequence_num, a.elapsed_time_seconds,

b.program_name, b.open_tran, b.status

FROM sys.dm_tran_active_snapshot_database_transactions a

join sys.sysprocesses b

on a.session_id = b.spid

ORDER BY elapsed_time_seconds DESC

Trace Flag 1118

This trace flag is available starting with SQL 2000 SP3 to reduce tempdb contention by forcing uniform extent allocations as opposed to mixed extent allocations. This trace flag is only to be used if you seeing contention (wait_Stats) on the PFS/GAM pages like 2:1:1 etc.. More internal details on this trace flag is available in Paul Randal’s blog post here.

Not only does enabling the trace flag help but you need to create multiple tempdb files equal to the number of logical processors. So if you have 4 CPU’s you will create 4 tempdb data files. Now, what if you have 16 or 32 processors, do you still need to create that many tempdb files?

The answer is NO, you don’t have to. The above recommendation has been stated in many KB articles like http://support.microsoft.com/default.aspx?scid=kb;EN-US;328551

If the number of logical processors on your server is greater than or equal to 8, then use 8 data files for tempdb. If the number of logical processors is less than 8, then use as many data files as your processor count.

You can use the following against any of the SQL Servers you manage to find out if any change is required in the tempdb data files to reduce contention and improve general performance.

Declare @tempdbfilecount as int;

select @tempdbfilecount = (select count(*) from sys.master_files where database_id=2 and type=0);

WITH Processor_CTE ([cpu_count], [hyperthread_ratio])

AS

(

      SELECT  cpu_count, hyperthread_ratio

      FROM sys.dm_os_sys_info sysinfo

)

select Processor_CTE.cpu_count as [# of Logical Processors], @tempdbfilecount as [Current_Tempdb_DataFileCount], 

(case 

      when (cpu_count<8 and @tempdbfilecount=cpu_count)  then 'No' 

      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes' 

      when (cpu_count<8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No'

      when (cpu_count>=8 and @tempdbfilecount=cpu_count)  then 'No (Depends on continued Contention)' 

      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount<cpu_count) then 'Yes'

      when (cpu_count>=8 and @tempdbfilecount<>cpu_count and @tempdbfilecount>cpu_count) then 'No (Depends on continued Contention)'

end) AS [TempDB_DataFileCount_ChangeRequired]

from Processor_CTE;

Here is a sample output. As you can see I have 8 processors and only 1 tempdb file. So I need to add 7 more files.

Last point before I wrap up this post. Once your tempdb database or log file is full, you have these options:-

1. Either you have to rollback any transactions consuming tempdb space or kill the transactions (not a good idea).

2. Create additional tempdb files in other drives which have free space, while you dig around to find the culprit who is growing tempdb.

3. Restart your SQL Server service.

Have fun working with tempdb. Here are some good references

Working with tempdb – http://technet.microsoft.com/hi-in/library/cc966545(en-us).aspx

Storage Engine Blog – http://blogs.msdn.com/b/sqlserverstorageengine/archive/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space.aspx

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2009/01/11/tempdb-monitoring-and-troubleshooting-out-of-space/

TempDB Monitoring and Troubleshooting: Out of Space

★★★★★

★★★★

★★★

★★

★

January 11, 2009 by Sunil Agarwal // 8 Comments

·          

·         0

·         0

One of the key challenges in TempDB is that it is a common resource for all applications running on an instance and any misbehaving application or rouge user command can take up all the space in TempDB bringing down other applications with it. In my discussions with customer during various conferences, I often hear of following suggestions

1.   Provide a way to control how much TempDB space can be allocated by various applications on an instance of SQL Server. Clearly, this will provide a very good way to isolate applications from misbehaving ones. In this case, if an application exceeds its limit, it may come to a stop even if there was space on TempDB. To address this, the SQL Server can possibly provide some alternatives like to allow space allocation if the TempDB is not in-use by other aplications and then do force deallocations when pressure from other applications mount.

2.   Provide multiple TempDBs and then assign different TempDBs to different applications. In my opinionm if SQL Server could do (1) well, then this may not be as use useful.

These suggestions are well taken but unfortunately SQL Server does not support this functionality today. So you wonder what you can do. Well, the SQL Server exposes a way using DMVs to identify TempDB space allocations by currently executing queries. If you identify that the TempDB space is running awfully low, you can use this new way to identify currently executing requests. May be some user ran an adhoc query that took significant space in TempDB. You, as an administrator, can then make the decision if you need to kill one or more of these queries to get back the space in TempDB.

Let me illustrate this with an example. I will use two large (actually not so large) tables and then join them using a hash join. You may recall that during hash join, one of the tables in hashed in memory and is backed by persistence in TempDB.

Now in another session, I will run the following DMV query

Here is the sample output of the query for my workload. I have simplified it by shortening the long DMV query and just put a symbolic name where XML show plan appears. This output shows that the query with hash-join is causing the most allocations in TempDB. Though in this case, we do know about the workload but you can run the above DMV query on any SQL Server without any knowlede of the workload and it can show you the top consumers (batches currently executing) of the space in TempDB. You can also take a look at the query plan to see what is causing the allocations in TempDB.

https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx#Monitoring Space Used by Queries

Troubleshooting Insufficient Disk Space in tempdb

This topic provides procedures and recommendations to help you diagnose and troubleshoot problems caused by insufficient disk space in the tempdb database. Running out of disk space in tempdb can cause significant disruptions in the SQL Server production environment and can prohibit applications that are running from completing operations.

tempdb Space Requirements

The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.

Diagnosing tempdb Disk Space Problems

The following table lists error messages that indicate insufficient disk space in the tempdb database. These errors can be found in the SQL Server error log, and may also be returned to any running application.

Error	Is raised when
1101 or 1105	Any session must allocate space in tempdb.
3959	The version store is full. This error usually appears after a 1105 or 1101 error in the log.
3967	The version store is forced to shrink because tempdb is full.
3958 or 3966	A transaction cannot find the required version record in tempdb.

tempdb disk space problems are also indicated when the database is set to autogrow, and the size of the database is quickly increasing.

Monitoring tempdb Disk Space

The following examples show how to determine the amount of space available in tempdb, and the space used by the version store and internal and user objects.

Determining the Amount of Free Space in tempdb

The following query returns the total number of free pages and total free space in megabytes (MB) available in all files in tempdb.

SELECT SUM(unallocated_extent_page_count) AS [free pages], 

(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount Space Used by the Version Store

The following query returns the total number of pages used by the version store and the total space in MB used by the version store in tempdb.

SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

(SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Longest Running Transaction

If the version store is using a lot of space in tempdb, you must determine what is the longest running transaction. Use this query to list the active transactions in order, by longest running transaction.

SELECT transaction_id

FROM sys.dm_tran_active_snapshot_database_transactions 

ORDER BY elapsed_time_seconds DESC;

A long running transaction that is not related to an online index operation requires a large version store. This version store keeps all the versions generated since the transaction started. Online index build transactions can take a long time to finish, but a separate version store dedicated to online index operations is used. Therefore, these operations do not prevent the versions from other transactions from being removed. For more information, see Row Versioning Resource Usage.

Determining the Amount of Space Used by Internal Objects

The following query returns the total number of pages used by internal objects and the total space in MB used by internal objects in tempdb.

SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

(SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Amount of Space Used by User Objects

The following query returns the total number of pages used by user objects and the total space used by user objects in tempdb.

SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

(SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

FROM sys.dm_db_file_space_usage;

Determining the Total Amount of Space (Free and Used)

The following query returns the total amount of disk space used by all files in tempdb.

SELECT SUM(size)*1.0/128 AS [size in MB]

FROM tempdb.sys.database_files

Monitoring Space Used by Queries

One of the most common types of tempdb space usage problems is associated with large queries that use a large amount of space. Generally, this space is used for internal objects, such as work tables or work files. Although monitoring the space used by internal objects tells you how much space is used, it does not directly identify the query that is using that space.

The following methods help identify the queries that are using the most space in tempdb. The first method examines batch-level data and is less data intensive than the second method. The second method can be used to identify the specific query, temp table, or table variable that is consuming the disk space, but more data must be collected to obtain the answer.

Method 1: Batch-Level Information

If the batch request contains just a few queries, and only one of them is a complex query, this is typically enough information to know just which batch is consuming the space instead of the specific query.

To continue with this method, a SQL Server Agent Job must be set up to poll from the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views by using a polling interval in the range of few minutes. A polling interval of three minutes is used in the following example. You must poll from both views because sys.dm_db_session_space_usage does not include the allocation activity of the current active task. Comparing the difference between the pages allocated at two time intervals lets you calculate how many pages are allocated in between the intervals.

The following examples provide the queries that are required for the SQL Server Agent job.

A. Obtaining the space consumed by internal objects in all currently running tasks in each session.

The following example creates the view all_task_usage. When queried, the view returns the total space used by internal objects in all currently running tasks in tempdb.

CREATE VIEW all_task_usage

AS 

    SELECT session_id, 

      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,

      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count 

    FROM sys.dm_db_task_space_usage 

    GROUP BY session_id;

GO

B. Obtaining the space consumed by internal objects in the current session for both running and completed tasks

The following example creates the view all_session_usage. When queried, the view returns the space used by all internal objects running and completed tasks in tempdb.

CREATE VIEW all_session_usage 

AS

    SELECT R1.session_id,

        R1.internal_objects_alloc_page_count 

        + R2.task_internal_objects_alloc_page_count AS session_internal_objects_alloc_page_count,

        R1.internal_objects_dealloc_page_count 

        + R2.task_internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count

    FROM sys.dm_db_session_space_usage AS R1 

    INNER JOIN all_task_usage AS R2 ON R1.session_id = R2.session_id;

GO

Assume that when these views are queried at a three-minute interval, the result sets provide the following information.

·         At 5:00 P.M., session 71 allocated 100 pages and deallocated 100 pages since the start of the session.

·         At 5:03 P.M., session 71 allocated 20100 pages and deallocated 100 pages since the start of the session.

When you analyze this information, you can tell that between the two measurements: The session allocated 20,000 pages for internal objects, and did not deallocate any pages. This indicates a potential problem.

Note

As the database administrator, you may decide to poll more frequently than three minutes. However, if a query runs for less than three minutes, the query probably will not consume a significant amount of space in tempdb.

To determine the batch that is running during that time, use SQL Server Profiler to capture the RPC:Completed and SQL:BatchCompleted event classes.

An alternative to using SQL Server Profiler is to run DBCC INPUTBUFFER once every three minutes for all the sessions, as shown in the following example.

DECLARE @max int;

DECLARE @i int;

SELECT @max = max (session_id)

FROM sys.dm_exec_sessions

SET @i = 51

  WHILE @i <= @max BEGIN

         IF EXISTS (SELECT session_id FROM sys.dm_exec_sessions

                    WHERE session_id=@i)

         DBCC INPUTBUFFER (@i)

         SET @i=@i+1

         END;

Method 2: Query-Level Information

Sometimes just looking at the input buffer or the SQL Server Profiler event SQL:BatchCompleted does not always tell which query is using most of the disk space in tempdb. The following methods can be used to find this answer, but these methods require collecting more data than the procedures defined in Method 1.

To continue with this method, set up a SQL Server Agent Job job that polls from the sys.dm_db_task_space_usage dynamic management view. The polling interval should be short, once a minute, as compared to Method 1. This short interval is because sys.dm_db_task_space_usage does not return data if the query (task) is not currently running.

In the polling query, the view defined on the sys.dm_db_task_space_usage dynamic management view is joined with sys.dm_exec_requests to return the sql_handle, statement_start_offset, statement_end_offset, and plan_handle columns.

CREATE VIEW all_request_usage

AS 

  SELECT session_id, request_id, 

      SUM(internal_objects_alloc_page_count) AS request_internal_objects_alloc_page_count,

      SUM(internal_objects_dealloc_page_count)AS request_internal_objects_dealloc_page_count 

  FROM sys.dm_db_task_space_usage 

  GROUP BY session_id, request_id;

GO

CREATE VIEW all_query_usage

AS

  SELECT R1.session_id, R1.request_id, 

      R1.request_internal_objects_alloc_page_count, R1.request_internal_objects_dealloc_page_count,

      R2.sql_handle, R2.statement_start_offset, R2.statement_end_offset, R2.plan_handle

  FROM all_request_usage R1

  INNER JOIN sys.dm_exec_requests R2 ON R1.session_id = R2.session_id and R1.request_id = R2.request_id;

GO

If the query plan is in cache, you can retrieve the Transact-SQL text of the query and the query execution plan in XML showplan format at any time. To obtain the Transact-SQL text of the query that is executed, use the sql_handle value and the sys.dm_exec_sql_text dynamic management function. To obtain the query plan execution, use the plan_handle value and the sys.dm_exec_query_plan dynamic management function.

SELECT * FROM sys.dm_exec_sql_text(@sql_handle);

SELECT * FROM sys.dm_exec_query_plan(@plan_handle);

If the query plan is not in cache, you can use one of the following methods to obtain the Transact-SQL text of the query and query execution plan.

A. Using the polling method

Poll from the view all_query_usage, and run the following query to obtain the query text:

SELECT R1.sql_handle, R2.text 

FROM all_query_usage AS R1

OUTER APPLY sys.dm_exec_sql_text(R1.sql_handle) AS R2;

Because sql_handle should be unique for each unique batch, you do not have to save duplicate sql_handle entries.

To save the plan handle and XML plan, run the following query.

SELECT R1.plan_handle, R2.query_plan 

FROM all_query_usage AS R1

OUTER APPLY sys.dm_exec_query_plan(R1.plan_handle) AS R2;

B. Using SQL Server Profiler events

As an alternative to polling the sys.dm_exec_sql_text and sys.dm_exec_query_plan functions, you can use SQL Server Profiler events. There are profiler events that can be used to capture the query plan and query text that is generated. For example, Event 165 returns performance statistics for trace, SQL text, query plans, and query statistics.

Monitoring Space Used by Temp Tables and Table Variables

You can use an approach similar to polling queries for monitoring the space used by temp tables and temp variables. Applications that acquire a large amount of user data inside temp tables or temp variables can cause space use problems in tempdb. These tables or variables belong to the user objects. You can use the user_objects_alloc_page_count and user_objects_dealloc_page_count columns in the sys.dm_db_session_space_usage dynamic management view and follow the methods described earlier.

Monitoring Page Allocation and Deallocation by Session

The following table shows the results returned by the sys.dm_db_file_space_usage, sys.dm_db_session_space_usage, and sys.dm_db_task_space_usage dynamic management views for a specified session. Each row represents an allocation or deallocation activity in tempdb for a specified session. The activity is listed in the Event column. The remaining columns show the values that would be returned in the columns of the dynamic management views.

For this scenario, assume that the tempdb database starts with 872 pages in unallocated extents, and 100 pages in user-object reserved extents. The session allocates 10 pages for a user table, and then deallocates all of them. The first 8 pages are in mixed extent. The remaining 2 pages are in uniform extent.

Event	dm_db_file_space_usage unallocated_extent_page_count column	dm_db_file_space_usage user_object_reserved_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_alloc_page_count column	dm_db_session_space_usage and dm_db_task_space_usage user_object_dealloc_page_count column
Start	872	100	0	0
Allocate page 1 from existing mixed extent	872	100	1	0
Allocate pages 2 to 8: consuming one new mixed extent	864	80	8	0
Allocate page 9: consuming one new uniform extent	856	108	16	0
Allocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 10 from existing uniform extent	856	108	16	0
Deallocate page 9, and the uniform extent	864	100	16	8
Deallocate page 8	864	100	16	9
Deallocate page 7 to 1, and deallocate on mixed extent	872	100	16	16

http://blog.sqlauthority.com/2015/01/23/sql-server-who-is-consuming-my-tempdb-now/

SQL SERVER – Who is Consuming my TempDB Now?

January 23, 2015Pinal DaveSQL, SQL Server, SQL Tips and Tricks15 comments

Off late my love for TempDB and writing on topics of tempDB has been a great learning experience. The more I work with tempDB, the more fascinated I am. TempDb is being used by a number of operations inside SQL Server, let me list some of them here:

• Temporary user objects like temp tables, table variables
• Cursors
• Internal worktables for spool and sorting
• Row Versioning for snapshot isolation
• Online Index rebuild operations
• MARS (Multiple Active Resultsets)
• AFTER Triggers and more

These are some of the ways in which tempdb in our servers get used. When I was talking about this to one of my DBA friends, he always asks some interesting questions. He said the previous blogs I wrote helped in understanding how to read temp tables, how to see table variable data. Now his requirement was different. He wanted to know if there was any script which will let him know who was consuming tempDB resources. If tempDB is used by many inside the SQL Server context, it is important that we know how to get this information from DMVs. There are a number of DMVs we can use:

• dm_db_file_space_usage – Returns space usage information for each file in tempdb
• dm_db_session_space_usage – Returns the number of pages allocated and deallocated by each session
• dm_db_task_space_usage – Returns page allocation and deallocation activity by task
• We can join these DMV’s with various other DMV’s like sys.dm_exec_sessions, sys.dm_exec_requests, etc and get to the actual TSQL statement and plan responsible for these allocations.

Here is a simple script that will outline the sessions which are using TempDB currently.
SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) DESC

Have you ever had such requirements to monitor and troubleshoot tempDB in your environments? What have you been using to monitor your tempDB usage? What is the typical output you are getting in your environments? Do let me know as we can learn together.

http://www.databasejournal.com/features/mssql/tempdb-space-usage-in-sql-server.html

Featured Database Articles

MS SQL

Posted June 10, 2011

Tempdb Space Usage in SQL Server

By Claire Hsu

We all know how important tempdb is and know that tempdb gets recreated every time SQL Server is restarted. We place tempdb on a fast disk array exclusively hoping it will run as fast as possible. Here we dig a little deeper to focus on how space is being used inside tempdb.

Space Usage categories

Roughly, we can separate tempdb usage into three categories:

• User object
• Internal job
• Version store

I would like to focus our discussion on few of the common operational tasks that utilize tempdb.

User Objects

Any user with permission to connect to the SQL instance can create a temporary table and table variables. You can't back up or restore tempdb, so any temporary object or table variable created previously (before SQL Server was restarted) will be wiped out and cannot be recovered.

The scope of user objects are either in the user session or in the routine. Routine can be in the form of a stored procedure, function or trigger. User objects in tempdb can be like one of the list below:

• User table and index (created explicitly in tempdb)
• Global temporary table and index
• Local temporary table and index
• Table variable

There are two ways you can query to get the space used by tempdb user objects.

select convert(numeric(10,2),round(sum(data_pages)*8/1024.,2)) as user_object_reserved_MB



from tempdb.sys.allocation_units a



inner join tempdb.sys.partitions b on a.container_id = b.partition_id



inner join tempdb.sys.objects c on b.object_id = c.object_id

Returned result:

or:

select



reserved_MB= convert(numeric(10,2),round((unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024.,2)) ,



unallocated_extent_MB =convert(numeric(10,2),round(unallocated_extent_page_count*8/1024.,2)),



user_object_reserved_page_count,



user_object_reserved_MB =convert(numeric(10,2),round(user_object_reserved_page_count*8/1024.,2))



from sys.dm_db_file_space_usage

Returned result:

Note that the returned value for [user_object_reserved_MB] from both queries is almost the same.

Internal objects

There are three types of objects considered to be internal objects.

1. Work tables
• Spooling, to hold intermediate results during a large query
• DBCC CHECKDB or DBCC CHECKTABLE
• Temporary large object storage (LOB)
• Processing SQL Service Broker Objects
• Common table expression
• Keyset-driven and static cursors
2. Work files
• Hash join or hash aggregate operations
3. Sort units
• ORDER BY, GROUP BY, UNION queries
• Index rebuilt or creation (with sort in tempdb is specified)

Unfortunately, there is no catalog view or dynamic management object that can provide the detailed page count information for each internal object. You can get the total number of pages in tempdb allocated for internal objects from DMV — sys.dm_db_file_space_usage.

select

reserved_MB=(unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,

unallocated_extent_MB =unallocated_extent_page_count*8/1024.,

internal_object_reserved_page_count,

internal_object_reserved_MB =internal_object_reserved_page_count*8/1024.

from sys.dm_db_file_space_usage

Returned result for internal object space used in tempdb:

Version Store

Version store is a collection of data pages that holds the data rows used for row versioning. Below are the features that use version store:

• Triggers
• MARS
• Online Index operation
• Row versioning-based transaction isolation level
• Read-committed isolation level (statement-level read consistency)
• Snapshot isolation level (transaction-level read consistency)

For read-committed isolation level, version store only needs to keep the row version until the end of the select statement.

For snapshot isolation level, version store will need to keep the version of the row when the transaction started so the transaction can refer back the original version of the row. If the transaction happens to be unreasonably long, with many rows modified by some other transactions, the snapshot query may fail if tempdb has space shortage and version store can no longer retain all the version required.

There are performance counters that can be used to monitor the size of tempdb and version store.

SQLServer: Transactions performance object

Object counter Description Free Space in tempdb Free space in kilobytes for tempdb Version store size in KB, tempdb being used to store snapshot isolation level row versions Version generation rate row versions are added to tempdb version store (Kilobyte/sec) Longest Transaction Running Time The length of time (in seconds) since the start of the transaction that has been active longer than any other current transaction. Snapshot Transactions The number of currently active transactions using the snapshot isolation level.

You can also query dynamic management view — sys.dm_db_file_space_usage — to get the total number of pages currently allocated in tempdb for versions tore.

select 

reserved_MB=(unallocated_extent_page_count+version_store_reserved_page_count+user_object_reserved_page_count+internal_object_reserved_page_count+mixed_extent_page_count)*8/1024. ,

unallocated_extent_MB =unallocated_extent_page_count*8/1024., 

version_store_reserved_page_count,

version_store_reserved_MB =version_store_reserved_page_count*8/1024.

from sys.dm_db_file_space_usage

Returned result for version stored occupant space on tempdb:

Now let's look into some features that use tempdb and how to estimate the space required on tempdb for performing these operations.

DBCC CHECKDB

For DBCC CHECKDB, we can use DBCC CHECKDB WITH ESTIMATEONLY to check how much space it requires without actually executing the command. It returns resultset for [Estimated TEMPDB space needed for CHECKALLOC(KB)] and [Estimated TEMPDB space needed for CHECKTABLES(KB)].

You may want to run this estimation for a very large database and ensure your tempdb has sufficient space to complete the task.

Index Creation and Rebuild

Tempdb space is only needed if SORT_IN_TEMPDB is on. The tempdb space is needed to store the intermediate sort result for index building. Let's take a look at both online index operation and offline index operation.

Important note; you cannot use SORT_IN_TEMPDB when dropping an index.

1. Offline Index operation and SORT_IN_TEMPDB

Here are examples for how much space we need on tempdb when creating/building index offline with the sort in tempdb option.

Create Non-clustered index:
tempdb should be big enough to store leaf rows of the indexes

index leaf row size * number of rows 

Create Clustered index:
tempdb should be big enough to store data rows of the table.

table row size * number of rows

Clustered and non-clustered indexes from same table:
tempdb should be big enough to carry largest index, usually clustered index is largest among all indexes from one table.

Largest index row size * number of rows

If rebuilding clustered index with different cluster key columns:
Since new clustered index leaf row size and old clustered index leaf row size are identical, so it's basically the total data rows size of the table

clustered index row size * number of rows

If there are cases when SQL optimizer doesn't do any sorting or if the sorting area can be performed in memory, then SORT_IN_TEMPDB will be ignored and no space will be needed in tempdb.

2. Online Clustered Index Operation and SORT_IN_TEMPDB

When performing an online index operation for a clustered index, a temporary mapping index is created to fulfill the operation. Below is the formula for calculating a temporary mapping index.

Temporary mapping index size = (number of rows of the table) * (old key size + new index key size - overlapping index key size) / (fillfactor in percentage) 

If there are key columns overlapping on the index between the old index key and new index key, then then we need to subtract the overlapping key size.

Create Clustered Index online on a heap table:
tempdb should be big enough to store data rows of the table and temporary mapping index

Data rows of the table= Table row size * number of rows



Temporary mapping index size = (number of rows of the table) * (RID+ new index key size) / (fillfactor in percentage)



Total space = Data rows of the table + Temporary mapping index

Rebuilding Clustered index with different clustered key columns:
tempdb should be big enough to store data rows of the table and temporary mapping index

Data rows of the table = Table row size * number of rows 



Temporary mapping index size = (number of rows of the table) * (old index key size+ new index key size - size in overlapping) / (fillfactor in percentage)   



The total tempdb space needed = Data rows of the table + Temporary mapping index

Conclusion

It's imperative to understand how SQL Server uses tempdb so we can do better perform capacity planning when setting up tempdb. A proper sizing and configured tempdb can improve the overall performance of a SQL instance.

http://dba.stackexchange.com/questions/13911/how-to-find-the-sql-statements-that-caused-tempdb-growth