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

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

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 (https://blog.sqlauthority.com)

Best Practices, Database, SQL Scripts, SQL TempDB
Previous Post
SQLAuthority News – Database Performance for SharePoint Sites – Session Tomorrow in Singapore
Next Post
SQLAuthority News – Blog of Nupur Dave on Windows Live

Related Posts

14 Comments. Leave new

  • thanks

  • Sumit Kumar Gaud
    October 27, 2010 10:39 am

    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

  • Sumit Kumar Gaud
    October 27, 2010 10:54 am

    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.

  • Namrata Chokhani
    October 28, 2010 1:19 pm

    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.

    • What changes do you want to do? If you want to change the datatype, make sure to have enough length

      • 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.

  • Dileep Karnati
    October 29, 2010 1:36 am

    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.

  • This can be done in 2 ways

  • What is the answer to the puzzle?

  • 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.


  • 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.

  • 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