TempDB Monitoring and Troubleshooting: Out of Space

TempDB Monitoring and Troubleshooting: Out of Space

Rate This
  • Comments 8

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.

create table t1 (c1 int primary key, c2 int, c3 char(8000))

go

 

create table t2  (C4 int, c5 char(8000))

go

 

 

 

declare @i int

select @i = 0

while (@i < 6000)

begin

insert into t1 values (@i, @i + 1000, 'hello')

insert into t2 values (@i,'there')

set @i = @i + 1

end

 

 

-- now let us clean the buffer pool so that this

-- query takes some time to complete and allows us

-- to monitor the TempDB space usage

dbcc freeproccache

DBCC DROPCLEANBUFFERS

 

-- Now run the query. Note, I have used a hash-join hint

select c1, c5

from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4

order by c2

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

-- This DMV query shows currently executing tasks and

-- tempdb space usage

-- Once you have isolated the task(s) that are generating lots

-- of internal object allocations,

-- you can even find out which TSQL statement and its query plan

-- for detailed analysis

 

select top 10

t1.session_id,

t1.request_id,

t1.task_alloc,

     t1.task_dealloc, 

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

          (CASE WHEN statement_end_offset = -1

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

                   ELSE statement_end_offset

              END - t2.statement_start_offset)/2)

     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,

 (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

 

from      (Select session_id, request_id,

sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,

sum (internal_objects_dealloc_page_count + user_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) and

      t1.session_id > 50

order by t1.task_alloc DESC

 

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.

session_id request_id           task_alloc           task_dealloc

---------- ----------            ---------            -------------

51          52                  6016                 1112

52          0                   0                    0

 

query_text  

select c1, c5  from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4  order by c2

select top 10  t1.session_id,   t1.request_id … (THE DMV query)

 

 

query_plan                                                                                                                                                                                                                                    query_plan

<XML-showplan fpr the first query>

<XML-showplan fpr the DMV query>

 

 

Thanks

Sunil Agarwal

Leave a Comment
  • Please add 4 and 3 and type the answer here:
  • Post
  • PingBack from http://blog.a-foton.ru/index.php/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space/

  • tempdb is showing one strange behavior :

    data files are of size 10gb each if i see on sql server ,

    but if i see them on operating system file system they seems to be of 50GB each

    and no way to shrink them as for sql server they are of size only 10gb

    any cpmment ?

    sql 2005 sp3 on windows 2003

  • are you sure you are looking at the file size or the allocated space? what command did you use? thakss

    This KB on shrink TempDB

    http://support.microsoft.com/kb/307487

  • Thank you for such valuable information.

    For more information about Monitoring and Optmizing tempdb, I can suggest you to click the below link.

    http://www.sqllion.com/2009/05/monitoring-tempdb-in-sql-server-2005/

  • Hi Sunil,

    Would there be a reason for having a lot of space reported in sys.dm_db_file_space_usage (internal_objects_reserved_page_count) with no associated consumption from the session / task point of view in sys.dm_db_session_space_usage / sys.dm_db_task_space_usage ?

    I've been autiding a SQL Server 2005 SP3 (out of the KB947204 situation) lately with 230 Mb reported as internal space used from sys.dm_db_file_space_usage, and only 20 Mb for all the sessions. So I have 100Mb allocated for internal objects but no idea where this space is related to. I noticed about 10 Sort warnings / Hash warnings per 24 hours, Service Broker is not used, only 3 to 4 workfiles are created per second, nothing that could explain this difference.

    Thanks for your posts.

    David B.

  • How can I use this in SQL 2000?

  • The DMVs are only available SQL2005 onwards.

    Thanks

    Sunil

  • Sunil,

    I have a question what should we do if we're seeing high number of TSQL statements like FETCH_API_CURSORXXXXXXXX. How we do decipher this statement to see what's going on inside?

Page 1 of 1 (8 items)