Tempdb Database:  It is a workspace for holding temporary objects or intermediate result sets. -       Temporary user objects that are explicitly created, such as: global o local temporary tables, temporary stored procedures, table variables, or cursors. -       Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting. -       Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions. -       Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers. Operations within Tempdb are minimally logged. This enables transactions to be rolled back. Tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in Tempdb to be saved from one session of SQL Server to another. Every SQL Server has a shared database named Tempdb that is for use by temporary objects. Because there is only one Tempdb database per instance, it often proves to be a bottleneck for those systems that make heavy usage of Tempdb. Typically, this happens because of PAGELATCH, in-memory latch contention on the allocation bitmap pages inside of the data files. The allocation bitmap pages are the page free space (PFS), global allocation map (GAM), and shared global allocation map (SGAM) pages in the database. The first PFS page occupies PageID 1 of the database, the first GAM page occupies PageID 2, and the first SGAM page occupies PageID 3 in the database. After the first page, the PFS pages repeat every 8088 pages inside of the data file, the GAM pages repeat every 511,232 pages (every 3994MB known as a GAM interval), and the SGAM pages repeat every 511,232 + 1 pages in the database. When PAGELATCH contention exists on one of the allocation bitmap pages in the database, it is possible to reduce the contention on the in-memory pages by adding additional data files, with the same initial size and auto-growth configuration. This works because SQL Server uses a round-robin, proportional fill algorithm to stripe the writes across the data files. When multiple data files exist for a database, all of the writes to the files are striped to those files, with the writes to any particular file based on the proportion of free space that the file has to the total free space across all of the files: This means that writes are proportionally distributed to the files according to their free space, to ensure that they fill at the same time, irrespective of their size. Each of the data files has its own set of PFS, GAM, and SGAM pages, so as the writes move from file to file the page allocations to occur from different allocation bitmap pages spreading the work out across the files and reducing the contention on any individual page. The size and physical placement of the Tempdb database can affect the performance of a system. As per the Microsoft best practice always recommended separate SSD drive for Tempdb . To optimizing Tempdb Performance so you can learn how to avoid performance issue caused by lack of free disk space. There are several different published suggestions for calculating the number of files used by Tempdb for the best performance. The SQL Server Customer Advisory Team (SQLCAT) team recommends that Tempdb should be created with one file per physical processor core, and this tends to be one of the most commonly quoted configuration methods for Tempdb. While this recommendation is founded in practical experience, it is important to keep in mind the types of environments that the SQLCAT team typically works, which are typical the highest volume, largest throughput environments in the world, and therefore are atypical of the average SQL Server environment. So while this recommendation might prevent allocation contention in Tempdb, it is probably overkill for most new server implementations today. Paul Randal has written about this in the past in his blog post A SQL Server DBA myth a day: (12/30) Tempdb should always have one data file per processor core where he suggests a figure of ¼ to ½ the number of cores in the server as a good starting point. This has typically been the configuration that I have followed for a number of years for setting up new servers, and I made a point of then monitoring the allocation bitmap contention of Tempdb on the actual workload to figure out if it was necessary to increase the number of files further. At PASS Summit 2011, Bob Ward, a Senior Escalation Engineer in Product Support, presented a session on Tempdb and some of the changes that were coming in SQL Server 2012. As a part of this session Bob recommended that for servers with eight CPUs or less, start off with one file per CPU for Tempdb. For servers with more than eight CPUs Bob recommended to start off with eight Tempdb data files and then monitor the system to determine if PAGELATCH contention on the allocation bitmaps was causing problems or not. If allocation contention continues to exist with the eight files, Bob’s recommendation was to increase the number of files by four and then monitor the server again, repeating the process as necessary until the PAGELATCH contention is no longer a problem for the server. To date, these recommendations make the most sense from my own experience and they have been what we’ve recommended at SQLskills since Bob’s session at PASS. Disk space issue at Tempdb: Most of the issues occur because of the long running queries or reports you are executing in the SQL Server Instance and that have to use different temporary data stored in the Tempdb database. 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; 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. if the query is not active, what you get back may not be the actual culprit). ;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; Reference article: https://www.simple-talk.com/sql/database-administration/optimizing-tempdb-configuration-with-sql-server-2012-extended-events/ https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx http://blogs.technet.com/b/beatrice_popa/archive/2013/05/21/sql-server-tempdb-database-and-disk-space-issues.aspx http://dba.stackexchange.com/questions/19870/how-to-identify-which-query-is-filling-up-the-tempdb-transaction-log By - Rajib Kundu