Here are some tips for OLTP workloads with strict response time requirements, high concurrency requirements, and/or high overall server throughput requirements:
1. Watch out for blocking and/or memory-consuming operators such as sorts, hash joins, and hash aggregates. Memory-consuming operators degrade response times because of the verhead of acquiring a memory grant, and they reduce concurrency by creating contention for memory, which is a limited resource.
2. Avoid scans and be sure that queries make maximum use of indexes. Keep in mind that
the cost of a scan is proportional to the size of the table, whereas the cost of an index seek is proportional to the number of rows returned. Thus, seeks yield more consistent performance as data sizes grow.
Here are some tips for data warehousing workloads where the response time of a single query or report is most important:
1. Make sure that large queries that process large datasets are choosing parallel query
plans. While parallel plans do add extra query processing overhead, they also minimize
the response time of queries.
2. Watch out for skew in parallel plans. Parallel scans over small tables and nested loops
joins with very few rows on the outer side of the join can lead to skew problems in which
some threads process many more rows or perform much more work than other threads.
3. Avoid sort-based operators, such as stream aggregates and merge joins in highly parallel plans on large machines with more processors or more cores. Specifically look out for merging exchanges at high degrees of parallelism. Merging exchanges use more server resources and are more subject to skew and scalability issues.