Checklist: SQL Server Performance

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

patterns & practices Developer Center

Improving .NET Application Performance and Scalability

J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman
Microsoft Corporation

May 2004

Related Links

Home Page for Improving .NET Application Performance and Scalability

Send feedback to Scale@microsoft.com

patterns & practices Library

How to Use This Checklist

This checklist is a companion to Chapter 14, "Improving SQL Server Performance"

SQL: Scale Up vs. Scale Out

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Optimize the application before scaling up or scaling out.
Ff647681.checkbox(en-us,PandP.10).gif Address historical and reporting data.
Ff647681.checkbox(en-us,PandP.10).gif Scale up for most applications.
Ff647681.checkbox(en-us,PandP.10).gif Scale out when scaling up does not suffice or is cost-prohibitive.

Schema

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Devote the appropriate resources to schema design.
Ff647681.checkbox(en-us,PandP.10).gif Separate online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
Ff647681.checkbox(en-us,PandP.10).gif Normalize first, denormalize later for performance.
Ff647681.checkbox(en-us,PandP.10).gif Define all primary keys and foreign key relationships.
Ff647681.checkbox(en-us,PandP.10).gif Define all unique constraints and check constraints.
Ff647681.checkbox(en-us,PandP.10).gif Choose the most appropriate data type.
Ff647681.checkbox(en-us,PandP.10).gif Use indexed views for denormalization.
Ff647681.checkbox(en-us,PandP.10).gif Partition tables vertically and horizontally.

Queries

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Know the performance and scalability characteristics of queries.
Ff647681.checkbox(en-us,PandP.10).gif Write correctly formed queries.
Ff647681.checkbox(en-us,PandP.10).gif Return only the rows and columns needed.
Ff647681.checkbox(en-us,PandP.10).gif Avoid expensive operators such as NOT LIKE.
Ff647681.checkbox(en-us,PandP.10).gif Avoid explicit or implicit functions in WHERE clauses.
Ff647681.checkbox(en-us,PandP.10).gif Use locking and isolation level hints to minimize locking.
Ff647681.checkbox(en-us,PandP.10).gif Use stored procedures or parameterized queries.
Ff647681.checkbox(en-us,PandP.10).gif Minimize cursor use.
Ff647681.checkbox(en-us,PandP.10).gif Avoid long actions in triggers.
Ff647681.checkbox(en-us,PandP.10).gif Use temporary tables and table variables appropriately.
Ff647681.checkbox(en-us,PandP.10).gif Limit query and index hint use.
Ff647681.checkbox(en-us,PandP.10).gif Fully qualify database objects.

Indexes

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Create indexes based on use.
Ff647681.checkbox(en-us,PandP.10).gif Keep clustered index keys as small as possible.
Ff647681.checkbox(en-us,PandP.10).gif Consider range data for clustered indexes.
Ff647681.checkbox(en-us,PandP.10).gif Create an index on all foreign keys.
Ff647681.checkbox(en-us,PandP.10).gif Create highly selective indexes.
Ff647681.checkbox(en-us,PandP.10).gif Create a covering index for often-used, high-impact queries.
Ff647681.checkbox(en-us,PandP.10).gif Use multiple narrow indexes rather than a few wide indexes.
Ff647681.checkbox(en-us,PandP.10).gif Create composite indexes with the most restrictive column first.
Ff647681.checkbox(en-us,PandP.10).gif Consider indexes on columns used in WHERE, ORDER BY, GROUP BY, and DISTINCT clauses.
Ff647681.checkbox(en-us,PandP.10).gif Remove unused indexes.
Ff647681.checkbox(en-us,PandP.10).gif Use the Index Tuning Wizard.

Transactions

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Avoid long-running transactions.
Ff647681.checkbox(en-us,PandP.10).gif Avoid transactions that require user input to commit.
Ff647681.checkbox(en-us,PandP.10).gif Access heavily used data at the end of the transaction.
Ff647681.checkbox(en-us,PandP.10).gif Try to access resources in the same order.
Ff647681.checkbox(en-us,PandP.10).gif Use isolation level hints to minimize locking.
Ff647681.checkbox(en-us,PandP.10).gif Ensure that explicit transactions commit or roll back.

