Thursday, March 19, 2009

Book Chapter on Improving SQL Server Performance

 

Book Chapter on Improving SQL Server Performance

I stumbled upon a great document on how to improve SQL Server performance. It's actually a chapther in a book on "Improving .NET Application Performance and Scalability" from a "Patterns and Practices" prescriptive guidance. Here's a summary of what it covers:

  • 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 OLAP and OLTP workloads.
    • Normalize first, denormalize for performance later.
    • 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 hints 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.
    • Consider 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 DDL and 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 blocking and locking.
    • Evaluate waittype and waittime in master..sysprocesses.
    • Use DBCC OPENTRAN to locate long-running transactions.
  • Testing
  • 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 appropriate 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.

If you like the summary, read the entire 32 pages from
http://msdn.microsoft.com/en-us/library/ms998577.aspx

Jose Barreto's Blog : Book Chapter on Improving SQL Server Performance

No comments:

Blog Archive