Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

4 Answers 4

up vote 9 down vote accepted

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

share|improve this answer
    
You can't add an index in the CREATE TABLE statement. Only constrains can be added there. –  Erik Dekker Aug 21 at 10:32
    
@ErikDekker Creating some constraints means creating indices also –  Oleg Dok Aug 22 at 5:30
    
I know. But the question was about adding a nonclustered index. That can't be done directly in the create statement ;-) –  Erik Dekker Aug 22 at 9:38
    
@ErikDekker You can specify which type of index will be created for constraint –  Oleg Dok yesterday

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 :-)

share|improve this answer
    
What kind of information would you suggest I add to my question to make it more helpful? –  O.O Jan 19 '12 at 20:58
    
An example of your temp table usage would be helpful. How often is it executed? How often would you read from the local temp table? I would also like to see the logical reads, writes, the execution plans for the code. If you had a baseline of the before and after this would let you know if the index is really helpful. –  JohnS Jan 19 '12 at 21:06
    
I'm not sure how to do all of that, but I can start with posting the actual explain plan of the stored procedure that uses the non-indexed temp tables, if you think that would be helpful? –  O.O Jan 19 '12 at 21:08
    
Sure, lets go for it. Here is also an link to show you how SET STATISTICS works. Can you run this against a development box? You would want to evaluate both senarios with and without the new index. You would also want to pull the execution plan for both executions. –  JohnS Jan 19 '12 at 21:20
    
Understood. How should I paste the explain plan? As XML? –  O.O Jan 19 '12 at 21:43

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.

share|improve this answer
    
Even if it's used multiple times in, let's say, a 2000+ line stored procedure? –  O.O Jan 19 '12 at 20:20
    
Sure - I've used them before in stored procs of that size with relative ease. Provided there are no query hints, the optimizer should be able to compensate. –  OliverAsmus Jan 19 '12 at 20:23

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.