Demystifying Performance Tuning

1. St. Louis SQL Server User Group, September 12, 2006 Demystifying Performance Tuning Taking a proactive approach by integrating performance tuning in your System Development Life Cycle This presentation complements separate SQL 2005 tools demo presentation. Holistic Approach to Performance Tuning was presented at STLSSUG in June, 2003, which discussed on the importance of SLAs and the fact that the database is only one aspect of application performance. This presentation focuses more on a database professional�s role in the full system life cycle (SDLC): design, development testing, implementation. Proactive: controlling a situation by causing something to happen rather than waiting to respond to it after it happens  A proactive DBA is often an unsung hero. A reactive DBA becomes a hero by solving problems after they occur. This presentation complements separate SQL 2005 tools demo presentation. Holistic Approach to Performance Tuning was presented at STLSSUG in June, 2003, which discussed on the importance of SLAs and the fact that the database is only one aspect of application performance. This presentation focuses more on a database professional�s role in the full system life cycle (SDLC): design, development testing, implementation. Proactive: controlling a situation by causing something to happen rather than waiting to respond to it after it happens A proactive DBA is often an unsung hero. A reactive DBA becomes a hero by solving problems after they occur.

2. St. Louis SQL Server User Group, September 12, 2006 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 Performance needs to be considered from application conception through sunsetPerformance needs to be considered from application conception through sunset

3. St. Louis SQL Server User Group, September 12, 2006 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 A �team� can consist on one individual or many members representing different specialties. Specialists should know enough about other technology areas to make informed design decisions. A hybrid solution is often best. Other considerations, such as reusability and the technology mix, influence decisions. You can always throw hardware at the problem (to a point).A �team� can consist on one individual or many members representing different specialties. Specialists should know enough about other technology areas to make informed design decisions. A hybrid solution is often best. Other considerations, such as reusability and the technology mix, influence decisions. You can always throw hardware at the problem (to a point).

4. St. Louis SQL Server User Group, September 12, 2006 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. St. Louis SQL Server User Group, September 12, 2006 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 Keep in mind that overall database performance is the cumulative performance of all queries. Keep in mind that overall database performance is the cumulative performance of all queries.

6. St. Louis SQL Server User Group, September 12, 2006 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 Details covered in other presentations. Real-word demo of SQL Trace with ad-hoc analysis and Query plan analysis at end of presentation.Details covered in other presentations. Real-word demo of SQL Trace with ad-hoc analysis and Query plan analysis at end of presentation.

7. St. Louis SQL Server User Group, September 12, 2006 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 Web App stress tool Testing tools in VS 2005 Team editionsWeb App stress tool Testing tools in VS 2005 Team editions

8. St. Louis SQL Server User Group, September 12, 2006 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 Ideally, very little needs to be done after implementation for in-house developed applications. Poorly written third party apps that can be problematic, especially when changes aren�t allowed. SQL 2005 plan guides are an option in that case. Ideally, very little needs to be done after implementation for in-house developed applications. Poorly written third party apps that can be problematic, especially when changes aren�t allowed. SQL 2005 plan guides are an option in that case.

9. St. Louis SQL Server User Group, September 12, 2006 Q & A Share your methodology and experiences Demo of real-world trace and questions. Mention additional resources.Demo of real-world trace and questions. Mention additional resources.