次の方法で共有

TempDB Monitoring and Troubleshooting: Out of Space

  • [アーティクル]

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. tempdb-space-1

Now in another session, I will run the following DMV query tempdb-space-2

 

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. tempdb-space-3

 

Thanks

 

Sunil Agarwal

Comments

  • Anonymous
    January 11, 2009
    PingBack from http://blog.a-foton.ru/index.php/2009/01/12/tempdb-monitoring-and-troubleshooting-out-of-space/

  • Anonymous
    April 29, 2009
    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

  • Anonymous
    April 29, 2009
    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

  • Anonymous
    May 16, 2009
    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/

  • Anonymous
    November 09, 2010
    The comment has been removed

  • Anonymous
    July 04, 2012
    How can I use this in SQL 2000?

  • Anonymous
    July 05, 2012
    The DMVs are only available SQL2005 onwards. Thanks Sunil

  • Anonymous
    September 19, 2012
    The comment has been removed