SQL Server Optimization for Developers

1. SQL Server Optimization for Developers Anil Desai (Anil@AnilDesai.net | http://AnilDesai.net) Austin .NET User’s Group, 04/14/2014

2. Presentation Overview • Database Performance Goals and Challenges • Monitoring and Optimizing Performance • Understanding indexes • SQL Profiler and Database Engine Tuning Advisor • Tuning Database Queries • Understanding the Query Optimizer and Execution Plans • Seeing the effects of indexes • Application Design Best Practices

3. Performance Monitoring Process • Best Practices: • Optimize for real-world workloads • Monitor/review performance regularly • Focus on specific issues

4. Monitoring and Troubleshooting Scenarios

5. Common Datbaase Questions

6. Database Performance Tools

7. Database Design Issues • Transaction processing (OLTP) • Favors normalized schema • Many tables, each with fewer columns • Optimized for write (transactional) activity • Reporting and Analysis • Centralized, consistent storage of required data • Favored by denormalized schema • Fewer tables with many columns in each • Data is aggregated from multiple sources into a data mart or data warehouse • May store aggregates in warehouse

8. Understanding Indexes • Index types • Clustered Index • Non-Clustered Indexes • Columnstore indexes • Indexing strategies • Goal is ideal index coverage • Index maintenance can slow-down write operations (Insert, Update, Delete) • Referential Integrity • Primary Key (default = clustered index) • Foreign Key references • Constraints • Statistics (manual vs. automatic)

9. General Index Tuning Best Practices • Make tuning a part of your development process • Dev: Use synthetic workloads and test cases • Test: Use real-world databases, whenever possible • Production: Capture real usage statistics for analysis • Collect a representative workload, whenever possible • Consider all applications and workloads when tuning a database • Use naming conventions for indexes and related objects • Use query hints sparingly (NOLOCK)

10. Using SQL Profiler • Purpose / Features: • GUI for managing SQL Trace • Monitor important events • Capture performance data / resource usage • Replaying of workloads / transactions • Identifying performance bottlenecks • Correlation of data with System Monitor • Workloads for Database Tuning Advisor • Examples: • Generate a list of the 100 slowest queries • Monitor all failed logins (Security)

11. Database Engine Tuning Advisor • Automatic workload analysis for Physical Design Structures (PDS) • Data Source: • File (Profiler Trace or .SQL files) • Table • Plan Cache • Tuning Options • Keep existing PDS • Advanced Features: Partitioning, indexed views, etc.

12. Demo: Optimizing Indexes • Generate sample queries / tables • View query execution plans • View the effects of indexes on common queries • Capture Performance Data with SQL Profiler • SQL Profiler traces, events, and filters • Using SQL Load Generator to generate database load • Capturing and storing • Analyzing and optimizing with Database Tuning Advisor • Analyzing index usage reports • Saving and applying index recommendations

13. Tuning Individual Queries • Query Analyzer Features • Execution Plan (estimated and actual) • Include Client Statistics (multiple trials) • Analyze in Database Engine Tuning Advisor (single query) • Trace query in SQL Profiler (single query) • Keep query logic close to the database • Filter returned data at the database layer • Minimize the size of result sets • Minimize round-trips to the server • Use standard (inner) joins, where possible • Consider strategic denormalization for core sets of data

14. Query Optimizer Details • Goal: Find the most efficient method to return the data • Come up with a plan quickly • Minimize CPU, memory, and I/O requirements • Use statistics and index details to improve plans • Query plan caching • Relational engine vs. storage engine • Execution Plan output • Save as .sqlplan file for later analysis • Output in graphical, text, and XML formats • Can store and export plans using SQL Profiler (ShowPlan XML event) • Can use query hints

15. Understanding Execution Plans • Optimizing individual queries • Rewrite query logic • Use other objects (views, stored, procedures, etc.) • Strategic demoralization • Data Retrieval: Table scan, index seek/scan • Index Usage • Covering indexes • Join conditions

16. Execution Plan Example

17. Execution Plans in Windows Azure

18. Client Statistics Example

19. Application Design Best Practices • Create an abstraction layer between business and database objects • ADO.NET • Microsoft Enterprise Library • NHibernate • Entity Framework • Use caching wherever possible • Server-side (web services) • Application-level (middle tier) • Client-side (desktop or mobile apps) • Minimize transaction times

20. Dev Best Practices: Application and Data Architecture

21. Windows Azure and Cloud Databases • Practical cloud benefits • Data redundancy and geographic distribution • Lower management overhead • Potential issues • Keeping data close to applications and services • Data synchronization • Network performance issues • Data security, legal issues, and regulatory compliance • Determine where/how to use cloud-based services • SaaS vs. PaaSvs. IaaS

22. Azure Database Services • SQL Azure Database • Cost-effective, managed database instances • Can be managed with standard tools (Visual Studio and SSMS) • Some limitations (CLR, Mirroring, Partitioning, Replication, Extended SP’s) • Other Services • Azure Virtual Machines (SQL Server templates) • Azure Web Sites (with gallery templates) • Azure HDInsight, Cache Service • Azure Backup and Recovery Manager • SQL Azure Reporting • Network, Active Directory, Service Bus, etc.

23. Managing SQL Azure Instances

24. ORM Considerations • General issues • Development efficiency vs. hardware/software efficiency • Latency, query inefficiency (outer joins), platform-specific optimizations • Frequency and number of server round-trips • ORM-generated queries can be inefficient • Difficult to tune or modify individual queries • Potential Solutions • Make sure entity relationships are correct • Can use views or stored procedures to improve performance in some cases • Bypass the ORM for some types of operations

25. New Features in SQL Server 2014 • Memory-optimized tables (In-Memory OLTP) • Buffer Pool Extension (for SSD usage) • Delayed durability • Async log writes can result in data loss • Enable at database-level; use with BEGIN ATOMIC … COMMIT • Resource Governor storage I/O limits • Updateable Clustered ColumnStore indexes • Primarily for data warehousing; supports data index compression • Azure storage for SQL Server data/log files • Backup to Azure; Backup encryption

26. Dev Best Practices: Managing Data • Large UPDATE or DELETE operations: • Use loops to minimize locking and transaction log growth • Large INSERT operations • Disable indexes and triggers (if present) • Use BULK INSERT, bcp, SSIS, or DTS • Change transaction isolation level (if appropriate) • Change recovery model • Use SQL to generate SQL • Example: INSERT statements • Schedule or delay non-critical operations

27. Dev Best Practices: Schema Changes • Generate Scripts • Script specific objects using SQL Server Management Studio • Script the entire database using Generate Scripts • Can include schema and/or data • Schema changes • Use ALTER commands when possible • Drop and recreate objects, as needed • Make all scripts re-runnable • Check before and after state of all objects

28. Dev Best Practices: Performance Testing • Build performance testing/optimization into the dev process • Develop load tests or test “harnesses” • Using synthetic load generation tools • Use representative test data • Consider caching effects: • Index maintenance (fragmentation) • DBCC DropCleanBuffers • DBCC FreeProcCache

29. Advanced Performance Approaches • Database Federations • Vertical and horizontal data partitioning • Cross-Server queries • Use Linked Servers to query across databases • Potential performance issues • Data compression (row- or page-level) • Resource governor • SQL Server Analysis Services (SSAS) • Pre-aggregation for performance • Dependent on a denormalized schema (optimized for reporting)

30. Links and References • Presenter: • http://AnilDesai.net | Anil@AnilDesai.net • Presentation slides and sample code • Microsoft TechNet Virtual Labs • Sample Databases • AdventureWorks Sample Databases (CodePlex) • Microsoft Contoso BI Demo Dataset • Database-related tools • SQL Load Generator by David Darden (CodePlex) • Glimpse • Red Gate Software • Spotlight

31. Summary and Conclusion