Do you have a maintenance window to perform database reindexing?
Have you ever performed full set of re-organize and re-index process on a bigger database?
Do you know there are best practices to deploy in this regard?
On large database systems, with large numbers of insert and update commands, the problem of index fragmentation is one of the main causes of performance degradation and a proper index optimization strategy is a must.
Also following are set of counters you need to keep in mind:
· Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on column which are used in JOIN Condition.
· Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of all the insert, updates, deletes to the table.
· Smaller Index Key gives better performance than Index key which covers large data or many columns
· Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
· Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.
Also there are few basic guidelines when you need to create a database from scratch such as,
· Design a normalized database.
· Optimize a database design by denormalizing.
· Optimize data storage.
· Manage concurrency – by selecting the appropriate transaction isolation level.
· Select a locking granularity level.
· Optimize and tune queries for performance.
· Optimize an indexing strategy.
· Decide when cursors are appropriate.
· Identify and resolve performance-limiting problems.
· Be familiar with index structures and index utilization. Specifically, they must understand the interaction between non-clustered indexes, clustered indexes and heaps. A must know why a covering index can improve performance.
· Be able to design a database to third normal form (3NF) and know the trade offs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.
Have you ever performed full set of re-organize and re-index process on a bigger database?
Do you know there are best practices to deploy in this regard?
On large database systems, with large numbers of insert and update commands, the problem of index fragmentation is one of the main causes of performance degradation and a proper index optimization strategy is a must.
Also following are set of counters you need to keep in mind:
· Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on column which are used in JOIN Condition.
· Remove any un-necessary Indexes. As Index occupies hard drive space as well as it decreases performance of all the insert, updates, deletes to the table.
· Smaller Index Key gives better performance than Index key which covers large data or many columns
· Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.
· Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.
Also there are few basic guidelines when you need to create a database from scratch such as,
· Design a normalized database.
· Optimize a database design by denormalizing.
· Optimize data storage.
· Manage concurrency – by selecting the appropriate transaction isolation level.
· Select a locking granularity level.
· Optimize and tune queries for performance.
· Optimize an indexing strategy.
· Decide when cursors are appropriate.
· Identify and resolve performance-limiting problems.
· Be familiar with index structures and index utilization. Specifically, they must understand the interaction between non-clustered indexes, clustered indexes and heaps. A must know why a covering index can improve performance.
· Be able to design a database to third normal form (3NF) and know the trade offs when backing out of the fully normalized design (denormalization) and designing for performance and business requirements in addition to being familiar with design models, such as Star and Snowflake schemas.
No comments:
Post a Comment