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))
create table t2 (C4 int, c5 char(8000))
declare @i int
select @i = 0
while (@i < 6000)
insert into t1 values (@i, @i + 1000, 'hello')
insert into t2 values (@i,'there')
set @i = @i + 1
-- 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
-- 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
(SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(max),text)) * 2
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
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
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)
<XML-showplan fpr the first query>
<XML-showplan fpr the DMV query>
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
Thank you for such valuable information.
For more information about Monitoring and Optmizing tempdb, I can suggest you to click the below link.
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.
How can I use this in SQL 2000?
The DMVs are only available SQL2005 onwards.
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?