I have upgraded an access database that was the backend for a classic ASP site to SQL Server Express 2005. Ever since then I seem to be getting wonky results in my queries, intermittent hangs, etc. Someone has suggested that probably need to clean up my indexes. However, I really dont know what i am doing with these. Most of the tables only have a PK and no other indexes. Two of the larger tables are frequently used
MailList: 500K to 1M Records PK on MailID (identity)
Order_History: 150K Records No PK, No Indexes
Is there a rule of thumb on creating the indexes and they keys that I should understand?
If its like the queries were working fine it may be that statistics are out of date. In such cases try updating statistics first
If question was to speed up queries which have always been slow, you need to analyse frequent queries and see columns they target for search, try data base tuning advisor to get an idea on missing index impact
visakh16 - Database Tuning Advisor should be handled with care. Very little expertise is required to run the Advisor - which can lead to problems about applying the recommendations.
Focus on some queries - check Ececution Plans and optimize where prossible. For example , an index?
The decision on which type of index is dependant on the queries , WHERE predicates and columns returned. Cardinality is also an issue