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)