SQL Server Performance Forum – Threads Archive

Can Index Tuning Wizard work with temporary table?

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.

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.

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

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

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, 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
[email protected] When life gives you a lemon, fire the DBA.

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.

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.

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

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.

"I wonder whether Yukon has updated ITW with temp tables"
No in beta1.
Luis Martin
Moderator
SQL-Server-Performance.com

Gaurav, This idea is forwarded to MS and logged as a request for Acadia.

You may send any request to MS @ [email protected] 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.

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

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.

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

]]>