Performance best practices for very large tables

PROBLEM:
What are the performance best practices for very large tables?

SOLUTION:
Here is a summary of best practices for querying, monitoring, and performance tuning for your very large tables:

• Pay attention to both the results of a query and the execution plan designed to return those results. Make it a routine exercise to scan query plans to become more familiar with how SQL Server resolves queries.
• De-normalize your schema where possible to reduce the need to join very large tables.
• Use table partitioning for your largest tables, and be sure when querying that the optimizer uses partition elimination whenever possible.
• Align secondary indexes with the same table partition function to reduce partitioned table maintenance.
• Consider using a heap instead of a clustered index, especially with a table that partially mimics a clustered index via its partition function.
• Design indexes to be covering for larger recurring queries whenever possible, especially when creating a filtered (i.e., partial) index.
• Use an indexed view as an alternative to a denormalized table or to materialize an aggregated dataset. The more static the underlying data, the less overhead will be required to maintain the indexed views.
• Use summary tables with automatic (scheduled) updates as an alternative to indexed views, especially when there is less significance in the currency of the data.
• For query monitoring, focus on the quantitative measures of Disk I/O, CPU Time, and Query Duration.
• Use the features of the Management Data Warehouse for overall server monitoring, and use the SQL Trace data collection set for query monitoring.
• Focus your tuning efforts on SQL code that is repeatedly run, either as a stored procedure or a SQL script, and that consumes the most server resources. Data warehouse applications tend to place the greatest pressure on Disk I/O.
• For tuning and management of large queries, consider saving historical copies of XML execution plans to disk for later reference.

If you find this useful, you are welcome to press one of the ads in this page.. Thanks!