Best optimization for a large SQL Server table (records 100-200 Mil)

Question

What are the best options / recommendations and optimizations that can be implemented when working with a large SQL Server 2005 table that contains anywhere from 100-200 million records?

Answer 1

Since you did not specify the purpose of the database or requirements, here are some general things that do not have a special order:

• A small clustered index for each table. Consider making this primary key on each table. This will be very efficient and save space in the main table and dependent tables.
• Corresponding non-clustered indexes (possibly covering indexes)
• Referential integrity
• Normalized tables
• Consistent naming of all database objects to simplify maintenance
• Appropriate partitioning (table and index) if you have Enterprise Edition SQL Server
• Appropriate control restrictions for tables if you intend to allow direct manipulation of data in the database.
• Decide where your business rules will reside and not deviate from it. In most cases, they are not included in the database.
• Launch Query Analyzer for your heavily used queries (at least) and view table scans. This will kill performance.
• Get ready to fight deadlocks. With a database of this size, especially if there is heavy recording, deadlocks can be a problem.
• Take advantage of views to hide the complexity of query pooling and the potential for query optimization and flexible security.
• Consider using schemas for better data organization and flexible security implementation.
• Meet Profiler. With a database of this size, you are likely to spend some time trying to identify bottlenecks for queries. A profiler can help you here.

Answer 2

Rule of thumb: if a table contains more than 25 million records, you should consider pagination (and indexes), but this feature is only available in Enterprise Edition SQL Server (and, accordingly, the version for developers).