Demystifying Performance Tuning

1
Demystifying Performance Tuning

• Taking a proactive approach by integrating
  performance tuning in your System Development
  Life Cycle

2
SQL Server Application Performance

• Performance is part of SLDC
• Application architecture and design
• Database architecture and design
• Query plan analysis during unit testing
• Pre-implementation performance stress testing
• Post-implementation performance analysis and
  troubleshooting
• Q A

3
Application Architecture and Design

• Building a high-performance application is a
  collaborative effort of the entire development
  team
• DBAs need to actively participate in application
  design decisions that ultimately affect database
  performance and scalability
• Keep in mind that there are many ways to address
  a problem and non-SQL approaches may be better in
  many cases
• Expected transaction volumes and SLAs influence
  design and should be part of formal application
  specs
• Although important, performance is not the only
  consideration

4
Database architecture and design

• Simply following database design Best Practices
  often provides very good out-of-the-box
  performance
• Normalize schema
• Attention to detail on data types (fixed vs.
  variable, appropriate data type for domain)
• NOT NULL when appropriate
• Primary keys on all tables
• Unique Constraints
• Foreign key constraints
• Develop and consistently follow standards, such
  as schema-qualifying object names

5
Database architecture and design(cont.)

• Add additional indexes during development as
  needed
• Candidates are columns used in predicates
  (JOIN/WHERE clause conditions)
• Choose clustered index carefully
• Specify UNIQUE when applicable or use UNIQUE
  constraint instead
• Create covering indexes (INCLUDE in SQL 2005) for
  critical queries
• Importantly, examine execution plans as part for
  unit testing process

6
Query plan analysis

• Examine query plans during unit testing using
  production-like data and volumes, taking note of
  expensive operators like scans and parallelism
• Optimize by refactoring queries and index tuning
• Use SQL 2005 performance features when
  appropriate, such as statement-level recompile
  hints or secure dynamic SQL for flexible search
  criteria
• Use Index Tuning Wizard/Database Tuning Advisor

7
Pre-implementation performance testing

• Test with workload that meets or exceeds expected
  production workload
• Use hardware similar to production, if possible
• Establish baseline for reference using SQL Trace
  and Performance Monitor
• Use automated tools, ideally exercising
  application code to hit the database
• Use SQL Trace to capture workload and analyze
  using Index Tuning Wizard or Database Tuning
  Advisor

8
Post-implementation Performance Analysis and
Troubleshooting

• Use SQL Trace and Performance Monitor to
  establish production performance baseline
• Create maintenance plans to reorg indexes and
  update statistics
• Import trace to a table for ad-hoc analysis
  long-running and frequently executed queries

9
Q A

• Share your methodology and experiences