TempDB

1. Tempdb is filling up drastically, what might be the activity that filling up the tempdb?

Usually, tempdb fills up when you are low on disk space, or when you have set an unreasonably low maximum size for database growth.

Many people think that tempdb is only used for #temp tables. When in fact, you can easily fill up tempdb without ever creating a single temp table. Some other scenarios that can cause tempdb to fill up:

§  Any sorting that requires more memory than has been allocated to SQL Server will be forced to do its work in tempdb

§  DBCC CheckDB(‘any database’) will perform its work in tempdb — on larger databases, this can consume quite a bit of space

§  DBCC DBREINDEX or similar DBCC commands with ‘Sort in tempdb’ option set will also potentially fill up tempdb

§  Large resultsets involving unions, order by / group by, cartesian joins, outer joins, cursors, temp tables, table variables, and hashing can often require help from tempdb

§  Any transactions left uncommitted and not rolled back can leave objects orphaned in tempdb

§  Use of an ODBC DSN with the option ‘create temporary stored procedures’ set can leave objects there for the life of the connection

§  Table value functions that pull large data sets hold their data in tempdb

§  If you are using Snapshot isolation on a database with a high number of transactions the snapshot data is stored in tempdb 

2. How to analyze the TempDB contention

• Run the below command

USE tempdb

GO
SELECT table_name FROM information_schema.tables

If you have a few tables, then you are most likely dealing with a large set issue. If you have a very large number of tables, then there is a runaway process or an unclosed process.

• Find the size of the tables. Shortlist the biggest tables and depends on the type of table we can find out the source.
• Make sure that disk space is not full and capacity settings for tempdb (low size for max growth)

Also with queries that fail to use an index on large tables can cause this from time to time. Do you have autoshrink turned on for tempdb

• You could check in on your record locking strategies for your executable sessions and/or records might not be getting released properly. Use the query optimizer tools maybe
• Check for index rebuilding jobs, data pulling jobs/triggers blocking sessions, long running transactions
• Use profiler / perfmon / dbcc commands to find the bottlenecks
• You can use Profiler to watch for events like database file auto grow and log file auto grow. If this is happening often, then you know that the space you’ve allocated to tempdb is not sufficient.
• You can also watch performance monitor’s counter for PhysicalDisk: CurrentDiskQueueLength on the drive where tempdb exists. If this number is consistently greater than 2, then there is likely a bottleneck in disk I/O.

3. How to fix the TempDB filling issue?

Short-Term Fix:

• Shrink Tempdb: We can shrink the db using ShrinkDatabase, DBCC ShrinkFile. If you can’t shrink the log, it might be due to an uncommitted transaction.
• See if you have any long-running transactions with the following command “dbcc opentran(tempdb)”
• Check the oldest transaction (if it returns any), and see who the SPID is “DBCC INPUTBUFFER(SPID)”
• Query will help you determine if you want to end this process with “KILL SPID”

• Restarting SQL Server will re-create tempdb from scratch, and it will return to its usually allocated size. In and of itself, this solution is only effective in the very short term; assumedly, the application and/or T-SQL code which caused tempdb to grow once will likely cause it to grow again.

Long-Term Prevention:

• Make sure that tempdb is set to autogrow — do *NOT* set a maximum size for tempdb. If the current drive is too full to allow autogrow events, then buy a bigger drive, or add files to tempdb on another device (using ALTER DATABASE) and allow those files to autogrow.
• For optimal performance, make sure that its initial size is adequate to handle a typical workload (autogrow events can cause performance to suffer as it allocates new extents).
• If possible, put tempdb on its own physical disk, array or disk subsystem
• To prevent tempdb log file growth, make sure tempdb is in simple recovery mode
• Try to make sure you have covering indexes for all large tables that are used in queries that can’t use a clustered index / index seek.
• In general, try to make your code as efficient as possible… avoid cursors, nested loops, and #temp tables if possible.
• Make sure all the transactions are having the corresponding Commit and Rollback

4. The most common argument I heard is table variables are in-memory structures (stored in memory not on tempdb) not like temporary tables. Is that true? How can you justify?

It’s actually a wrong assumption; both table variables and temp tables are stored in tempdb. A DMV “sys.dm_db_session_space_usage” can help us to make sure these objects use Temdb.

On a development machine restart the SQL server and select data from above DMV as below.

SELECT session_id, database_id, user_objects_alloc_page_count FROM sys.dm_db_session_space_usage WHERE session_id > 50 ;

Initially user_objects_alloc_page_count is shown as 0.

Create a simple temporary table and insert test data and now check the same DMV, we can see the page_count as 1.

Then create a table variable and insert a row and we can see the page_count increased to 2. 

5. What database objects are stored in tempdb?

There are three different types of objects stored in tempdb. 

Internal Objects:

1. Intermediate runs for sort.
2. Intermediate results for hash join and hash aggregates.
3. XML variables or other large object (LOB) data type variables. (text, image, ntext, varchar(max), varbinary(max))
4. Queries that need a spool to store intermediate results.
5. Keyset cursors to store the keys.
6. Static cursors to store a query result.
7. Service Broker to store messages in transit.
8. INSTEAD OF triggers to store data for internal processing.
9. DBCC CHECK internally uses a query that may need to spool intermediate results.
10. Query notification and event notification use Service Broker.

Note: Page allocations on internal objects and Updates to internal objects do not generate log records.

Does not appear in catalog views such as sys.all_objects

Version Store:

1. Snapshot Isolation / Read Committed Snapshot Islotaion
2. Triggers (After Triggers). Instead of triggers doesn’t generate versions.
3. MARS (Multiple Active Result Sets)
4. Index Rebuilds

Note: Inserts into version stores do not generate log records.

Does not appear in catalog views such as sys.all_objects

User Objects:

1. User defined tables and indexes
2. Local and global temporary tables, bulk insert and BCP intermediate results
3. Index rebuilds with “SORT IN TEMPDB” option.

Note: Most of the operations under this category are bulk logged.

Appear in catalog views such as sys.all_objects.

5. How to move tempdb files?

Steps to move TempDB and log files to new location

Here are the steps to move SQL Server temporary database :

• Identify the location of TempDB data and log files
• Change the location of TempDB data and log files using ALTER DATABASE
• Stop and restart the SQL Server service
• Check path change
• Delete old TempDB as well as .mdf and .ldf files