Performance Audit Checklist

Return to Resume
  1. 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
  2. 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
  3. 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.
  4. 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
    • Consider findings of the Index Tuning Wizard
  5. 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
  6. Stored Procedures
    • Use Set NOCOUNT ON in stored procedures
    • Do not use the sp_prefix for custom stored procedures
  7. 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
  8. 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
  9. SQL XML
    • Avoid OPENXML over large XML documents.
    • Avoid large numbers of concurrent OPENXML statements over XML documents.
  10. 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
  11. 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
  12. 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
  13. Deployment
    • 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