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

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

Ff647681.checkbox(en-us,PandP.10).gifOptimize the application before scaling up or scaling out.
Ff647681.checkbox(en-us,PandP.10).gifAddress historical and reporting data.
Ff647681.checkbox(en-us,PandP.10).gifScale up for most applications.
Ff647681.checkbox(en-us,PandP.10).gifScale out when scaling up does not suffice or is cost-prohibitive.


Ff647681.checkbox(en-us,PandP.10).gifDevote the appropriate resources to schema design.
Ff647681.checkbox(en-us,PandP.10).gifSeparate online analytical processing (OLAP) and online transaction processing (OLTP) workloads.
Ff647681.checkbox(en-us,PandP.10).gifNormalize first, denormalize later for performance.
Ff647681.checkbox(en-us,PandP.10).gifDefine all primary keys and foreign key relationships.
Ff647681.checkbox(en-us,PandP.10).gifDefine all unique constraints and check constraints.
Ff647681.checkbox(en-us,PandP.10).gifChoose the most appropriate data type.
Ff647681.checkbox(en-us,PandP.10).gifUse indexed views for denormalization.
Ff647681.checkbox(en-us,PandP.10).gifPartition tables vertically and horizontally.


Ff647681.checkbox(en-us,PandP.10).gifKnow the performance and scalability characteristics of queries.
Ff647681.checkbox(en-us,PandP.10).gifWrite correctly formed queries.
Ff647681.checkbox(en-us,PandP.10).gifReturn only the rows and columns needed.
Ff647681.checkbox(en-us,PandP.10).gifAvoid expensive operators such as NOT LIKE.
Ff647681.checkbox(en-us,PandP.10).gifAvoid explicit or implicit functions in WHERE clauses.
Ff647681.checkbox(en-us,PandP.10).gifUse locking and isolation level hints to minimize locking.
Ff647681.checkbox(en-us,PandP.10).gifUse stored procedures or parameterized queries.
Ff647681.checkbox(en-us,PandP.10).gifMinimize cursor use.
Ff647681.checkbox(en-us,PandP.10).gifAvoid long actions in triggers.
Ff647681.checkbox(en-us,PandP.10).gifUse temporary tables and table variables appropriately.
Ff647681.checkbox(en-us,PandP.10).gifLimit query and index hint use.
Ff647681.checkbox(en-us,PandP.10).gifFully qualify database objects.


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


Ff647681.checkbox(en-us,PandP.10).gifAvoid long-running transactions.
Ff647681.checkbox(en-us,PandP.10).gifAvoid transactions that require user input to commit.
Ff647681.checkbox(en-us,PandP.10).gifAccess heavily used data at the end of the transaction.
Ff647681.checkbox(en-us,PandP.10).gifTry to access resources in the same order.
Ff647681.checkbox(en-us,PandP.10).gifUse isolation level hints to minimize locking.
Ff647681.checkbox(en-us,PandP.10).gifEnsure that explicit transactions commit or roll back.

Stored Procedures

Ff647681.checkbox(en-us,PandP.10).gifUse Set NOCOUNT ON in stored procedures.
Ff647681.checkbox(en-us,PandP.10).gifDo not use the sp_prefix for custom stored procedures.

Execution Plans

Ff647681.checkbox(en-us,PandP.10).gifEvaluate the query execution plan.
Ff647681.checkbox(en-us,PandP.10).gifAvoid table and index scans.
Ff647681.checkbox(en-us,PandP.10).gifEvaluate hash joins.
Ff647681.checkbox(en-us,PandP.10).gifEvaluate bookmarks.
Ff647681.checkbox(en-us,PandP.10).gifEvaluate sorts and filters.
Ff647681.checkbox(en-us,PandP.10).gifCompare actual versus estimated rows and executions.

Execution Plan Recompiles

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


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


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


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


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

Deployment Considerations

Ff647681.checkbox(en-us,PandP.10).gifUse default server configuration settings for most applications.
Ff647681.checkbox(en-us,PandP.10).gifLocate logs and the tempdb database on separate devices from the data.
Ff647681.checkbox(en-us,PandP.10).gifProvide separate devices for heavily accessed tables and indexes.
Ff647681.checkbox(en-us,PandP.10).gifUse the correct RAID configuration.
Ff647681.checkbox(en-us,PandP.10).gifUse multiple disk controllers.
Ff647681.checkbox(en-us,PandP.10).gifPre-grow databases and logs to avoid automatic growth and fragmentation performance impact.
Ff647681.checkbox(en-us,PandP.10).gifMaximize available memory.
Ff647681.checkbox(en-us,PandP.10).gifManage index fragmentation.
Ff647681.checkbox(en-us,PandP.10).gifKeep 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.