SQL SERVER – SQL Challenge – SQL Puzzle – Query Creating Most TempDB IO Usage

melting SQL SERVER   SQL Challenge   SQL Puzzle   Query Creating Most TempDB IO Usage

Recently, there have been a lot of interesting concepts in various challenges. My friend Jacob Sebastian is running the SQLQuiz for the entire month, and it has been very popular and going just great. So here I thought I would put something very similar to the quiz bee.

The award here is simple, all valid answers will be published on this blog with due credit to you, plus the credit would link back to your desired profile.

Now the question is: What are the queries which are creating lots of IO operations in TempDB?

You can use any DMV to answer this question. You need to list all the operations which are creating IO operations in TempDB, and those which may grow the size TempDB. Sometimes it is not TempDB but the open transactions with lots of queries that can lead to lots of TempDB IO and size growth. In that case, we want to find those open transactions, too. There are no limits as to how many DMVs you can use or how many suggestions you can give. Just find a reason that grows TempDB and creates lots of IO.

I think this is very interesting and many also want to learn the answer for this great question.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

14 thoughts on “SQL SERVER – SQL Challenge – SQL Puzzle – Query Creating Most TempDB IO Usage

  1. Tempdb is a system database used by SQL Server to store temporary tables and temporary stored procedures, for sorting, subqueries, and aggregates with GROUP BY, ORDER BY, for cursors and so on. Tempdb database contains only temporary objects


  2. The default size of tempdb is 2 MB. If you use large temporary tables, or run many queries with GROUP BY or ORDER BY, or frequently use large cursors, then the size of the tempdb database should be increased.


  3. Hi,
    I’m new to SQL Server 2005.
    I wish to change the the table structure of table1 which already has n number of rows.
    Now the problem is my data in table1 must not be lost if I alter the table structure.
    Please help me as soon as possible.


      • You can able to change the table structure by

        ALTER TABLE TABLENAME ADD ColumnName datatype

        If you want to change the existing column datatype

        ALTER TABLE TABLENAME ALTER COLUMN ColumnName newdatatype


    • You can change your table structure with the help of alter statement. If u r adding a new column there will no problem with existing data.

      Yes while modifying existing column datatype do see that the data should comply with the new datatype.

      And if u want to rename your table or column u can make use of sp_rename procedure.


  4. You can do this in two ways without losing your data.
    1) Duplicate the table:(Safe game)
    Generate a create table script of your existing table(let’s say in db name ‘X’) and execute it in any other (say Y database). Using the import/export task, export your data to Y from X by mapping the table. After data transfer is done, drop the table in X and create what you want. Then, import the data from Y by mapping the column names. Note: If you are trying to add any new columns, make sure you made them ‘NULL’ by default.

    2)If you are just trying to add new columns, you need not loose your data. Make sure go to Tools in SSMS Menu , navigate to OPTIONS>DESIGNERS>TABLE AND DB DESIGNERS>. Make sure the option that says ‘Prevent saving data that requires table re-creation’ is uncheked. Now, go to table design mode and add columns, assign NULL to them or add a default value and save. You should be good.


  5. For open transaction,


    This will tell u the details , that increasing the tempdb size.

    One more news,

    Once the Tempdb is full,the DB will become read-only.



  6. I don’t think we have a good answer to Pinal’s question yet.

    What are the queries which are creating lots of IO operations in TempDB?

    We are trying to purchase SSD’s and put our tempdb on it. I believe having this question answered correctly would help me in making that decision as to whether in addition to optmiizing my queries should we still make the switch to SSD’s. Thanks.


  7. Hi Pinal

    I have gone through this posting but didnt find correct answer till now.

    Kindly lets know how to find which query is taking up space in Tempdb.



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s