17:06
Unknown
Index Optimization Tips:
• Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses.
• Keep your indexes as narrow as possible.
• Drop indexes that are not used.
• Try to create indexes on columns that have integer values rather than character values.
• Limit the number of indexes, if your application updates data very frequently.
• Check that index you tried to create does not already exist.
• Create clustered index instead of nonclustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results.
• Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.
• Create clustered index on column(s) that is not updated very frequently.
• Create clustered index based on a single column that is as narrow as possibly.
• Avoid creating a clustered index based on an incrementing key.
• Create a clustered index for each table.
• Don't create index on column(s) which values has low selectivity.
• If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
• If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.
• If you need to join several tables very frequently, consider creating index on the joined columns.
• Consider creating a surrogate integer primary key (identity, for example).
• Consider creating the indexes on all the columns, which referenced in most frequently used queries in the WHERE clause which contains the OR operator.
• If your application will perform the same query over and over on the same table, consider creating a covering index including columns from this query.
• Use the DBCC DBREINDEX statement to rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation.
• Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.
• Use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes.
• Consider creating index on column(s) frequently used in the WHERE, ORDER BY, and GROUP BY clauses.
• Keep your indexes as narrow as possible.
• Drop indexes that are not used.
• Try to create indexes on columns that have integer values rather than character values.
• Limit the number of indexes, if your application updates data very frequently.
• Check that index you tried to create does not already exist.
• Create clustered index instead of nonclustered to increase performance of the queries that return a range of values and for the queries that contain the GROUP BY or ORDER BY clauses and return the sort results.
• Create nonclustered indexes to increase performance of the queries that return few rows and where the index has good selectivity.
• Create clustered index on column(s) that is not updated very frequently.
• Create clustered index based on a single column that is as narrow as possibly.
• Avoid creating a clustered index based on an incrementing key.
• Create a clustered index for each table.
• Don't create index on column(s) which values has low selectivity.
• If you create a composite (multi-column) index, try to order the columns in the key as to enhance selectivity, with the most selective columns to the leftmost of the key.
• If you create a composite (multi-column) index, try to order the columns in the key so that the WHERE clauses of the frequently used queries match the column(s) that are leftmost in the index.
• If you need to join several tables very frequently, consider creating index on the joined columns.
• Consider creating a surrogate integer primary key (identity, for example).
• Consider creating the indexes on all the columns, which referenced in most frequently used queries in the WHERE clause which contains the OR operator.
• If your application will perform the same query over and over on the same table, consider creating a covering index including columns from this query.
• Use the DBCC DBREINDEX statement to rebuild all the indexes on all the tables in your database periodically (for example, one time per week at Sunday) to reduce fragmentation.
• Use the DBCC INDEXDEFRAG statement to defragment clustered and secondary indexes of the specified table or view.Consider using the SORT_IN_TEMPDB option when you create an index and when tempdb is on a different set of disks than the user database.
• Use the SQL Server Profiler Create Trace Wizard with "Identify Scans of Large Tables" trace to determine which tables in your database may need indexes.
Posted in:
SQL Server Tutorial
0 comments:
Post a Comment