I am the OP here. It appears that the thread became a discsussion for whether indexes are needed on temp tables or not. I can say for sure that the answer is "yes, it is needed", particularly when large volume of data is on the temp tables and joins are done.

Now, if you all can be kind enough to read my original question and help me find a way to make SQL Server find out such missing indexes that would help performance, it will be helpful. I have summaized it again below.

1) Let's say "#t1" is created in session id 100 and #t1 has no indexes on any columns and has 100,000 rows in it.

2) The end user has his "own, custom" stored procedure that works on the data in the #t1 and joins #t1 with another perm. table say "MainData".

3) The temp. table "#t1" and the stored procedure are created by the end user.

4) Whereas the perm. table "MainData" (part of the application) is properly indexed, the "OrderID" column in "#t1" is not indexed which is used in the join with "MainData" and this causes slow performance. If the #t1 is indexed on "OrderID", the performance is great.

5) Now, at the end of the SP execution or before, I would like SQL Server to say, index on "OrderID" column in "#t1" is "recommended".

6) Again, please do note that the temp. table "#t1" is dynamic in nature, and the application will have no knowledge about this and that's the reason the index on "OrderId" can be "pre" created. Therefore, I want the app to suggest to the user that index on "OrderID" column in "#t1" is recommended.

I can make the application run some queries at the end (in the same session id 100 where the #t1 is created) to make the index suggestions to the user.

Any ideas?