Checklist: SQL Server Performance

Article
2010-07-14

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.

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 [email protected]

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
	Optimize the application before scaling up or scaling out.
	Address historical and reporting data.
	Scale up for most applications.
	Scale out when scaling up does not suffice or is cost-prohibitive.

Schema

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

Queries

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

Indexes

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

Transactions

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

Stored Procedures

Check	Description
	Use Set NOCOUNT ON in stored procedures.
	Do not use the sp_prefix for custom stored procedures.

Execution Plans

Check	Description
	Evaluate the query execution plan.
	Avoid table and index scans.
	Evaluate hash joins.
	Evaluate bookmarks.
	Evaluate sorts and filters.
	Compare actual versus estimated rows and executions.

Execution Plan Recompiles

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

SQL XML

Check	Description
	Avoid OPENXML over large XML documents.
	Avoid large numbers of concurrent OPENXML statements over XML documents.

Tuning

Check	Description
	Use SQL Profiler to identify long-running queries.
	Take note of small queries called often.
	Use sp_lock and sp_who2 to evaluate locking and blocking.
	Evaluate waittype and waittime in master..sysprocesses.
	Use DBCC OPENTRAN to locate long-running transactions.

Testing

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

Monitoring

Check	Description
	Keep statistics up to date.
	Use SQL Profiler to tune long-running queries.
	Use SQL Profiler to monitor table and index scans.
	Use Performance Monitor to monitor high resource usage.
	Set up an operations and development feedback loop.

Deployment Considerations

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

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.

Share via