locked
Query grows tempdb after moving from SQL 2008 SP1 to SQL 2012 Core SP1 RRS feed

  • Question

  • I have a query that has been running on a SQL 2008 server for time. It takes about 4 hours to run. The largest table referenced in the query is an 8GB fact table.

    The database was restored to a SQL 2012 Core Server. The query would then run for 8+ hours and fail with a message that more space was needed in tempdb.

    Space was added to tempdb, but the query still failed. Using sys.dm_db_task_space_usage,  was able to see the space used in tempdb per connection.  More than 800GB (sic) of space was consumed in tempdb.

    I looked at the data in each of the tables used in the joins.  I was expecting to find a Cartesian product returned from one of the joins.  This was not the case.  The join column from every table   was unique in one of the joined tables.

    I overcame the issue by adding each join to the statement individually and then seeing where the issue arose. If I only joined three of the tables the query would return TOP 1000 immediately.  When added together, the final join caused an issue.  The TOP 1000 would not return after 20 minutes.

    I changed the query to load data to #temp tables and then joined the #temp tables to the rest of the tables.  Each join to the #temp tables performed quickly and did not create an explosion of growth in tempdb.

    The question still remains, however, why did the query complete on a less powerful 2008 server, but fail on a more powerful SQL 2012 Core Server with more memory and space?

    Pseudo code…

    --Before query

    select *
    from table1 t1
       inner join table2 t2 on t1.id = t2.t1_id
       inner join table3 t3 on t2.id = t3.t1_id
       inner join table4 t4 on t2.id = t4.t1_id
       inner join (
          select col1, col2, max(col3)
          from table5 t5
          where date > @date
             and col1 = @col1
          group by col1, col2
          ) as dt -- derived table
             on dt.col1 = t1.col1
       inner join table6 t6 on t1.id = t6.t1_id

    --After query
    select col1, col2, max(col3)
    into #temp1
    from table5 t5
    where date > @date
       and col1 = @col1
    group by col1, col2

    select *
    into #temp2
    from table1 t1
       inner join table2 t2 on t1.id = t2.t1_id
       inner join table3 t3 on t2.id = t3.t1_id
       inner join table4 t4 on t2.id = t4.t1_id
       inner join (
          select *
          from #temp1
          ) as dt -- derived table
             on dt.col1 = t1.col1

    select *
    from #temp2 t2
       inner join table6 t6 on t2.id = t6.t1_id

    Friday, March 22, 2013 2:05 PM

Answers

  • Hi DBAJay,

    Please update the table statistics on SQL Server 2012 and check the result again. Additionally, I suggest comparing the execution plan generated by SQL Server 2012 and SQL Server 2008. For more detail information, please refer to the following link:

    UPDATE STATISTICS (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms187348.aspx


    Allen Li
    TechNet Community Support

    Monday, March 25, 2013 2:47 AM