How to increase the performance of a Database?
database, performance, sql, sql-server-2005
Solution
Optimize the logical design
The logical level is about the structure of the query and tables themselves. Try to maximize this first. The goal is to access as few data as possible at the logical level.
- Have the most efficient SQL queries
- Design a logical schema that support the application's need (e.g. type of the columns, etc.)
- Design trade-off to support some use case better than other
- Relational constraints
- Normalization
Optimize the physical design
The physical level deals with non-logical consideration, such as type of indexes, parameters of the tables, etc. Goal is to optimize the IO which is always the bottleneck. Tune each table to fit it's need. Small table can be loaded permanently loaded in the DBMS cache, table with low write rate can have different settings than table with high update rate to take less disk spaces, etc. Depending on the queries, different index can be used, etc. You can denormalized data transparently with materialized views, etc.
- Tables paremeters (allocation size, etc.)
- Indexes (combined, types, etc.)
- System-wide parameters (cache size, etc.)
- Partitioning
- Denormalization
Try first to improve the logical design, then the physical design. (The boundary between both is however vague, so we can argue about my categorization).
Optimize the maintenance
Database must be operated correctly to stay as efficient as possible. This include a few mainteanance taks that can have impact on the perofrmance, e.g.
- Keep statistics up to date
- Re-sequence critical tables periodically
- Disk maintenance
- All the system stuff to have a server that rocks
Problem
I have designed databases several times in my company. To increase the performance of the database, I look for Normalisation and Indexing only. If you were asked to increase the performance of a database which has approx 250 tables and some tables with millions of records, what different things you would look for? Thanks in advance.