To improve performance in SQL Server the first step is to create indexes on the appropriate table-fields. Below a list of 9 tips when creating indexes.
1) Create indexes on the highly selective colums that are used in the WHERE-clause
2) Create indexes on all columns that are used in the WHERE clause in case OR is used
3) Create at least a clustered index on every table. Generally use the column that monotonically increases
4) Create indexes columns that are frequently accessed by WHERE, ORDER BY, GROUP BY, TOP and DISTINCT
5) Only add indexes that will be used frquently
6) Avoid adding too much indexes on dynamic tables (subject to many INSERTs, UPDATEs or DELETEs)
7) For static tables use a FILLFACTOR and PAD_INDEX of 100. For dynamic tables use a lower FILLFACTOR
8) To identify additional indexes use the SS Profiler Create Trace Wizard and trace "Identify Scans of Large Tables"
9) Avoid adding indexes twice.
Of course another step is to improve your queries. For example make them sargable (capable of using indexes). I've created a list with 23 tips to improve your SQL queries.
These tips (together with lots of other facts) can also be found on my SQL Server factsheet for developers. This factsheet (PDF) fits on one sheet can be found here.
Labels: Indexes, performance, SQL Server, SQL Server 2000, SQL Server 2005
Posted by Xander Zelders
0 Comments
