Again, you may not be delving much into this, but there are a few tips to keep in mind.
- While it is possible to over-normalize a database, under-normalization is much more prevalent. This leads to repetition of data, inefficient storage, and poor performance. Data normalization is a performance tuning technique as well as a storage mechanism.
- Referential integrity constraints such as foreign keys actually help performance, because the optimizer can recognize these enforced constraints and make better choices for joins and other data access.
- Unique constraints also help performance because the optimizer can use these in different ways to enhance its query tuning.
- Make sure your database doesn't hold 'historic' data that is no longer used. Archive it out, either into a special 'archive' database, a reporting OLAP database, or on file. Large tables mean longer table scans and deeper indexes. This in turn can mean that locks are held for longer. Admin tasks such as Statistics updates, DBCC checks, and index builds take longer, as do backups.
- Separate out the reporting functions from the OLTP production functions. OLTP databases usually have short transactions with a lot of updates whereas reporting databases, such as OLAP and data warehouse systems, have longer data-heavy queries. If possible, put them on different servers.
No comments :
Post a Comment