When to add indexes to #temp tables?

Question

When testing stored procedures in SSMS, it sometimes says there is a missing index on #someTempTable____________________000000000000005B] (someField) etc etc

When I do add them like this to the sp:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[#someTable] ([someField])
GO

I never seem to see a speed improvement. So, I often do not add such indexes. When should I be adding such indexes?

Answer 1

Add an index if you'll use your temp table and its index twice or more during the query run.

Or to maintain usual index tasks, like uniqueness

If your data loaded into temp table are already sorted, the to create temp table with the same clustered index as sort of data

BUT

taking into account sql server's feature of temp tables reuse- if you decide to create an index on temp table - try to do it in CREATE TABLE statement. If you'll add an index explicitly after table creation - it will prevent sql server to reuse that table next time

Answer 2

you should update statistics after a significantly large change in the table, for example: create tempTable, add index, insert 10k records... UPDATE STATISTICS.

Answer 3

This is hard to answer without a lot more information. Generally, I would say, "it depends".

Generally speaking I add indexes onto a temp tables if the benefit of the index is greater than the original execution cost plus the cost of creating the index. You would have to do a benchmark using SET STATISTICS IO as an example. I would also want to review the execution plans used before and after the index is added. Sometimes recommended indexes are not really good recommended indexes :-)

Answer 4

Given the fact that the temp table you indicated has local scope (as denoted by the single #), it is generally not best to add an index as the table will only alive for the duration of the connection.