Return to HomePage

---

Checklist: SQL Server Performance

Source: http://msdn.microsoft.com/library/en-us/dnpag/html/ScaleNetCheck08.asp
J.D. Meier, Srinath Vasireddy, Ashish Babbar, Rico Mariani, and Alex Mackman

SQL: Scale Up vs. Scale Out

* 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

* 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

* 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

* 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

* 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

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

Execution Plans

* 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

* 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

* Avoid OPENXML over large XML documents.
* Avoid large numbers of concurrent OPENXML statements over XML documents.

Tuning

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

Testing

* 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

* 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

* 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.

---

Return to HomePage