SQL Server 2000 Performance Checklist

From Guidance Share

(Difference between revisions)
Jump to: navigation, search
Revision as of 07:49, 13 October 2006 (edit)
Admin (Talk | contribs)

← Previous diff
Current revision (04:28, 13 December 2007) (edit)
JD (Talk | contribs)

 
Line 7: Line 7:
* Scale up for most applications. * Scale up for most applications.
* Scale out when scaling up does not suffice or is cost-prohibitive. * Scale out when scaling up does not suffice or is cost-prohibitive.
 +
== Schema == == Schema ==
Line 17: Line 18:
* Use indexed views for denormalization. * Use indexed views for denormalization.
* Partition tables vertically and horizontally. * Partition tables vertically and horizontally.
 +
== Queries == == Queries ==
Line 31: Line 33:
* Limit query and index hint use. * Limit query and index hint use.
* Fully qualify database objects. * Fully qualify database objects.
 +
== Indexes == == Indexes ==
Line 44: Line 47:
* Remove unused indexes. * Remove unused indexes.
* Use the Index Tuning Wizard. * Use the Index Tuning Wizard.
 +
== Transactions == == Transactions ==
Line 52: Line 56:
* Use isolation level hints to minimize locking. * Use isolation level hints to minimize locking.
* Ensure that explicit transactions commit or roll back. * Ensure that explicit transactions commit or roll back.
 +
== Stored Procedures == == Stored Procedures ==
* Use Set NOCOUNT ON in stored procedures. * Use Set NOCOUNT ON in stored procedures.
* Do not use the sp_prefix for custom stored procedures. * Do not use the sp_prefix for custom stored procedures.
 +
== Execution Plans == == Execution Plans ==
Line 70: Line 76:
* Avoid interleaving data definition language (DDL) and data manipulation language (DML) in stored procedures, including the tempdb database DDL. * Avoid interleaving data definition language (DDL) and data manipulation language (DML) in stored procedures, including the tempdb database DDL.
* Avoid cursors over temporary tables. * Avoid cursors over temporary tables.
 +
== SQL XML == == SQL XML ==
* Avoid OPENXML over large XML documents. * Avoid OPENXML over large XML documents.
* Avoid large numbers of concurrent OPENXML statements over XML documents. * Avoid large numbers of concurrent OPENXML statements over XML documents.
 +
== Tuning == == Tuning ==
Line 89: Line 97:
* Use common user scenarios, with appropriate balances between reads and writes. * Use common user scenarios, with appropriate balances between reads and writes.
* Use testing tools to perform stress and load tests on the system. * Use testing tools to perform stress and load tests on the system.
 +
== Monitoring == == Monitoring ==
Line 108: Line 117:
* Keep database administrator tasks in mind. * Keep database administrator tasks in mind.
-== Resources ==+ 
-* see online at http://msdn.microsoft.com/library/en-us/dnpag/html/ScaleNetCheck08.asp+[[Category: SQL Server 2000]]
 +[[Category: Checklist]]

Current revision

J.D. Meier, Srinath Vasireddy, Ashish Babbar, Rico Mariani, and Alex Mackman


Contents

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

  • 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.
Personal tools