Stored Procedures

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Use Set NOCOUNT ON in stored procedures.
Ff647681.checkbox(en-us,PandP.10).gif Do not use the sp_prefix for custom stored procedures.

Execution Plans

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Evaluate the query execution plan.
Ff647681.checkbox(en-us,PandP.10).gif Avoid table and index scans.
Ff647681.checkbox(en-us,PandP.10).gif Evaluate hash joins.
Ff647681.checkbox(en-us,PandP.10).gif Evaluate bookmarks.
Ff647681.checkbox(en-us,PandP.10).gif Evaluate sorts and filters.
Ff647681.checkbox(en-us,PandP.10).gif Compare actual versus estimated rows and executions.

Execution Plan Recompiles

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Use stored procedures or parameterized queries.
Ff647681.checkbox(en-us,PandP.10).gif Use sp_executesql for dynamic code.
Ff647681.checkbox(en-us,PandP.10).gif Avoid interleaving data definition language (DDL) and data manipulation language (DML) in stored procedures, including the tempdb database DDL.
Ff647681.checkbox(en-us,PandP.10).gif Avoid cursors over temporary tables.

SQL XML

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Avoid OPENXML over large XML documents.
Ff647681.checkbox(en-us,PandP.10).gif Avoid large numbers of concurrent OPENXML statements over XML documents.

Tuning

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Use SQL Profiler to identify long-running queries.
Ff647681.checkbox(en-us,PandP.10).gif Take note of small queries called often.
Ff647681.checkbox(en-us,PandP.10).gif Use sp_lock and sp_who2 to evaluate locking and blocking.
Ff647681.checkbox(en-us,PandP.10).gif Evaluate waittype and waittime in master..sysprocesses.
Ff647681.checkbox(en-us,PandP.10).gif Use DBCC OPENTRAN to locate long-running transactions.

Testing

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Ensure that your transactions logs do not fill up.
Ff647681.checkbox(en-us,PandP.10).gif Budget your database growth.
Ff647681.checkbox(en-us,PandP.10).gif Use tools to populate data.
Ff647681.checkbox(en-us,PandP.10).gif Use existing production data.
Ff647681.checkbox(en-us,PandP.10).gif Use common user scenarios, with appropriate balances between reads and writes.
Ff647681.checkbox(en-us,PandP.10).gif Use testing tools to perform stress and load tests on the system.

Monitoring

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Keep statistics up to date.
Ff647681.checkbox(en-us,PandP.10).gif Use SQL Profiler to tune long-running queries.
Ff647681.checkbox(en-us,PandP.10).gif Use SQL Profiler to monitor table and index scans.
Ff647681.checkbox(en-us,PandP.10).gif Use Performance Monitor to monitor high resource usage.
Ff647681.checkbox(en-us,PandP.10).gif Set up an operations and development feedback loop.

Deployment Considerations

Check Description
Ff647681.checkbox(en-us,PandP.10).gif Use default server configuration settings for most applications.
Ff647681.checkbox(en-us,PandP.10).gif Locate logs and the tempdb database on separate devices from the data.
Ff647681.checkbox(en-us,PandP.10).gif Provide separate devices for heavily accessed tables and indexes.
Ff647681.checkbox(en-us,PandP.10).gif Use the correct RAID configuration.
Ff647681.checkbox(en-us,PandP.10).gif Use multiple disk controllers.
Ff647681.checkbox(en-us,PandP.10).gif Pre-grow databases and logs to avoid automatic growth and fragmentation performance impact.
Ff647681.checkbox(en-us,PandP.10).gif Maximize available memory.
Ff647681.checkbox(en-us,PandP.10).gif Manage index fragmentation.
Ff647681.checkbox(en-us,PandP.10).gif Keep database administrator tasks in mind.

patterns & practices Developer Center

Retired Content

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

© Microsoft Corporation. All rights reserved.