I am by no means a DBA, however based on my experience with high transaction and high volume databases for enterprise and web based systems, I have gained lot of experience in DB tuning.
Following are some of my thoughts:
1. If you perform regular joins between two or more tables in your queries, performance will be improved if each of the joined columns have their own indexes, including adding indexes to the columns in each table used to join the tables.
2. Generally speaking, a clustered key is better than a non-clustered key for optimum JOIN performance.
3. Foreign keys are not indexed automatically. If you ever plan to join a table to the table with the foreign key, using the foreign key as the linking column, then you should consider adding an index to the foreign key column. An index on a foreign key column can substantially boost the performance of many joins.
4. Avoid joining tables based on columns with few unique values. If columns used for joining aren’t mostly unique, then the SQL Server optimizer may not be able to use an existing index in order to speed up the join. Ideally, for best performance, joins should be done on columns that have unique indexes.
5. Indexes on the columns being joined should ideally be numeric data types, not CHAR or VARCHAR, or other non-numeric data types.
6. Indexes on the columns to be joined should have the same data type, and ideally, the same width.
7. To clean up data, prefer TRUNCATE over DELETE. After you have run the TRUNCATE TABLE statement, it is important then to manually update the statistics on this table using UPDATE STATISTICS. This is because running TRUNCATE TABLE will not reset the statistics for the table, which means that as you add data to the table, the statistics for that table will be incorrect for a time period.
8. Clustered Index
Only 1 allowed per tablePhysically rearranges the data in the table to conform to the index constraintsFor use on columns that are frequently searched for ranges of dataFor use on columns with low selectivity
Up to 249 allowed per tableCreates a separate list of key values with pointers to the location of the data in the data pagesFor use on columns that are searched for single valuesFor use on columns with high selectivity
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
10. Last but not the least, review query execution plan to review the table scans and usage of indexes….