Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

We had a slowdown on a db server this morning. Checking the logs, the only thing that happened during the 2-minute slowdown worth noting was an object being created and deleted in tempdb over the span of about 3ms. I know that temporary tables and table variables would do this, but as far as I can tell none were used. Are there any other actions (e.g. nested selects) which might create a temporary table?

P.S. We've already figured out that it was most likely a log expansion that caused the slowdown, and the hit to tempdb was unrelated. I just wanted to ask for academic purposes and because I just cannot figure out what caused tempdb to be used.

share|improve this question
There's a good rundown here: msdn.microsoft.com/en-us/library/ms345368.aspx – JNK♦ Jan 12 '12 at 21:34
3  
Lots of things can use tempdb in addition to temp tables and table variables - CTEs, sorts, joins, spools, cursors, snapshot isolation, service broker, index maintenance... what else did you observe about the "object" that was created, and other than the fact that it happened in your 2-minute window, what leads you to believe it is anything but a coincidence? – Aaron Bertrand Jan 12 '12 at 21:42
1  
+1 @AaronBertrand - the real question is how does he know it's tempdb related at all – JNK♦ Jan 12 '12 at 21:58

1 Answer

up vote 2 down vote

To summarize Tempdb is used to store 3 types of objects:

  1. user objects

    • local temporary table (seen by one session)
    • global temporary table (seen by all sessions)
    • Table variables

  2. internal objects used temporarily by SQL server during Query processing : operations such as sort , join, hash and cursors all require space in tempdb. see the sys.dm_db_session_space_usage DMV.

  3. the version store : The following features make use of the version store:

    • Triggers
    • Snapshot isolation or Read-Commited Snapshot isolation
    • Online index operations
    • Multiple Active Result Sets (MARS)

Please note that using the sys.dm_tran_version_store can be ressource intensive.

May I suggest that you check Troubleshooting insufficient disk space in tempdb for a few examples of how to use the DMV.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.