Optimizing SQL Server Performance - 1

Normalize Logical Database Design

Experience has shown that reasonable normalization of the logical database design will yield best performance. A greater number of narrower tables is characteristic of a normalized database. A lesser number of wider tables is characteristic of a denormalized database. A highly normalized database is normally associated with complex relational joins, which can hurt performance. However, the SQL Server optimizer is very efficient at selecting rapid, efficient joins, as long as effective indexes are available. Benefits from normalization:


accelerates sorting and index creation, since tables are narrower

allows more clustered indexes, since there are more tables

indexes tend to be narrower and more compact

allows better use of segments to control physical placement of tables

fewer indexes per table, helping UPDATE performance

fewer NULLs and less redundant data, increasing compactness of the database

With SQL Server, normalization will often help performance rather than hurt it. As normalization increases, so will the number and complexity of joins required to retrieve data. We suggest carrying on the normalization process unless this causes many queries to have over 4-way joins.

If the logical database design is already fixed and total redesign is not feasible, it may be possible to selectively normalize a large table if study shows a bottleneck on this table. If access to the database is conducted through stored procedures, this schema change could take place without impacting applications. If not, it may be possible to hide the change by creating a view that presents the illusion of a single table.