On a small table you will not notice much improvement; but general recommendation is you should have cluster indexes on every table.
It was at this time i started looking at existing index's on tables created within this process and i found that none of the tables had Clustered index's but many had non-clustered index's, sometimes multiple Clustered Index's ....
A table can only have ONE clustered index it cannot have multiple clustered indexes because cluster indexes dictate how the rows are sorted on the physical level.
If you are just using non-clustered indexes and heap; it will first cause a RID Lookup anytime you access anything in that table via a Index Seek. That is when an index seek operation completes it will have to go back to main heap to get the data. In addition when you are inserting, deleting, and updating records in the table the heap can become fragmented and there is no way to defrag it because of how heap are stored. So the access speed, updates, and deletes can take long time.
These temporary tables you changed to permanent tables; do these all get destroyed and recreated every time?
Also becareful about using the Index DMV they are only good as of last time you restarted your SQL Serer. Also they can lead you wrong if your statistics are out of date.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]