SQL Server Performance

Can Index Tuning Wizard work with temporary table?

Discussion in 'T-SQL Performance Tuning for Developers' started by pcsql, May 4, 2004.

  1. pcsql New Member

    Can Index Tuning Wizard work with temporary table?
    pcsql, May 4, 2004
    #2

  2. Luis Martin Moderator

    No.
    If you have something like:

    insert into #aux (select ........)

    then, pick from select to end and copy into SQL Analyzer and run ITW from that.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    Luis Martin, May 4, 2004
    #2

  3. pcsql New Member

    Hi Luis,

    So, if I have something like this:

    update #temp set column1 = table1.column2 where #temp.column3 = table1.column3,

    I cannot use ITW to recommend what index to add.


    pcsql, May 4, 2004
    #2

  4. Luis Martin Moderator

    In that case the only I can suggest is to create real tables (in test database) and then run ITW or see execution plan.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Luis Martin, May 4, 2004
    #2

  5. pcsql New Member

    Hi Luis,

    This is exactly what I have been doing to use ITW. Thank you for the confirmation since I just start using ITW.


    pcsql, May 4, 2004
    #2

  6. gaurav_bindlish New Member

    If the temp table operation is part of bigger query, i think index tuning wizard will work. In any case it won't recommend anything dor temp tables as they don't have any indexes right?


    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    gaurav_bindlish, May 4, 2004
    #2

  7. derrickleggett New Member

    Gaurav, many times really large batch processes can get a big performance boost from using indexes on temp tables. I'm a little confused by your statement. Can you clarify please?

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
    derrickleggett, May 4, 2004
    #2

  8. gaurav_bindlish New Member

    Yeah you can explicitly create indexes on the temp tables but I am not sure if ITW can recommend that as it generates the index recommendations based on the query and the no. of rows present in the table. I would be surprised if ITW is intelligent enough to handle temp tables.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    gaurav_bindlish, May 4, 2004
    #2

  9. pcsql New Member

    Hi gaurav_bindlish,

    As of now, I cannot find a way to use ITW for temp tables. However, it does not make sense to me that ITW cannot handle temp tables. To workaround this, I create a permanent table which is a duplicate of the temp table. After I use ITW to get the index for the permanent table, I apply the same index to the temp table. This seems to work fine. So, in my mind, ITW should able to accept temp tables instead of just permanent tables. I wonder whether Yukon has updated ITW with temp tables.

    pcsql, May 5, 2004
    #2

  10. Luis Martin Moderator

    About temp tables:
    Gaurav is right. 3rd part application I have to deal, have a lot of temp tables with large queries with union and join with many tables.
    The idea to use ITW is not to create index on temp tables, but ITW may recomend indexs in other real tables using in queries, and with that I get performance improvement.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Luis Martin, May 5, 2004
    #2

  11. gaurav_bindlish New Member

    pcsql, this is a good idea what you have suggested.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    gaurav_bindlish, May 5, 2004
    #2

  12. Luis Martin Moderator

    "I wonder whether Yukon has updated ITW with temp tables"
    No in beta1.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    Luis Martin, May 5, 2004
    #2

  13. pcsql New Member

    Gaurav,

    This idea is forwarded to MS and logged as a request for Acadia.
    pcsql, May 14, 2004
    #2

  14. satya Moderator

    You may send any request to MS @ sqlwish@microsoft.com to addup.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
    satya, May 16, 2004
    #2

  15. pcsql New Member

    Hi Satya,

    That post is just a FYI for Gaurav. Arun Marathe, DTA Program Manager, has already put in this request.


    Peter
    pcsql, May 17, 2004
    #2

  16. gaurav_bindlish New Member

    Thanks for the Information...

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
    gaurav_bindlish, May 17, 2004
    #2

  17. james786 New Member

    Hi
    This is james786
    I am new to this forum. I think your site is providing information about Can Index Tuning Wizard work with temporary table?. Here I am providing information about temporary table tuning wizard work. I think this site is to be useful to whom wants to find tuning wizard work they require to see this.
    ==========================
    jamesbrosnan
    New Jobs
    james786, Feb 4, 2009
    #3

Share This Page